PostgreSQL – logging to a file

These steps were carried out on FreeBSD 11.0 with PostgreSQL 9.6 (two of my favorite tools).

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:

1. Telling PostgreSQL to log via syslog
2. Telling FreeBSD to local postgres to /var/log/postgres.log (my preference).
3. 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:

 *.notice;authpriv.none;kern.debug;lpr.info;mail.crit;news.err	/var/log/messages

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:

*.notice;authpriv.none;kern.debug;lpr.info;mail.crit;news.err;local0.none	/var/log/messages

Then, to get the messages into my preferred location, I added this to the file:

local0.*     /var/log/postgresql.log

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

Restarting syslog is straight forward:

sudo kill -HUP `sudo cat /var/run/syslog.pid `

That’s it

Now you should see PostgreSQL logging in /var/log/postgresql.log.

Enjoy