In this article, we will see how to log PostgreSQL queries step by step. We are going to configure PostgreSQL to permanently create iterative log files for all sessions and connections.
Steps to log PostgreSQL queries:
1. First lets verify the postgresql.conf file locatiob by running below command frop psql.
show config_file;
2. Before modifying any settings in postgresql.conf file, take backup of it.
cp /etc/postgresql/12/main/postgresql.conf /etc/postgresql/12/main/postgresql.conf_log
3. Now open postgresql.conf file and navigate to “REPORTING and LOGGING” section which contains the parameters to be configured for logging queries in PostgreSQL.
Notice in this image all parameters are disabled(# at starting of parameter means it is disabled).
4. Now, enable following parameters and change settings Or simply add following lines at the of postgresql.conf file and save it.
log_destination = 'csvlog' logging_collector = on log_directory = 'pg_log' log_statement = 'all' log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
5. Now, restart the service using any of the following commands.