Production operations

Production stack

PgBouncer, pgBackRest, Patroni, CloudNativePG, and phased adoption.

Monitoring and logs

pg_stat_statements, JSON logs, Prometheus, Grafana, and pgBadger.

Safe migrations

Expand-and-contract, DDL locks, CI, RLS tests, and zero-downtime boundaries.

Autovacuum and bloat

Dead tuples, freeze risk, vacuum progress, and hot-table settings.

Backup and recovery

Logical and physical backup, PITR, restore drills, and evidence.

Security baseline

Roles, authentication, TLS, search_path, RLS, and audit boundaries.

Replication and upgrades

Streaming and logical replication, major upgrades, and cutover checks.

Define objectives first

Objective	Question
RPO	How much data can be lost?
RTO	How quickly must service return?
Capacity	What are peak connections and data/WAL/backup growth?
Availability	Which failures auto-fail over, and which require judgment?
Security	Who can connect, read which data, and perform which changes?

“High availability” and “we have backups” are not verifiable without targets.

Minimal daily view

SELECT now(), version();

SELECT state, count(*)
FROM pg_stat_activity
GROUP BY state
ORDER BY state;

SELECT datname, age(datfrozenxid)
FROM pg_database
ORDER BY age(datfrozenxid) DESC;

SELECT
  num_timed,
  num_requested,
  num_done,
  buffers_written,
  write_time,
  sync_time
FROM pg_stat_checkpointer;

SELECT buffers_clean, maxwritten_clean, buffers_alloc
FROM pg_stat_bgwriter;

Since PostgreSQL 17, checkpoint statistics live in pg_stat_checkpointer; background-writer statistics remain in pg_stat_bgwriter. See the PostgreSQL 18 cumulative statistics views for the field definitions.

Also monitor disk, WAL generation/archive, replication lag, backup state, transaction age, lock waits, query latency, autovacuum, and pool saturation. Thresholds come from this system's baseline.

Change discipline

Measure lock and duration on representative data.
Document rollback and the point of irreversibility.
Set lock_timeout so a migration does not wait indefinitely then acquire a disruptive lock.
Observe locks, WAL, replication lag, and errors during execution.
Verify with queries and business signals.

Production DDL is not done when the command succeeds. It is an observable, interruptible, verified release.