Advanced20 min read

Administration

Configure, monitor, vacuum, and upgrade PostgreSQL clusters.

Configuration

postgresql.conf controls server behavior; pg_hba.conf controls authentication. ALTER SYSTEM writes settings to auto.conf. Some settings require reload (pg_reload_conf) or restart.

Tune shared_buffers ~25% RAM, effective_cache_size ~75% RAM as starting heuristics—validate with metrics.

ALTER SYSTEM SET max_connections = 200;
SELECT pg_reload_conf();

Monitoring

pg_stat_activity shows current queries. pg_stat_user_tables tracks seq scans and dead tuples. pg_stat_statements (extension) ranks query cost.

Integrate with Prometheus postgres_exporter, Datadog, or cloud monitoring. Alert on replication lag, disk usage, long transactions.

SELECT pid, state, query, now() - query_start AS duration
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

Vacuuming

UPDATE/DELETE leave dead tuples; VACUUM reclaims space for reuse. VACUUM FULL rewrites table exclusively—avoid in production hot paths. Autovacuum runs automatically tuned by cost thresholds.

Monitor wraparound risk on pg_database datfrozenxid. Long transactions block vacuum progress.

Autovacuum aggressive enough prevents bloat on busy tables
Manual VACUUM ANALYZE after large bulk changes updates stats
Consider pg_repack for online bloat reclamation

Upgrades

Minor versions via package upgrade with brief restart. Major upgrades use pg_upgrade, logical replication, or dump/restore. Test extensions compatibility before major jump.

pg_upgrade --link fast but keeps old cluster for rollback. Logical replication allows near-zero-downtime major upgrades.

pg_upgrade --old-datadir=/var/lib/postgresql/15/data \
  --new-datadir=/var/lib/postgresql/16/data --check

Routine Operations

REINDEX CONCURRENTLY fixes index bloat. Manage tablespaces and disk layout for large installations. Schedule maintenance windows for parameter changes requiring restart.

Document runbooks for connection storms, runaway queries (pg_cancel_backend, pg_terminate_backend), and disk full emergencies.