1. Overview: The postgresql run log is not enabled by default. If you want to query the log, you need to manually enable it.
2. Modify the configuration file and enable the run log:
vim log_destination = ‘csvlog' logging_collector = on log_directory = ‘pg_log' log_filename = ‘postgresql-%Y-%m-%d_%H%M%' log_rotation_age = 1d log_rotation_size = 100MB log_min_messages = debug1 # Record slow SQL executionlog_min_duration_statement = 60 log_checkpoints = on log_connections = on log_disconnections = on log_duration = on log_line_prefix = ‘%m' # Monitor long-term locks in the databaselog_lock_waits = on # Record DDL operationslog_statement = ‘all'
[root@localhost data]# cd pg_log [root@localhost pg_log]# ls postgresql-2023-07-27_22.csv postgresql-2023-07-27_22.log postgresql-2023-07-28_14.csv postgresql-2023-07-28_14.log
3. Import the run log into the database:
1. Create a log table:
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, backend_type text, leader_pid integer, query_id bigint, PRIMARY KEY (session_id, session_line_num) )
2. Copy the log file to the database
DO $func$BEGIN EXECUTE $$ COPY public.postgres_log from '/opt/postgresql/data/pg_log/postgresql-$$ || to_char(now(),'YYYY-MM-DD_HH24') || $$.csv' DELIMITER ',' CSV HEADER; $$;END;$func$ LANGUAGE plpgsql;
SELECT * from postgres_log
4. Others: Some parameter descriptions
log_destination is stderr by default, and there are three options: stderr, csvlog, and syslog; if csvlog is used, logging_collector must be enabled. You can also use csvlog and stderr at the same time, which will record logs in two formats.
log_rotation_age When logging_collector is enabled, this parameter determines the maximum life of an individual log file. When these minutes pass, a new log file will be created. Setting this parameter to zero will disable new time-based log file creation. 1d represents 1 day.
log_rotation_size: When logging_collector is enabled, this parameter determines the maximum size of an individual log file. When so many kilobytes are sent to a log file, a new log file will be created. Setting this parameter to zero will disable new log file creation based on size.
log_min_messages: Controls which message levels are written to the server log. Valid values are DEBUG5, DEBUG4, DEBUG3, DEBUG2, DEBUG1, INFO, NOTICE, WARNING, ERROR, LOG, FATAL, and PANIC. Each level includes all levels later. The lower the level, the fewer messages are sent. The default value is WARNING.
log_min_duration_statement: equivalent to mysql's long_query_time, slow SQL recording, SQL that exceeds this time will be recorded in the log.
log_connections: causes each attempt to connect to the server to be recorded, and the successful completion of client authentication will also be recorded. Only the superuser can change this parameter at the beginning of the session, and it cannot be changed during the session. The default is off.
log_disconnections: This causes the session to terminate and will also be recorded. The information provided by the log output is similar to log_connections, but also the duration of the session. Only the superuser can change this parameter at the beginning of the session, and it cannot be changed during the session. The default is off.
log_duration: causes the duration of each completed statement to be recorded. The default value is off. If log_duration is on and log_min_duration_statement is positive, all durations will be recorded, but only statements that exceed the threshold will be recorded in the query text. This behavior helps collect statistics in high load installations
log_line_prefix: Set the log output format (can record time, user name, database name, client IP and port, convenient for positioning issues) The default value is '%m [%p]', which records the timestamp and process ID
log_lock_waits: Controls whether a log message is to be generated when a session waits for a lock to exceed deadlock_timeout. This helps determine whether lock waiting causes poor performance. The default value is off
log_statement: Controls which SQL statements are recorded. Valid values are none (off), ddl, mod, and all (all statements). The default value is none
Summarize
This is the article about the detailed steps and parameters of postgresql to enable pg_log log. For more related postgresql to enable pg_log log content, please search for my previous articles or continue browsing the following related articles. I hope everyone will support me in the future!