Sending PostgreSQL logs to a central server

Introduction

Your database already knows which query took eleven seconds. It just is not telling you.

PostgreSQL has excellent logging, and almost nobody turns it on. The defaults give you startup messages and fatal errors. The things you want during an incident — the slow query, the refused connection, the deadlock — are all there, and all switched off.

This guide turns on the useful settings, structures the output, and ships it somewhere you can query it.

Step 1: Configure what PostgreSQL logs

Find your config file:

SHOW config_file;

Typically /etc/postgresql/16/main/postgresql.conf on Debian/Ubuntu or /var/lib/pgsql/16/data/postgresql.conf on RHEL.

The settings that matter

# Write to files we can ship, not just stderr.
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d.log'

# JSON output — PostgreSQL 15 and later.
log_destination = 'jsonlog'

# Log any statement taking longer than 500ms.
log_min_duration_statement = 500

# Connections, disconnections, and lock waits.
log_connections = on
log_disconnections = on
log_lock_waits = on

# Log all DDL — cheap, and invaluable when something changed.
log_statement = 'ddl'

# Include useful context on every line.
log_line_prefix = '%m [%p] %q%u@%d '

# Log slow autovacuum and temp file usage.
log_autovacuum_min_duration = 1000
log_temp_files = 10240

Reload:

sudo systemctl reload postgresql

SELECT pg_reload_conf(); picks up most of these. logging_collector needs a full restart.

On log_min_duration_statement

This setting pays for the whole exercise. Handle it with care.

500 logs every statement over half a second. Set it to 0 on a busy OLTP database and you log every statement. The I/O that generates can become the performance problem you sat down to diagnose. Start at 500 or 1000. Lower it when you need more detail.

Want sampling instead of a hard threshold? PostgreSQL 13 and later have log_min_duration_sample and log_statement_sample_rate.

If you are on PostgreSQL 14 or earlier

PostgreSQL 15 added jsonlog. On older versions use CSV:

log_destination = 'csvlog'

CSV parses fine. The column order is documented and stable within a major version. It is only more annoying than JSON, because you map the columns yourself.

Step 2: Ship the logs

PostgreSQL writes to log_directory, which is relative to the data directory unless you give an absolute path. With jsonlog you get one JSON object per line — exactly what the ingest endpoint wants.

Tail and forward

#!/bin/bash
# /usr/local/bin/ship-postgres-logs.sh
set -euo pipefail

LOG_DIR=/var/lib/postgresql/16/main/log
OFFSET_DIR=/var/lib/pg-shipper
ENDPOINT="https://logs.example.com/api/v1/ingest_logs/YOUR-SOURCE-TOKEN"

mkdir -p "$OFFSET_DIR"

# PostgreSQL rotates daily, so ship the newest .json file.
LOG=$(ls -1t "$LOG_DIR"/*.json 2>/dev/null | head -1)
[ -z "$LOG" ] && exit 0

OFFSET_FILE="$OFFSET_DIR/$(basename "$LOG").offset"
offset=$(cat "$OFFSET_FILE" 2>/dev/null || echo 0)
size=$(stat -c %s "$LOG")

[ "$size" -lt "$offset" ] && offset=0    # file was truncated
[ "$size" -eq "$offset" ] && exit 0      # nothing new

tail -c "+$((offset + 1))" "$LOG" \
    | gzip \
    | curl -sf -X POST "$ENDPOINT" \
        -H "Content-Encoding: gzip" \
        --data-binary @- >/dev/null

echo "$size" > "$OFFSET_FILE"

# Drop offset files for logs that have been rotated away.
find "$OFFSET_DIR" -name '*.offset' -mtime +7 -delete

* * * * * /usr/local/bin/ship-postgres-logs.sh

PostgreSQL rotates to a new filename every day, so the script tracks an offset per file rather than one global offset. The find ... -mtime +7 -delete stops that directory from growing by one file per day forever.

Or log to syslog

You can also have PostgreSQL write to syslog and forward from there:

log_destination = 'syslog'
syslog_facility = 'LOCAL0'
syslog_ident = 'postgres'

Then follow the rsyslog guide. Already forwarding syslog? This is less work. You pay for it in structure — syslog carries the formatted message, not the fields.

Step 3: Query the logs

Here is the payoff. PostgreSQL’s jsonlog fields are documented and stable, so SQL queries against them stay simple.

Slowest queries

SELECT
    "timestamp",
    json_extract(msg, '$.user_name')   AS db_user,
    json_extract(msg, '$.database_name') AS database,
    json_extract(msg, '$.message')     AS statement
FROM logs
WHERE json_valid(msg)
  AND json_extract(msg, '$.message') LIKE 'duration:%'
ORDER BY "timestamp" DESC
LIMIT 100;

Errors grouped by SQLSTATE

The state_code field holds the SQLSTATE. It names the failure category, so you never parse English error text:

SELECT
    json_extract(msg, '$.state_code') AS sqlstate,
    json_extract(msg, '$.message')    AS message,
    COUNT(*)                          AS occurrences
FROM logs
WHERE json_valid(msg)
  AND json_extract(msg, '$.error_severity') IN ('ERROR', 'FATAL', 'PANIC')
GROUP BY sqlstate, message
ORDER BY occurrences DESC
LIMIT 40;

A few SQLSTATEs worth knowing on sight:

Connection churn

SELECT
    json_extract(msg, '$.user_name') AS db_user,
    COUNT(*)                         AS connections
FROM logs
WHERE json_valid(msg)
  AND json_extract(msg, '$.message') LIKE 'connection authorized%'
GROUP BY db_user
ORDER BY connections DESC;

A high number means something opens a fresh connection per request. PostgreSQL connections are expensive. Connection poolers exist to fix exactly this.

Step 4: Alert on the important ones

Some database conditions should find you, not wait for you. Set them up as alert rules:

Deadlocks — always a bug, always worth knowing about:

SELECT COUNT(*) AS n
FROM logs
WHERE json_valid(msg)
  AND json_extract(msg, '$.state_code') = '40P01'
HAVING n > 0;

Connection limit exhaustion — the database is about to start refusing work:

SELECT COUNT(*) AS n
FROM logs
WHERE json_valid(msg)
  AND json_extract(msg, '$.state_code') = '53300'
HAVING n > 0;

Checkpoints happening too often. Your max_wal_size is too small, and you pay for it in I/O:

"checkpoints are occurring too frequently"

That last one is a full-text rule, not SQL. PostgreSQL emits it as a hint, and it is one of the rare log messages that tells you how to fix the problem.

Do not forget retention

On a busy server, log_min_duration_statement = 500 piles up fast. Set two things.

On the PostgreSQL side, cap local retention so the data volume does not fill:

log_rotation_age = 1d
log_rotation_size = 100MB
log_truncate_on_rotation = on

On the Central Logging side, set a retention window on the log source. Old rows then prune themselves. See Configuration.

Summary

Turn on jsonlog and log_min_duration_statement. Ship the files with an offset-tracking script. Query by SQLSTATE, never by error text.

Then alert on deadlocks and connection exhaustion. Those two failure modes are the ones most likely to wake you up.

Central Logging stores logs in SQLite and queries them with SQL. See Deploy to get an instance running.