postgres-operations

name	postgres-operations
description	PostgreSQL operations for backups, restores, PITR, pg_dump, pg_restore, pg_basebackup, pgBackRest, WAL archiving, vacuum, autovacuum, bloat, transaction ID wraparound, maintenance jobs, upgrades, and capacity management.

PostgreSQL Operations

Focus on recoverability, maintenance safety, and capacity. Always distinguish logical backups from physical backups and require restore validation for production.

How To Approach An Operations Request

1. Establish RPO/RTO expectations before recommending a backup method. The method follows the objective, not the other way around.
2. Identify what already exists: backup tool, WAL archiving state, retention, last successful restore test.
3. For vacuum/bloat symptoms, find what is blocking cleanup before tuning thresholds: long transactions, abandoned replication slots, prepared transactions, or hot standby feedback.
4. For wraparound risk, check age(datfrozenxid) first; treat autovacuum_freeze_max_age approach as urgent and 2-billion approach as an emergency.
5. For upgrades, confirm major vs minor, extension compatibility, and rollback plan before choosing a method.
6. Prefer read-only evidence (scripts/) before recommending intrusive commands like VACUUM FULL or REINDEX.

Backup Decision Guide

Requirement	Use	Why
Point-in-time recovery, low RPO	Physical base backup + continuous WAL archiving (pgBackRest, Barman, pg_basebackup + archive)	Only WAL replay gives arbitrary-point recovery
Portability across versions/platforms, selective objects	Logical (pg_dump/pg_restore, custom or directory format)	Logical dumps restore into different major versions and subsets
Whole-cluster roles and tablespaces in logical form	pg_dumpall --globals-only plus per-database pg_dump	pg_dump never captures roles/tablespaces
Very large databases, fast delta backups	pgBackRest incremental/differential, or PG 17+ pg_basebackup --incremental	Full copies stop scaling
Managed cloud	Provider snapshots + PITR window, plus logical exports for portability	Filesystem access is unavailable

A backup that has never been restored is a hope, not a backup. Test restores on a schedule and time them against RTO.

Vacuum And Bloat Triage

Dead tuples that vacuum cannot remove are almost always held by an xmin horizon: the oldest thing still able to see old row versions. Check in order:

1. Long-running or idle in transaction sessions (backend_xmin in pg_stat_activity).
2. Abandoned or lagging replication slots (xmin/catalog_xmin in pg_replication_slots).
3. Prepared transactions (pg_prepared_xacts).
4. Standbys with hot_standby_feedback = on.

Only after the horizon is clean does autovacuum tuning matter: lower per-table autovacuum_vacuum_scale_factor for large tables, raise autovacuum_vacuum_cost_limit / lower cost delay for throughput, and add workers only when all workers are consistently busy.

Common Pitfalls

1. Recommending VACUUM FULL for routine bloat; it takes an exclusive lock and rewrites the table. Prefer fixing the cause, then pg_repack if a rewrite is truly needed.
2. Treating pg_dump as a PITR strategy. It is a point-in-time snapshot of one database only.
3. Forgetting --globals-only roles when restoring a logical dump to a new instance.
4. Copying PGDATA while the server runs without pg_backup_start/backup tooling coordination.
5. Raising autovacuum_freeze_max_age to silence wraparound autovacuums instead of letting them finish.
6. Running pg_upgrade without --check first, or without analyzing (vacuumdb --analyze-in-stages) after; optimizer statistics do not carry over (before PG 18).
7. Scheduling manual VACUUM jobs that overlap and starve autovacuum workers.

References

• references/backup-maintenance.md - backup method matrix, PITR workflow, vacuum/bloat remediation, wraparound response, and upgrade paths.
• ../postgres/references/best-practices.md - imported domain-expert backup, vacuum, bloat, and configuration material.
• ../postgres/references/architecture.md - MVCC, WAL, checkpoint, heap, FSM/VM, and XID internals.

Scripts

• scripts/01-backup-readiness.sql - archiving and WAL settings.
• scripts/02-vacuum-health.sql - autovacuum and transaction ID risk.
• scripts/03-table-bloat-signals.sql - dead tuple and table-size signals.
• scripts/04-xid-wraparound-risk.sql - database and table freeze-age risk.
• scripts/05-autovacuum-configuration.sql - global and per-table autovacuum settings.
• scripts/06-relation-capacity.sql - largest relations, tablespaces, and database sizes.
• scripts/07-maintenance-progress.sql - active vacuum, analyze, cluster, create-index, and base-backup progress.
• scripts/08-invalid-unlogged-toast.sql - invalid indexes, unlogged tables, TOAST, and persistence risks.

Cross-Skill Routing

• Live symptom triage, waits, and blocking go to postgres-monitoring.
• WAL archiving for replication and failover posture goes to postgres-ha-replication.
• Checkpoint, WAL sizing, and storage configuration go to postgres-infrastructure.
• Provider snapshot/PITR behavior and managed limits go to postgres-cloud.
• Index design changes discovered during bloat work go to postgres-engineering.