Create Load JSON logs into DB table.md

[staticlibs]

I think this trick with loading JSON into DB can be useful for logs analysis. Not sure whether it should go to Dev_Tools or some other section though.

[bill-ramos-rmoswi]

Hi @staticlibs, I like the idea! I'd add that there is an option to use csvlog as well. It's a smaller and can be directly linked to PostgreSQL using the log FDW. Here is what Claude 3.5 Sonnet chat provided with the following prompt.

Provide detailed instructions on how to my DBA team can use the PostgreSQL LOG   
Foreign data wrapper to create foreign tables against the logs and then query them.   

I did something similar with Aurora PostgreSQL with Babelfish using the Aurora version of log_fdw. I have not tested this out yet.

Claude 3.5 Sonnet - Configuring and testing the log_fdw

Here are detailed instructions for your DBA team to use the PostgreSQL LOG Foreign Data Wrapper (FDW) to create foreign tables against logs and query them:

1. Install the LOG FDW extension

   NOTE: from @bill-ramos-rmoswi: You need to do this in the default postgres database since Babelfish added a restriction on being able to change one of the settings if you try to create the extension in babelfish_db or in WiltonDB's case the wilton database>.

First, ensure the log_fdw extension is installed on your PostgreSQL server. If it's not already available, you may need to install it from the PostgreSQL contrib packages.

CREATE EXTENSION log_fdw;

3. Create a foreign server:

Create a foreign server that points to the log files you want to query. You'll need to specify the log file format and location.

CREATE SERVER log_server
FOREIGN DATA WRAPPER log_fdw
OPTIONS (
    program 'pglog',
    log_file_path '/path/to/your/postgresql/log/directory/*.log'
);

Replace /path/to/your/postgresql/log/directory/*.log with the actual path to your PostgreSQL log files.

4. Create a user mapping:

Create a user mapping for the role that will be querying the logs:

CREATE USER MAPPING FOR CURRENT_USER
SERVER log_server;

5. Create foreign tables:

Now, create foreign tables that correspond to the log file structure. The exact columns will depend on your log_line_prefix setting and the information you want to extract. Here's an example:

CREATE FOREIGN TABLE postgres_logs (
    log_time timestamp with time zone,
    user_name text,
    database_name text,
    process_id integer,
    connection_from text,
    session_id text,
    session_line_num bigint,
    command_tag text,
    session_start_time timestamp with time zone,
    virtual_transaction_id text,
    transaction_id bigint,
    error_severity text,
    sql_state_code text,
    message text,
    detail text,
    hint text,
    internal_query text,
    internal_query_pos integer,
    context text,
    query text,
    query_pos integer,
    location text,
    application_name text
)
SERVER log_server
OPTIONS (
    filename_pattern '*.log'
);

Adjust the columns as necessary to match your log format.

6. Query the logs:

You can now query the foreign table as if it were a regular table:

SELECT log_time, user_name, database_name, message
FROM postgres_logs
WHERE error_severity = 'ERROR'
  AND log_time > NOW() - INTERVAL '1 day'
ORDER BY log_time DESC;

This query retrieves all error messages from the last 24 hours.

7. Performance considerations:

• The LOG FDW reads log files directly, which can be slow for large log files or frequent queries.
• Consider creating materialized views or summary tables that are refreshed periodically for frequently accessed log data.
• Use appropriate WHERE clauses to limit the amount of data scanned.

8. Maintenance:

• Regularly archive old log files to prevent the foreign table from becoming too large.
• Update the foreign table definition if you change your log_line_prefix or add new log file locations.

9. Security:

• Ensure that only authorized users have access to the foreign server and user mappings.
• Be cautious about exposing sensitive information that may be present in log files.

By following these steps, you should be able to effectively use the PostgreSQL LOG FDW to query log data directly from within PostgreSQL. This approach can be particularly useful for real-time log analysis and integrating log data with other database information.

[bill-ramos-rmoswi]

You can put your JSON logs in the Dev_Tools folder. It would be good to add some Query examples as well. Show me the errors over the last 15 mins. Also, make sure to turn on other log parameters like:

• log_checkpoints
• log_connections
• log_duration
• log_error_verbosity
• log_line_prefix
• log_lock_waits
• log_statement with all

And also use some of the Babelfish specific parameters like:

• babelfishpg_tds.tds_debug_log_level with a value of 1 and test with 3

I found that these confused the Aurora log_fdw with the resulting FDW table.

[staticlibs]

Added log_fdw description for CSV logs as a separate page. Also extended JSON page including an example.