PostgreSQL Server Log Configuration

2018-02-06 By Vonng In Article 820 words 4 min

VonngAuthor of PG Exporter and Pigsty, working on PostgreSQL infrastructure and monitoring.

This article was written in 2018. The table definitions below use the 23-column PostgreSQL 10 CSV format, and the Filebeat example uses the legacy prospectors configuration.

CSV logs can be imported into PostgreSQL tables or queried through file_fdw for analysis.

Log Configuration

log_destination = 'csvlog'
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%a.log'
log_min_duration_statement = 1000
log_checkpoints = on
log_lock_waits = on
log_statement = 'ddl'
log_replication_commands = on
log_timezone = 'UTC'
log_autovacuum_min_duration = 1000

track_io_timing = on
track_functions = all
track_activity_query_size = 16384

The last three settings collect runtime statistics; they do not control log output. logging_collector and track_activity_query_size require a server restart. The weekday filename repeats each week. Configure rotation and retention to match how long you need to keep the files.

Log Collection

The original setup used Filebeat to forward local files to Kafka. Replace the paths and broker address with your own. This legacy example starts at the end of existing files and joins lines by a timestamp pattern, with a 20-line limit. It does not provide general CSV record framing for arbitrary multiline messages.

filebeat.prospectors:
  - type: log
    enabled: true
    paths:
      - /pg/data/log/postgresql-*.csv
    document_type: db-trace
    tail_files: true
    multiline.pattern: '^20\d\d-\d\d-\d\d'
    multiline.negate: true
    multiline.match: after
    multiline.max_lines: 20

max_cpus: 1

filebeat.config.modules:
  path: ${path.config}/modules.d/*.yml
  reload.enabled: false

queue.mem:
  events: 1024
  flush.min_events: 0
  flush.timeout: 1s

output.kafka:
  hosts: ["kafka.example.com:9092"]
  topics:
    - topic: 'log.db'

CSV Log Format

PostgreSQL can query its logs as rows. The following table matches the PostgreSQL 10 CSV fields. Later server versions add columns; use the table definition for the server that wrote the log:

-- Log table structure
create table postgresql_log
(
  log_time               timestamp(3) with time zone,
  user_name              text,
  database_name          text,
  process_id             integer,
  connection_from        text,
  session_id             text   not null,
  session_line_num       bigint not null,
  command_tag            text,
  session_start_time     timestamp with time zone,
  virtual_transaction_id text,
  transaction_id         bigint,
  error_severity         text,
  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,
  PRIMARY KEY (session_id, session_line_num)
);

Importing Logs

Import a closed CSV log file with COPY. The server must be able to read the file. For a file on the client machine, use psql’s \copy. Import complete files after rotation, and import each file only once.

COPY postgresql_log FROM '/pg/data/log/postgresql-Mon.csv' WITH (FORMAT csv);

Mapping Logs

Use file_fdw to query local CSV files without copying them into a table. Run the setup with the required extension and server-file privileges. The example assumes English weekday names and existing files under /pg/data/log; adjust the paths to match the log output. Query an individual child table if some weekday files do not yet exist.

CREATE SCHEMA IF NOT EXISTS monitor;

-- This session only
SET search_path = monitor, pg_catalog;

-- extension
CREATE EXTENSION IF NOT EXISTS file_fdw WITH SCHEMA monitor;

-- log parent table: empty
CREATE TABLE monitor.pg_log
(
  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         text,
  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,
  PRIMARY KEY (session_id, session_line_num)
);
COMMENT ON TABLE monitor.pg_log IS 'PostgreSQL csv log schema';
-- local file server
CREATE SERVER IF NOT EXISTS pg_log FOREIGN DATA WRAPPER file_fdw;
-- Change filename to actual path
CREATE FOREIGN TABLE IF NOT EXISTS monitor.pg_log_mon() INHERITS (monitor.pg_log) SERVER pg_log OPTIONS (filename '/pg/data/log/postgresql-Mon.csv', format 'csv');
CREATE FOREIGN TABLE IF NOT EXISTS monitor.pg_log_tue() INHERITS (monitor.pg_log) SERVER pg_log OPTIONS (filename '/pg/data/log/postgresql-Tue.csv', format 'csv');
CREATE FOREIGN TABLE IF NOT EXISTS monitor.pg_log_wed() INHERITS (monitor.pg_log) SERVER pg_log OPTIONS (filename '/pg/data/log/postgresql-Wed.csv', format 'csv');
CREATE FOREIGN TABLE IF NOT EXISTS monitor.pg_log_thu() INHERITS (monitor.pg_log) SERVER pg_log OPTIONS (filename '/pg/data/log/postgresql-Thu.csv', format 'csv');
CREATE FOREIGN TABLE IF NOT EXISTS monitor.pg_log_fri() INHERITS (monitor.pg_log) SERVER pg_log OPTIONS (filename '/pg/data/log/postgresql-Fri.csv', format 'csv');
CREATE FOREIGN TABLE IF NOT EXISTS monitor.pg_log_sat() INHERITS (monitor.pg_log) SERVER pg_log OPTIONS (filename '/pg/data/log/postgresql-Sat.csv', format 'csv');
CREATE FOREIGN TABLE IF NOT EXISTS monitor.pg_log_sun() INHERITS (monitor.pg_log) SERVER pg_log OPTIONS (filename '/pg/data/log/postgresql-Sun.csv', format 'csv');

Extracting Log Fields

These functions extract duration, statement text, and IPv4-shaped text from English log messages. Durations are in milliseconds. They handle the message patterns shown here, not every extended-query or localized message format:

CREATE OR REPLACE FUNCTION extract_duration(statement TEXT)
  RETURNS FLOAT AS $$
DECLARE
  found_duration BOOLEAN;
BEGIN
  SELECT position('duration' in statement) > 0
  into found_duration;
  IF found_duration
  THEN
    RETURN (SELECT regexp_matches [1] :: FLOAT
            FROM regexp_matches(statement, '^duration: ([0-9]+(?:\.[0-9]+)?) ms')
            LIMIT 1);
  ELSE
    RETURN NULL;
  END IF;
END
$$
LANGUAGE plpgsql
IMMUTABLE;

CREATE OR REPLACE FUNCTION extract_statement(statement TEXT)
  RETURNS TEXT AS $$
DECLARE
  found_statement BOOLEAN;
BEGIN
  SELECT position('statement' in statement) > 0
  into found_statement;
  IF found_statement
  THEN
    RETURN (SELECT regexp_matches [1]
            FROM regexp_matches(statement, 'statement: (.*)')
            LIMIT 1);
  ELSE
    RETURN NULL;
  END IF;
END
$$
LANGUAGE plpgsql
IMMUTABLE;

CREATE OR REPLACE FUNCTION extract_ip(app_name TEXT)
  RETURNS TEXT AS $$
DECLARE
  ip TEXT;
BEGIN
  SELECT regexp_matches [1]
  into ip
  FROM regexp_matches(app_name, '(\d+\.\d+\.\d+\.\d+)')
  LIMIT 1;
  RETURN ip;
END
$$
LANGUAGE plpgsql
IMMUTABLE;

JavaScript is required to load comments. View discussions on GitHub.