Where are my PostgreSQL logs?

Authors: Supriya Khosare and William Ivanski.

PostgreSQL users can choose to handle database logs in a variety of ways, or even in a combination of ways. However, it might be challenging to even locate the logs, particularly for inexperienced users or those acclimating to a new system. This article will help locating the relevant logs.

The default location of PostgreSQL logs can vary depending on your operating system and how PostgreSQL was installed. Here are some common default locations:

1. Linux

• Data Directory: /var/lib/pgsql/<version>/data/
• Log Directory: /var/lib/pgsql/<version>/data/log/

2. macOS

• Data Directory: /Library/PostgreSQL/<version>/data/
• Log Directory: /Library/PostgreSQL/<version>/data/log/

3. Windows

• Data Directory: C:\Program Files\PostgreSQL\<version>\data\
• Log Directory: C:\Program Files\PostgreSQL\<version>\data\log\

In these paths, <version> represents the major version of PostgreSQL you are using, such as 14, 15, 16, etc.

If you have a non-standard installation or if the logs have been configured to be stored elsewhere, you might need to consult your PostgreSQL configuration file (postgresql.conf) to find the exact location. First, connect to PostgreSQL with psql, pgadmin, or some other client where you can run SQL to see the log_destination setting which tells PostgreSQL where log entries should go.

PostgreSQL supports several methods for logging server messages. We can have stderr, csvlog, jsonlog, and syslog for log_destination parameter. We will see one by one.

Log messages are sent to the standard error output (stderr). This is the default destination if no other destination is specified.

When log_destination is set to stderr, you can also tell PostgreSQL to redirect its stderr output internally, with the logging_collector option. If logging_collector is turned on, then check the log_directory setting in the postgresql.conf file or check from psql prompt.

For example:

log_destination = 'stderr'
logging_collector = 'on'
data_directory = '/var/lib/pgsql/data/'
log_directory = 'log'

In this case, log is a directory path relative to the PGDATA directory, so the log files will be under PGDATA/log, but you can also specify an absolute path starting with /.

When log_destination = 'stderr' and logging_collector = on, you can also see the log file path in a file PGDATA/current_logfiles:

$ cat /var/lib/pgsql/data/current_logfiles
stderr log/postgresql-Fri.log

PostgreSQL superusers (or users with enough privileges) can also locate the log file by leveraging the function pg_current_logfile():

postgres=# SELECT pg_current_logfile();
   pg_current_logfile
------------------------
 log/postgresql-Fri.log

If log_destination = 'stderr' but logging_collector is disabled, then you can still locate the Postgres logs using the /proc filesystem.

First, connect to Postgres and get the PID of the backend you are connected to, for example:

postgres=# SELECT pg_backend_pid();
 pg_backend_pid
----------------
           7436
(1 row)

Then check /proc/PID/fd/2 (replacing the PID with the output from the query above), which is a symbolic link to the current log file:

postgres=# \! ls -lh /proc/7436/fd/1
l-wx------ 1 postgres postgres 64 Jun  4 17:54 /proc/7436/fd/1 -> /var/log/postgresql/postgresql-14-main.log

Log messages are written to CSV (Comma-Separated Values) files. Each log entry is written as a separate line in the CSV file, making it easy to import and analyze log data using external tools.

To know more regarding how to import CSV logs into a table, check this article.

Log messages are sent to the system logging facility (syslog) on Unix-like systems. The messages are written to the system log files according to the configuration of the syslog daemon.

Check the PostgreSQL settings syslog_facility and syslog_ident to understand how to grep for Postgres logs from the system logs (in case they are included in the same system log file).

Log messages are sent to the Windows event log on Windows systems. This destination is only available on Windows platforms.

You can also specify multiple destinations by separating them with commas, for example:

log_destination = 'stderr,csvlog'

In the above configuration, log messages would be sent to both stderr and CSV files.