Syslog logging with journald in PostgreSQL

Logging to syslog is not a new PostgreSQL feature at all. Still, I find few people using it, so perhaps an introductory tutorial will be helpful. Since today's Linux distributions normally use journald for logging, I'll focus on that.

Syslog terminology

Syslog is the de-facto logging standard on Unix systems. A syslog daemon receives messages from processes and processes them according to its configuration.

Every syslog message has a facility (type of system) and a severity. There are a couple of pre-defined facilities, like kernel, mail or security, but none for PostgreSQL or databases. However, there are facilities local0 to local7 for custom use, and these are what we have to use with PostgreSQL. When logging PostgreSQL to syslog, the severity is determined by this table:

PostgreSQL message level	Syslog severity
DEBUG5	debug
DEBUG4
DEBUG3
DEBUG2
DEBUG1
LOG	info
INFO
NOTICE	notice
WARNING
ERROR	warning
FATAL	err
PANIC	crit

Syslog and journald

These days, most Linux distributions use systemd for most operation system management tasks:

• as init process (process 1, which starts all other processes)
• to autostart and manage system services (like PostgreSQL)
• for logging

the systemd daemon for logging is journald, and it implements the syslog API, so it can be used with PostgreSQL. I'll describe logging to journald in the rest of this article, although the PostgreSQL configuration is the same for any syslog implementation. journald is for local logging on a single machine. If you want to collect all logs on a central system, you should use systemd instead.

Configuring PostgreSQL for syslog logging

Setting up the PostgreSQL side is simple: all you have to do is to edit postgresql.conf and set[code

language="text"] log_destination = 'syslog' # must be "on" only if PostgreSQL writes a log file logging_collector = off # "local0" is the default value syslog_facility = 'local0' # "postgres" is the default value syslog_ident = postgres # no point in logging the time or process ID # (systemd logs these anyway) # but logging user and database might be good log_line_prefix = '%q%u@%d ' [/code]

Then reload PostgreSQL (if you changed logging_collector, you need to restart).

If you are running more than one PostgreSQL cluster on a machine, it is a good idea to use a different syslog_ident or syslog_facility for each one. That allows you to view the log for each cluster separately.

Viewing journald messages

You can view journald messages with the journalctl command. To view the last 20 messages logged to

Other useful options are:

option	description
-t postgres --identifier=postgres	see only messages with the identifier postgres (useful if you have several clusters with different identifiers)
-S 09:00:00 --since=09:00:00	show only messages since 9 am today (if you need a different date, use -S '2024-03-13 09:00:00')
-S today --since=today	show only messages since midnight today
-S -1h --since=-1h	show only messages from the last hour
-p warning --priority=warning	show only ERROR, FATAL and PANIC messages
-g 'syntax error' --grep='syntax error'	show only matching messages (supports Perl regular expressions)
-f --follow	continuously print messages as they are logged until the command is interrupted (like tail -f)

journald and permissions

The PostgreSQL log is sensitive information: it may contain SQL statements and their parameters as well as passwords. So it is important to keep the PostgreSQL log from unauthorized access. This is fairly simple with journald: journalctl only allows members of certain operating system groups to see logs. On my Fedora 39 Linux, these groups are adm, systemd-journal and wheel, but that may be different for other distributions.

You should also verify that the files where journald stores data are protected. On my system, they reside in /var/log/journal, and only users in the systemd-journal group can read them.

Performance and space management with journald

It is well known that logging can be a performance problem. This is also the case with syslog logging, because PostgreSQL sends all messages to the syslog daemon. However, journald limits the number of messages it is ready to retain and will drop some messages if the volume gets too high. That should limit the impact of excessive logging.

journald also keeps the growth of the log files at bay. You can configure in /etc/systemd/journald.conf how big the log can grow. That way, it is easy to keep the log from filling the disk.

Conclusion

It is simple to configure syslog logging in PostgreSQL. If journald is set up on your machine, there is nothing more you have to do. journalctl with its many options makes reading the log much more convenient than a tradition log file. While I wrote this article, I switched my PostgreSQL instance to syslog logging, and I don't think I will switch back.