Production logging configuration · PostgreSQL

Every other instrument in this part shows you the present. The log is the only one that shows you last Tuesday at 03:14.

The prefix

Everything starts here, because a log line without context cannot be joined to anything.

log_line_prefix = '%m [%p] %q%u@%d/%a '

Escape	Is	Why
`%m`	Timestamp with milliseconds	Correlating with anything else
`%p`	Process id	Joins to `pg_stat_activity.pid` and to the OS
`%q`	Stop here for background processes	Keeps background lines clean
`%u`	User	Who
`%d`	Database	Where
`%a`	Application name	Which service — if the app sets it

Read-only / Safewhat that prefix produces— EXECUTION VALIDATED on 18.6.

$ tail /tmp/sb.log

2026-08-27 23:13:19.892 UTC [637] postgres@postgres/psql LOG:  statement: CREATE ROLE demo_user ...

%p is the one that matters most. Lesson XVI-06 shows that pg_stat_activity.pid is the operating system pid, so a log line carrying %p can be joined to a running backend and to ps, top and /proc.

Add %h (client host) if you need to trace connections back to machines, and %x (transaction id) when investigating locking.

What to log

Always:

log_min_duration_statement = '1s'   # the slow query log
log_checkpoints = on                # on by default since 15
log_autovacuum_min_duration = '1s'  # vacuum that took real time
log_lock_waits = on                 # anything waiting past deadlock_timeout
log_temp_files = 0                  # every spill to disk; lesson XI-05
log_connections = on
log_disconnections = on

Each answers a question you will have during an incident:

log_min_duration_statement — which queries were slow?
log_lock_waits — what was blocking? Part IX’s whole subject.
log_temp_files — was work_mem too small? Lesson XI-05 measured spilling.
log_checkpoints — was a write stall a checkpoint? Lesson XII-04.
log_connections/log_disconnections — was it a connection storm?

Destination and rotation

logging_collector = on          # RESTART REQUIRED
log_destination = 'stderr'      # or 'jsonlog', or 'csvlog', or several
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = '1d'
log_rotation_size = '100MB'
log_truncate_on_rotation = off

logging_collector requires a restart, and it is what makes PostgreSQL manage its own files. Without it, output goes to whatever started the postmaster — journald under systemd, docker logs in a container — which is fine if that is deliberate and surprising if it is not.

jsonlog, added in PostgreSQL 15, emits one JSON object per event. If a collector consumes your logs, it is easier to parse correctly than stderr — no ambiguity about where a multi-line statement ends.

What the logging collector protects you from, and the price it charges

Without logging_collector, backends write to the stderr they inherited from the postmaster. That is simple and it has two problems.

Interleaving. Several processes writing to one file descriptor can interleave partial lines. PostgreSQL writes each message with a single write(), which the kernel makes atomic for pipes up to PIPE_BUF (4096 bytes on Linux), so short messages are safe. A message longer than that — a long statement, a plan from auto_explain — can be split, and another process’s output can land in the middle. Logs that occasionally contain spliced lines usually have this cause.

No rotation. Somebody else’s problem, and if that somebody is logrotate moving the file while PostgreSQL holds it open, you need a copytruncate or a reload signal, and getting that wrong loses whatever was written between the move and the reopen.

The logging collector is a dedicated process that all backends send messages to over a pipe, and it alone writes the file. It reassembles messages that arrived in chunks, so interleaving cannot happen, and it handles rotation itself.

The price is that it is a single process on the path of every log message. If it cannot keep up — because logging volume is enormous, or the log filesystem is slow — the pipe fills and backends block writing to it. Logging is not free and is not fire-and-forget: at log_statement = 'all' on a busy cluster, the collector can become the bottleneck, and the symptom is queries slowing down for no reason visible in any query metric.

That is the strongest practical argument for logging by threshold and sampling rather than logging everything, and it is a failure mode worth recognising because nothing in pg_stat_statements will show it.

What to take from this

log_line_prefix = '%m [%p] %q%u@%d/%a '. %p is what joins the log to pg_stat_activity and to the OS.
Check what your prefix actually is; distributions differ.
Log slow statements, checkpoints, slow autovacuum, lock waits, temp files, connections.
log_min_duration_statement is a threshold. Use sampling rather than lowering it globally.
logging_collector requires a restart and prevents interleaved lines.
The collector can become a bottleneck that backends block on. Do not log everything.

Cross-course references

Linux for Production Sysadmins — Part VIII (Logging and journald) covers rotation and the filesystem the logs land on, which is a disk-full cause in its own right.
Observability for Production Sysadmins — Part XXXI (Logging foundations) and Part XXXVI (Log shipping) cover getting these lines off the host, and Part XL (Log retention) covers how long to keep them.