Logging Basics

Q: Include query id in logs?

PostgreSQL 13+ can use %q in prefix for query id when track_activity_query_size allows.

Q: Autovacuum log noise?

log_autovacuum_min_duration = 0 during bloat incidents; raise to 1s when stable.

PostgreSQL logs are your flight recorder: connections, errors, slow queries, and checkpoints. Configure log_line_prefix, selective log_min_duration_statement, and rotation discipline before log volume overwhelms disk or SIEM budget. PostgreSQL 18.4 defaults are minimal; production needs explicit policy.

Recipe

# postgresql.conf production starter
log_destination = 'csvlog'
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 1d
log_rotation_size = 100MB
 
log_line_prefix = '%m [%p] %u@%d %a %h %i '
log_connections = on
log_disconnections = on
log_statement = 'ddl'
log_min_duration_statement = 1000   # 1s slow query threshold (ms)

When to reach for this:

New cluster provisioning before traffic
Incident forensics when you cannot trace who ran DDL
Tuning log volume after disk or cost alert

Working Example

Structured prefix, DDL capture, and slow query logging without log_statement=all.

# postgresql.conf
log_destination = 'csvlog'
logging_collector = on
log_line_prefix = '%t [%p]: user=%u,db=%d,app=%a,client=%h,session=%i '
log_connections = on
log_disconnections = on
log_statement = 'ddl'
log_min_duration_statement = 500
log_checkpoints = on
log_autovacuum_min_duration = 0
log_lock_waits = on

SELECT pg_reload_conf();
 
-- Verify settings
SELECT name, setting, unit
FROM pg_settings
WHERE name LIKE 'log%'
ORDER BY name;

# Sample csvlog line fields (grep errors)
grep "ERROR" /var/lib/postgresql/18/main/log/postgresql-*.log | tail -5

What this demonstrates:

csvlog eases SIEM parsing vs unstructured stderr
Prefix carries user, db, app, client IP, session id for correlation
DDL always logged; DML only when slower than 500ms
log_lock_waits surfaces blocker context in logs

Deep Dive

log_line_prefix Tokens

Token	Meaning
`%m`	Timestamp with ms
`%p`	PID
`%u`	User
`%d`	Database
`%a`	application_name
`%h`	Remote host
`%i`	Session id

Set application_name in app DSN for service attribution.

Volume Control Ladder

log_statement = none (default DML off)
log_min_duration_statement for slow queries only
log_statement = ddl for schema changes
log_statement = mod only in short debug windows
Never log_statement = all in prod without sampling plan

Rotation

# logging_collector manages rotation when size/age hit
# Ship off-host daily; keep 7-30 days local per policy

Gotchas

log_statement=all on OLTP - fills disk in hours. Fix: duration threshold + pg_stat_statements for ranking.
Missing application_name - every line shows app= empty. Fix: set in pool startup query and ORM DSN.
Logs only on primary disk - same volume as PGDATA risks full disk. Fix: ship to central store; separate volume if local retention required.
Logging passwords in errors - failed queries may echo literals. Fix: parameterized queries; redact in log pipeline.
No timezone in timestamps - correlation pain. Fix: log_timezone = 'UTC' everywhere.
Reload vs restart - most log GUCs reload; log_destination change may need restart. Fix: change in maintenance window.

Alternatives

Alternative	Use When	Don't Use When
stderr + journald	Simple VM, systemd	CSV field parsing at scale
jsonlog (PG15+)	JSON-native pipelines	csvlog already standardized in org
External APM only	Tiny dev	Compliance needs server audit trail
pgaudit	Regulated table access	General slow query capture

FAQs

csvlog vs stderr?

csvlog gives quoted CSV fields for parsers. stderr is fine for single-node dev.

Ideal slow query threshold?

500-1000ms for OLTP APIs; lower temporarily during tuning sprints.

Log replication statements?

Usually no at statement level. Use replication monitoring metrics instead.

Managed Postgres log access?

RDS CloudWatch, Cloud SQL logging, Azure diagnostic settings - still tune parameters.

Include query id in logs?

PostgreSQL 13+ can use %q in prefix for query id when track_activity_query_size allows.

Who can read logs?

OS postgres user locally; SIEM access RBAC in central store. Not all engineers.

log_duration vs min_duration_statement?

min_duration logs full statement when threshold exceeded. Prefer min_duration for forensics.

Autovacuum log noise?

log_autovacuum_min_duration = 0 during bloat incidents; raise to 1s when stable.

PII in logs?

Scrub in ingest pipeline; avoid logging full row payloads from triggers.

Disk full from logs?

Emergency: increase rotation frequency, ship and truncate, never delete WAL. See disk full runbook.

csvlog & JSON Logs - centralized shipping
pgaudit & Compliance Logging - audit scope
Forensic Queries - investigate access
pg_stat_statements - workload ranking
Logging Best Practices - volume checklist

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.

Recipe

# postgresql.conf production starter
log_destination = 'csvlog'
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 1d
log_rotation_size = 100MB
 
log_line_prefix = '%m [%p] %u@%d %a %h %i '
log_connections = on
log_disconnections = on
log_statement = 'ddl'
log_min_duration_statement = 1000   # 1s slow query threshold (ms)

When to reach for this:

New cluster provisioning before traffic
Incident forensics when you cannot trace who ran DDL
Tuning log volume after disk or cost alert

Working Example

Structured prefix, DDL capture, and slow query logging without log_statement=all.

# postgresql.conf
log_destination = 'csvlog'
logging_collector = on
log_line_prefix = '%t [%p]: user=%u,db=%d,app=%a,client=%h,session=%i '
log_connections = on
log_disconnections = on
log_statement = 'ddl'
log_min_duration_statement = 500
log_checkpoints = on
log_autovacuum_min_duration = 0
log_lock_waits = on

SELECT pg_reload_conf();
 
-- Verify settings
SELECT name, setting, unit
FROM pg_settings
WHERE name LIKE 'log%'
ORDER BY name;

# Sample csvlog line fields (grep errors)
grep "ERROR" /var/lib/postgresql/18/main/log/postgresql-*.log | tail -5

What this demonstrates:

csvlog eases SIEM parsing vs unstructured stderr
Prefix carries user, db, app, client IP, session id for correlation
DDL always logged; DML only when slower than 500ms
log_lock_waits surfaces blocker context in logs

Deep Dive

log_line_prefix Tokens

Token	Meaning
`%m`	Timestamp with ms
`%p`	PID
`%u`	User
`%d`	Database
`%a`	application_name
`%h`	Remote host
`%i`	Session id

Set application_name in app DSN for service attribution.

Volume Control Ladder

log_statement = none (default DML off)
log_min_duration_statement for slow queries only
log_statement = ddl for schema changes
log_statement = mod only in short debug windows
Never log_statement = all in prod without sampling plan

Rotation

# logging_collector manages rotation when size/age hit
# Ship off-host daily; keep 7-30 days local per policy

Gotchas

log_statement=all on OLTP - fills disk in hours. Fix: duration threshold + pg_stat_statements for ranking.
Missing application_name - every line shows app= empty. Fix: set in pool startup query and ORM DSN.
Logs only on primary disk - same volume as PGDATA risks full disk. Fix: ship to central store; separate volume if local retention required.
Logging passwords in errors - failed queries may echo literals. Fix: parameterized queries; redact in log pipeline.
No timezone in timestamps - correlation pain. Fix: log_timezone = 'UTC' everywhere.
Reload vs restart - most log GUCs reload; log_destination change may need restart. Fix: change in maintenance window.

Alternatives

Alternative	Use When	Don't Use When
stderr + journald	Simple VM, systemd	CSV field parsing at scale
jsonlog (PG15+)	JSON-native pipelines	csvlog already standardized in org
External APM only	Tiny dev	Compliance needs server audit trail
pgaudit	Regulated table access	General slow query capture

FAQs

csvlog vs stderr?

csvlog gives quoted CSV fields for parsers. stderr is fine for single-node dev.

Ideal slow query threshold?

500-1000ms for OLTP APIs; lower temporarily during tuning sprints.

Log replication statements?

Usually no at statement level. Use replication monitoring metrics instead.

Managed Postgres log access?

RDS CloudWatch, Cloud SQL logging, Azure diagnostic settings - still tune parameters.

Include query id in logs?

PostgreSQL 13+ can use %q in prefix for query id when track_activity_query_size allows.

Who can read logs?

OS postgres user locally; SIEM access RBAC in central store. Not all engineers.

log_duration vs min_duration_statement?

min_duration logs full statement when threshold exceeded. Prefer min_duration for forensics.

Autovacuum log noise?

log_autovacuum_min_duration = 0 during bloat incidents; raise to 1s when stable.

PII in logs?

Scrub in ingest pipeline; avoid logging full row payloads from triggers.

Disk full from logs?

Emergency: increase rotation frequency, ship and truncate, never delete WAL. See disk full runbook.

csvlog & JSON Logs - centralized shipping
pgaudit & Compliance Logging - audit scope
Forensic Queries - investigate access
pg_stat_statements - workload ranking
Logging Best Practices - volume checklist

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.

Logging Basics

Search Documentation

Logging Basics