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.
pg_journal is not yet finished. The current version has some caveats:
journalctlcannot currently display multiline log messages, such as
log_statementmessages for multiline queries. These messages are shown as:
Sep 12 00:45:47 hostname postgres: [123B blob data]
Currently, the default
journalctloutput only displays the main log message without HINT/DETAIL etc lines. See below how to access the other fields.
Under high log traffic, systemd can drop some messages. This may be due to a bug or limitation in the current systemd version.
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_libraries = '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.
By default, enabling pg_journal prevents log messages from appearing in other
log destinations (log file, syslog and csvlog). If you want to duplicate
messages to other log destinations too, you can to add the
custom_variable_classes = 'pg_journal' # PostgreSQL 9.1 and earlier only pg_journal.passthrough_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:
% journalctl -f _COMM=postgres Mar 07 18:30:27 hostname postgres: LOG: loaded library "pg_journal" Mar 07 18:30:27 hostname postgres: LOG: database system was shut down at 2012-03-07 18:30:26 EET Mar 07 18:30:27 hostname postgres: LOG: database system is ready to accept connections Mar 07 18:30:27 hostname postgres: LOG: autovacuum launcher started
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):
% journalctl -f _COMM=postgres -o verbose [...] Wed, 07 Mar 2012 18:38:36 +0200 [...] MESSAGE=LOG: checkpoints are occurring too frequently (2 seconds apart) PRIORITY=6 PGLEVEL=15 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 [...] MESSAGE=ERROR: canceling statement due to user request PRIORITY=4 PGLEVEL=20 SQLSTATE=57014 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,
MESSAGE: Primary log message and severity.
PRIORITY: Syslog priority level of message (number).
PGLEVEL: PostgreSQL log level (number).
SQLSTATE: SQL error code, [see PostgreSQL documentation] (http://www.postgresql.org/docs/current/static/errcodes-appendix.html).
DETAIL: Log message detail.
HINT: Log message hint.
QUERY: Internal query.
CONTEXT: Context where the error occurred.
STATEMENT: Statement/query that caused this error.
SCHEMA: Schema containing the object related to the error. (PostgreSQL versions 9.3 and later)
TABLE: The table name this error refers to. (9.3+)
COLUMN: Table column name. (9.3+)
DATATYPE: Data type. (9.3+)
CONSTRAINT: Constriant name. (9.3+)
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
SYSLOG_IDENTIFIER: Value of the
syslog_identconfig option. (Change requires restart)
SYSLOG_IDENTIFIERis now set from the
syslog_identGUC variable, rather than process title, which changes. Makes journalctl output prettier.
- Fix build with PostgreSQL 9.3 and later.
- Add new fields SCHEMA, TABLE, COLUMN, DATATYPE, CONSTRAINT, introduced in PostgreSQL 9.3.
- Included a LICENSE file with the source package. No need to rebuild.
- The meaning of
skip_server_logsetting was inverted and renamed to
- Log message severity is now always included in MESSAGE for consistency with PostgreSQL logs
- Minor fixes and improvements
- Initial release
Bugs should be reported to pg_journal's GitHub issue tracker.
Copyright and License
Copyright (c) 2012-2013 Marti Raudsepp
pg_journal and all related files are available under The PostgreSQL License. See LICENSE file for details.