This is a PostgreSQL preload module for sending log messages directly to the systemd journal log.
- PostgreSQL version 9.2+ (earlier versions supported with a server patch).
- systemd v38 or newer with libsystemd-journal installed.
Once pg_journal is installed, you can enable it in the configuration file (see
README.md for installation instructions). Find the
(usually in your PostgreSQL data directory) and add the following line:
shared_preload_libaries = 'pg_journal'
You need to restart your server for this to take effect. After that, log
messages are automatically sent to journal. pg_journal follows the usual
logging settings (
log_min_messages, etc) to decide what to log.
If you want to prevent logged messages from appearing in the normal server log,
you can add the
custom_variable_classes = 'pg_journal' # PostgreSQL 9.1 and earlier only pg_journal.skip_server_log = on
Note that if journal logging fails for any reason, then pg_journal falls back to the server log despite this setting, allowing you to debug the issue.
After making the configuration changes above and restarting, PostgreSQL should
automatically start logging to journal. You can use the following command to
see the latest log messages (like
tail -f) from 'postgres' processes:
% systemd-journalctl -f _COMM=postgres Mar 07 18:30:27 hostname postgres: loaded library "pg_journal" Mar 07 18:30:27 hostname postgres: database system was shut down at 2012-03-07 18:30:26 EET Mar 07 18:30:27 hostname postgres: autovacuum launcher started Mar 07 18:30:27 hostname postgres: database system is ready to accept connections
Note that unlike regular PostgreSQL logging, only the primary message of each error is displayed -- without DETAIL or HINT lines. This will probably change in a future version. Until then, you need to use journalctl in the verbose mode, which displays all logged fields (documented below):
% systemd-journalctl -f _COMM=postgres -o verbose [...] Wed, 07 Mar 2012 18:38:36 +0200 [...] PRIORITY=6 PGLEVEL=15 MESSAGE=checkpoints are occurring too frequently (2 seconds apart) HINT=Consider increasing the configuration parameter "checkpoint_segments". CODE_FILE=checkpointer.c CODE_LINE=488 CODE_FUNCTION=CheckpointerMain _TRANSPORT=journal [... other systemd-specific fields ...] Wed, 07 Mar 2012 18:38:37 +0200 [...] PRIORITY=4 PGLEVEL=20 SQLSTATE=57014 MESSAGE=canceling statement due to user request STATEMENT=insert into foo select generate_series(1,10000000); CODE_FILE=postgres.c CODE_LINE=2914 CODE_FUNCTION=ProcessInterrupts PGUSER=joe PGDATABASE=sixpack PGHOST=[local] PGAPPNAME=psql _TRANSPORT=journal [...]
pg_journal adds the following log fields to log messages:
MESSAGE_ID: Only one message ID is defined,
PRIORITY: Syslog priority level of message (number).
PGLEVEL: PostgreSQL log level (number).
SQLSTATE: SQL error code, see PostgreSQL documentation.
MESSAGE: Primary log message.
DETAIL: Log message detail.
HINT: Log message hint.
QUERY: Internal query.
CONTEXT: Context where the error occurred.
STATEMENT: Statement/query that caused this error.
CODE_FILE: PostgreSQL source file name where this error was reported.
CODE_LINE: PostgreSQL source line number where this error was reported.
CODE_FUNCTION: PostgreSQL internal function name.
PGUSER: User name of client.
PGDATABASE: Database where the client was connected to.
PGHOST: Hostname or host:port where the client connected from.
PGAPPNAME: Value of the
Bugs can be reported to pg_journal's GitHub issue tracker.
Copyright and License
Copyright (c) 2012 Marti Raudsepp
pg_journal and all related files are available under The PostgreSQL License