PostgreSQL logger foreign data wrapper

Requirements

• PostgreSQL 9.3

Installation

• Add pglog in shared_preload_libraries in postgresql.conf

  shared_preload_libraries = 'pglog'

• Install the pglog extension in a database, using the following command:

  CREATE EXTENSION pglog;

Configuration options

pglog.directory

Where log data files will be spooled by the PostgreSQL server (by default pglog_spool under $PGDATA).

Example

pglog.directory = '/var/log/postgres/pglog'

pglog.min_messages

Sets the message levels that are logged. Each level includes all the levels that follow it. The later the level, the fewer messages are sent. Default 'WARNING'. Possible values: 'DEBUG', 'INFO', 'NOTICE', 'WARNING', 'ERROR', 'LOG', 'FATAL', 'PANIC'.

Example

pglog.min_messages = 'ERROR'

pglog.rotation_age

Automatic spool file rotation will occur after N minutes. Default 1 day. 0 disables automatic rotation.

Example

pglog.rotation_age = '1d'

Overview

The pglog extension will log system events in a spooling directory specified by pglog.directory parameter.

The pglog extension allows users to access events via SQL through the pglog foreign table. The extension must be installed on any database for which you want to make this feature available.

The extension is responsible for:

CREATE SERVER pglog_server
  FOREIGN DATA WRAPPER pglog;

CREATE TYPE pglog_severity AS ENUM (
	'DEBUG',
	'INFO',
	'NOTICE',
	'WARNING',
	'ERROR',
	'LOG',
	'FATAL',
	'PANIC',
	'???'
);

CREATE FOREIGN TABLE pglog
(
  log_time timestamp(3) with time zone,
  user_name text,
  database_name text,
  process_id integer,
  connection_from text,
  session_id text,
  session_line_num bigint,
  command_tag text,
  session_start_time timestamp with time zone,
  virtual_transaction_id text,
  transaction_id bigint,
  error_severity pglog_severity,
  sql_state_code text,
  message text,
  detail text,
  hint text,
  internal_query text,
  internal_query_pos integer,
  context text,
  query text,
  query_pos integer,
  location text,
  application_name text
) SERVER pglog_server;

Initial limitations

• Limited spool file rotation. A spool file is written in the appropriate file name but no file is yet automatically deleted.

• No support for reliable event spooling in a high concurrent environment (currently it simply appends the events to the spool file). In the future it can be implemented using a dedicated process that gathers the events and append them to the spool file.

• Support for up to 16 spool files (it could be made higher or set on an option)

• No support for ordering of log files (currently files are read as they are from pglog.directory with the order returned by the filesystem)

• No support for condition push down in WHERE queries

• No support for ANALYSE

• No support for date partitioning

• No support for security (full control for superusers, limited to normal users ^[1])

• No support for current database only (so that pglog can be installed on every database and allow to view only messages for that database)

Links

• http://www.postgresql.org/docs/9.3/static/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-CSVLOG

---

1. User control can be limited to string matching or current roles in the database server