pg_journal

Description

This is a PostgreSQL preload module for sending log messages directly to the systemd journal log.

Prerequisites:

  • PostgreSQL version 9.2+ (earlier versions supported with a server patch).
  • systemd v38 or newer with libsystemd-journal installed.

Configuration

Once pg_journal is installed, you can enable it in the configuration file (see README.md for installation instructions). Find the postgresql.conf file (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 skip_server_log setting:

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.

Usage

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[16028]: loaded library "pg_journal"
Mar 07 18:30:27 hostname postgres[16030]: database system was shut down at 2012-03-07 18:30:26 EET
Mar 07 18:30:27 hostname postgres[16034]: autovacuum launcher started
Mar 07 18:30:27 hostname postgres[16028]: 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
    [...]

Log fields

pg_journal adds the following log fields to log messages:

  • MESSAGE_ID: Only one message ID is defined, a63699368b304b4cb51bce5644736306, for log_statement log messages.
  • 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 application_name variable.

Support

Bugs can be reported to pg_journal's GitHub issue tracker.

Author

Marti Raudsepp

Copyright and License

Copyright (c) 2012 Marti Raudsepp

pg_journal and all related files are available under The PostgreSQL License