This article explains how to enable query logging in PostgreSQL. One of the most useful features of PostgreSQL includes is query logging, which allows you to better understand the performance of your databases and the queries you run on them.
PostgreSQL Log files
PostgreSQL’s log files will contain any error output from the server process, as well as status from the process. These logs will be created in the directory:
/var/logs/
In addition to this, you can log queries themselves for diagnostics and reporting.
Updating the PostgreSQL Configuration File
Modifying PostgreSQL’s logging options is done through its configuration file. You can find the file by running:
sudo -u postgres psql -c 'SHOW config_file'
Note the use of the sudo command to run the command as the posgres system user.
Enabling Logging in PostgreSQL Configuration
Once you have located the configuration file currently in use by PostgreSQL, edit it by running
sudo nano /path/to/postgresql.conf
Then, find the following line:
#log_statement = 'none'
Uncomment it, and change none to all so it looks like the following:
log_statement = 'all'
The other options for this configuration setting are:
- ddl – Logs all definition statements, such as CREATE, ALTER, and DROP.
- mod – Logs all statements from ddl, as well as data-modifying statements like INSERT, UPDATE, DELETE, TRUNCATE, and COPY FROM. PREPARE, EXECUTE, and EXPLAIN ANALYZE statements are also logged if their content is appropriate.
- all – Logs all statements.
To save and quit in nano, press CTRL + X.
Query Log Destination
You can also change the destination of the query log files – this directory must be writable by the postgresql user.
Change this by editing the log_destination entry in your PostgreSQL configuration file.
By default, query logs will be stored in the directory pg_log in the PostgreSQL data directory, which can be found by running:
sudo -u postgres psql -c 'SHOW data_directory'
Applying PostgreSQL Configuration Changes
Once you’ve updated your configuration, restart the PostgreSQL service by following our instructions here.
Queries executed will now be logged to the location configured in the previous step. The log files will named postgresql-YYYY-MM-DD_HHMMSS.log and will contain details about each query, including the query string, and execution time.
Query logs can take up a lot of disk space, especially if your database is busy. It’s best to periodically clear these files, or, rotate them out so that old files are regularly cleared out as new ones are created, reducing disk usage. logrotate is included with most Linux distributions and does just this.
To configure logrotate, add a configuration file for PostgreSQL query logging by running:
sudo nano /etc/logrotate/.d/postgresql-query
…and adding the following configuration to the file:
/path/to/postgresql-*.log {
size 50M
rotate 10
compress
delaycompress
missingok
notifempty
copytruncate
}
This configuration will keep only the most recent 10 log files and compress them to save disk space. A limit of 50 megabytes will also be imposed. The logrotate utility will run periodically to rotate the log files according to this configuration.
Once the configuration has been updated, run:
sudo service logrotate restart
…to apply it by restarting the logrotate service.
