How to Log Queries in PostgreSQL

Enabling logging within PostgreSQL is made quite easy by altering a handful of configuration settings and then restarting the server. While these settings can be altered “in memory”, thereby enabling temporary logging for only that particular client session, in this tutorial we’ll cover how to configure postgres to permanently create iterative log files for all sessions and connections.

Locating the Configuration File

If you are unsure where the postgresql.conf config file is located, the simplest method for finding the location is to connect to the postgres client (psql) and issue the SHOW config_file; command:

postgres=# SHOW config_file;
               config_file
------------------------------------------
 /etc/postgresql/9.3/main/postgresql.conf

In this case, we can see the path to the postgresql.conf file for this server is /etc/postgresql/9.3/main/postgresql.conf. Now just open that file with your favorite text editor and we can start changing settings:

$ vim /etc/postgresql/9.3/main/postgresql.conf

Locate the Data Directory Path

It’s also a good idea to confirm the path of the data directory for your postgres installation. This will be useful later on, and retrieving the path is a matter of another simple SHOW statement:

postgres=# SHOW data_directory;
        data_directory
------------------------------
 /var/lib/postgresql/9.3/main

On some installations, the configuration file and the data directory will be along the same path, while in others (like this example), they are different. Either way, copy down this data directory path for later use.

Configuring PostgreSQL to Generate Log Output

With the postgresql.conf file open, scroll down to the ERROR REPORTING AND LOGGING section and you’ll likely see a number of configuration options commented out. The most critical of these settings are log_destination and logging_collector. Below are the recommended settings, though feel free to change these to suit your own needs:

#------------------------------------------------------------------------------
# ERROR REPORTING AND LOGGING
#------------------------------------------------------------------------------

# - Where to Log -

log_destination = 'csvlog'              # Valid values are combinations of
                                        # stderr, csvlog, syslog, and eventlog,
                                        # depending on platform.  csvlog
                                        # requires logging_collector to be on.

# This is used when logging to stderr:
logging_collector = on          # Enable capturing of stderr and csvlog
                                        # into log files. Required to be on for
                                        # csvlogs.
                                        # (change requires restart)

# These are only used if logging_collector is on:
log_directory = 'pg_log'                # directory where log files are written,
                                        # can be absolute or relative to PGDATA
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # log file name pattern,
                                        # can include strftime() escapes

Here we’re telling postgres to generate logs in the CSV format and to output them to the pg_log directory (within the data directory). We’ve also uncommented the log_filename setting to produce some proper name including timestamps for the log files.

You can find detailed information on all these settings within the official documentation.

Restart the PostgreSQL Service

The final step is to restart the PostgreSQL service so that these settings, in particular logging_collector, will take effect. Performing a postgres restart will differ from system to system, but typically for a UNIX system the command will look something like this:

$ service postgresql restart
 * Restarting PostgreSQL 9.3 database server                               [ OK ]

Verifying Log Generation

Once the system has been restarted logging should begin immediately. To ensure this is the case, navigate to the data/pg_log directory of your postgres installation. Remember that we grabbed the data directory path earlier, so simply navigate to that directory by adding /pg_log to the end to get into the log directory:

$ cd /var/lib/postgresql/9.3/main/pg_log

Now list the files and you should see a log file has been created following the previous service restart:

$ ls -l
-rw------- 1 postgres postgres 935 Apr 13 20:30 postgresql-2016-04-13_203022.csv

There we have it; automatically generated log files are enabled with PostgreSQL by changing just a few configuration settings.