This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing a CSV Log file option which we can insert into the table of PostgreSQL Database.
Once you generate the PostgreSQL Logs in CSV format, we can quickly dump that log into a database table.
If log data available in the table, more effectively we can use that data.
PostgreSQL: Important Parameters to enable Log for all Queries
Check the below steps:
Change below parameter in postgresql.conf:
|
1 |
log_destination = csvlog |
Create a below table for storing the CSV logs:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
CREATE TABLE postgres_log ( log_time timestamp(3) 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, PRIMARY KEY (session_id, session_line_num) ); |
Copy CSV logs into table:
Get your CSV log file path and copy into a table
|
1 |
COPY postgres_log FROM '/home/dbrnd/postgresql/logs/logfile_1.csv' WITH csv; |
Leave a Reply