Syslog has been a PostgreSQL logging target for a long time, yet it sees little use in practice. On current Linux distributions logging is usually handled by journald, which implements the syslog API, so that is the focus here.
Facilities, severities and the journal
On Unix systems syslog is the de-facto logging standard. A syslog daemon accepts messages from processes and handles them according to its configuration. Each message carries a facility describing the type of system it came from and a severity. Predefined facilities such as kernel, mail or security exist, but none are reserved for PostgreSQL or databases in general; the custom-use facilities local0 through local7 are the ones available to PostgreSQL. The severity is derived from the setting mapped in the table below:
| PostgreSQL message level | Syslog severity |
|---|---|
DEBUG5 |
debug |
DEBUG4 |
|
DEBUG3 |
|
DEBUG2 |
|
DEBUG1 |
|
LOG |
info |
INFO |
|
NOTICE |
notice |
WARNING |
|
ERROR |
warning |
FATAL |
err |
PANIC |
crit |
Most Linux distributions rely on systemd as the init process, as a manager that autostarts and supervises system services such as PostgreSQL, and for logging. Its logging daemon is journald, which speaks the syslog API and can therefore be paired with PostgreSQL. journald is intended for local logging on a single machine; a central log collection setup calls for systemd instead. The PostgreSQL side of the configuration is the same regardless of which syslog implementation you use.
PostgreSQL configuration
Only postgresql.conf needs to be edited:
PgSQL
|
1 2 3 4 5 6 7 8 9 10 11 |
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 ' |
A reload is enough afterwards, unless logging_collector changed, in which case a restart is required. When several PostgreSQL clusters run on one machine, giving each a distinct syslog_ident or syslog_facility lets you inspect each cluster's log separately.
Reading the log
journald messages are read with journalctl. The last 20 messages logged are shown by:
Shell
|
1 |
journalctl --facility=local0 -n 20 |
Further options worth knowing:
| 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) |
Access control
PostgreSQL log contents are sensitive — depending on configuration they may include SQL statements with their parameters and passwords — so unauthorized access must be prevented. With journald this is largely built in: journalctl reveals logs only to members of particular operating system groups. On the author's Fedora 39 Linux those groups are adm, systemd-journal and wheel, which may differ on other distributions. It is also worth checking the protection on the files journald writes: on that system they live in /var/log/journal and are readable only by users in the systemd-journal group.
Overhead and disk usage
Logging is a known performance concern, and syslog logging is no exception, since PostgreSQL forwards all messages to the syslog daemon. journald does cap how many messages it keeps and discards some when the volume becomes too high, which limits the fallout from excessive logging. It also constrains log growth: the maximum size is configured in /etc/systemd/journald.conf, making it easy to stop the log from filling the disk.
