I like logging. I like logging PostgreSQL. With logs, you can see what happened. Without, you can only guess.
Setting up logging for PostgreSQL involves several parts, each of which must be completed or else I don’t get what I want. This is not a criticism of PostgreSQL. It’s a feature.
I am documenting this because each time I configure a new PostgreSQL instance, it takes me more than one iteration to get it working. The goal: this post lets both you and me get it right the first time.
The parts include:
- Telling PostgreSQL to log via syslog
- Telling FreeBSD to local postgres to /var/log/postgres.log (my preference).
- Telling PostgreSQL the things you want logged.
Changes to postgresql.conf
The file location varies with the version installed. For PostgreSQL 9.6 on FreeBSD, the file is /var/db/postgres/data96/postgresql.conf (adjust 96 according to the version installed).
I made these changes to that file.
log_destination = 'syslog' log_min_messages = notice log_min_error_statement = notice log_checkpoints = on log_lock_waits = on log_timezone = 'UTC'
By default, PostgreSQL logs to the local0 facility and is controlled by the syslog_facility in postgresql.conf. This will be used in syslog.conf (see the next section of this post).
EDIT 2019.08.20 – Other entries I added because I wanted additional logging:
log_checkpoints = on log_connections = on log_disconnections = on log_duration = on log_lock_waits = on log_statement = 'all'
The above mentioned changes require a reload: service postgresql reload
Changes to /etc/syslog.conf
Now that we have PostgreSQL logging to syslog, we want to tell syslog where to put those messages.
I changed this line in /etc/syslog.conf:
With *.notice pulling in some local0 messages, adding local0.none to the line will free the messages up for later use in the configuration file. Otherwise, the PostgreSQL messages will be in /var/log/messages.
The changed line is:
Then, to get the messages into my preferred location, I added this to the file:
NOTE: this same file name appears in the next section.
Log file rotation
For rotating my log file, I added a new file: /usr/local/etc/newsyslog.conf.d/postgresql96
/var/log/postgresql.log pgsql:wheel 640 7 * $D0 GB /var/db/postgres/data96/postmaster.pid 30
Before restarting syslog, I did this, so the destination file existed. This isn’t always/strictly necessary, but because the ownership is not chown root:wheel, I do it to get that part set.
touch /var/log/postgresql.log chown pgsql:wheel /var/log/postgresql.log
Restarting syslog is straight forward:
sudo kill -HUP `sudo cat /var/run/syslog.pid `
Now you should see PostgreSQL logging in /var/log/postgresql.log.