-
Add
pgloginshared_preload_librariesinpostgresql.confshared_preload_libraries = 'pglog'
-
Install the
pglogextension in a database, using the following command:CREATE EXTENSION pglog;
- pglog.directory
-
Where log data files will be spooled by the PostgreSQL server (by default
pglog_spoolunder$PGDATA).Examplepglog.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'.
Examplepglog.min_messages = 'ERROR'
- pglog.rotation_age
-
Automatic spool file rotation will occur after N minutes. Default 1 day. 0 disables automatic rotation.
Examplepglog.rotation_age = '1d'
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;
-
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.directorywith 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
pglogcan be installed on every database and allow to view only messages for that database)