Postgresql enables SQL execution statement collection process

Modify the configuration file

1. Open the Postgresql configuration file, such as: C:\Program Files\PostgreSQL\14\data\
2. Modify as follows: If the field is commented #, remove #

log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%'
log_statement = 'all'

log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%' # log file name pattern,
log_statement = 'all'

log_statement: The log_statement parameter controls which SQL statements are recorded. Valid values are none(off), ddl, mod and all (all statements).
ddl records all data definition statements, such as CREATE, ALTER and DROP statements.
mod records all ddl statements, as well as data modification statements, such as INSERT, UPDATE, DELETE, TRUNCATE and COPY FROM.

View log

Enter the log storage directory: PostgreSQL installation directory\data\log, such as: C:\Program Files\PostgreSQL\14\data\log, open the log file in the directory to view it

--View the configuration file path

SHOW config_file;
ALTER SYSTEM SET log_statement = 'all';

---Query system configuration variables

SELECT * FROM pg_settings;
SHOW log_statement;
SHOW log_filename;
SHOW log_directory;
SELECT name, setting, unit, short_desc, vartype
FROM pg_settings
WHERE name = 'log_statement';

#Whether to enable log collection (including system logs, error logs, etc.)logging_collector = on
#Log file directory.  Subdirectories relative to $PGDATAlog_directory = 'log' 
#Log file namelog_filename = 'postgresql-%Y-%m-%d_%H%M%'
#Login file permissions.  Default is 0600.  If you want other users on the server to be able to read it, then 0644 (there is a risk of leaking information)log_file_mode = 0600
#Switch logs by log file duration.  Default 1d means 24 hours.  0 is not to switch according to the durationlog_rotation_age = 1d
#Switch logs by log file size.  The default is 10MB.  0 is not toggle by sizelog_rotation_size = 0
#Log content item definitionlog_line_prefix = '%m [%p] %a %u %d %r '
#------ The following is closely related to recording SQL statements -------#Record SQL range, types can be: none, ddl, mod, alllog_statement = 'mod'
#Record the execution time of the statement (in milliseconds).  The execution time of the statement is recorded only if this threshold is exceeded# -1 is disabled, 
# 0 logs all statements and their durations
# &gt; 0 logs only statements running at least this number of milliseconds
log_min_duration_statement = 0
#Whether to record the connection creation (for example, login)log_connections = on
#Whether to record the connection port (for example, log out)log_connections = on
#Whether to record the acquisition lock timeout.  When the session gets lock time is greater than or equal to the deadlock_timeout configuration, recordlog_lock_waits = on

This is the article about collecting Postgresql-enabled SQL execution statements. For more related Postgresql-enabled SQL execution statements, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!