Essential Tools Best Practices
A condensed summary of the 25 most important essential-tool practices for PostgreSQL 18.4 operations teams - backup verification, log analysis, bloat control, and skeptical use of automated advisors.
//
Search across all documentation pages
A condensed summary of the 25 most important essential-tool practices for PostgreSQL 18.4 operations teams - backup verification, log analysis, bloat control, and skeptical use of automated advisors.
Pin tool versions in tools/versions.env: pgBackRest, pgBadger, pg_repack, and psql client aligned to server 18.4 (Tools Basics).
One physical backup system per cluster: pgBackRest OR Barman OR managed snapshots - never two agents archiving the same data directory.
Automate pgbackrest check daily: Alert on non-zero exit; silent archive failures fill disks.
Weekly full backup plus daily diff: Document retention in DR runbook; match RPO/RTO commitments.
Quarterly restore drill to sandbox: Measure minutes-to-readiness; attach evidence to governance ticket.
Verify restore with app sanity queries: Row counts and max(created_at) on critical tables, not only pg_ctl start.
Enable log_min_duration_statement with threshold: 200-500ms OLTP starting point; avoid logging all statements.
Use csvlog for pgbadger pipelines: Consistent parsing; ship reports to durable object storage.
Run pgbadger incremental nightly: Avoid reprocessing full log history every run.
Pair pgbadger with pg_stat_statements: Live aggregates plus historical log trends.
Top 10 slow queries reviewed weekly: Each ticket gets EXPLAIN (ANALYZE, BUFFERS) evidence.
pg_repack requires extension allowlist ADR: CREATE EXTENSION pg_repack is governed, not casual.
Repack in change windows with replication lag monitor: Large repacks generate WAL; watch replicas.
Fix autovacuum after repack: Lower autovacuum_vacuum_scale_factor on large tables or bloat returns.
Index advisor output is input to human review: pganalyze/OtterTune never auto-merge DDL (pganalyze / OtterTune).
CREATE INDEX CONCURRENTLY for production index adds: Advisor-suggested indexes follow migration safety scoring.
Drop unused indexes advisor flagged as churn: Write amplification savings matter as much as adds.
Knob changes one at a time in staging load test: OtterTune suggestions validated with benchmarks, not faith.
Bastion hosts hold backup credentials: Not on application pods; least-privilege IAM.
CI runs migration lint, not prod pg_repack: Heavy maintenance stays out of deploy pipelines.
Document managed vs self-hosted tool matrix: RDS teams skip pgBackRest install docs; use native backup APIs.
Redact logs before external report sharing: Query text may contain PII.
Tool failures become runbook updates: Each missed alert becomes a checklist bullet.
New hire tooling day-one: psql, EXPLAIN habit, read-only prod; backup tools week two with buddy.
Retire tools with migration plan: Switching backup agent requires overlap verification window, not weekend cutover alone.
AWS automated backups + pg_stat_statements + PR EXPLAIN review. Add pgbadger when log export is configured. pgBackRest applies when moving self-hosted.
pgbackrest check and verify reduce risk but do not replace quarterly restore drill. Managed providers: test snapshot restore to new instance.
On-call DBA or platform rotation publishes weekly link; app teams own query fixes. Escalate recurring offenders to architecture review.
Stack versions: This page was written for PostgreSQL 18.4, pgBackRest 2.55+, pgBadger 12+, and pg_repack 1.5+.
Reviewed by Chris St. John·Last updated Jul 16, 2026