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.
-
Pin tool versions in
tools/versions.env: pgBackRest, pgBadger, pg_repack, and psql client aligned to server18.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 checkdaily: 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 onlypg_ctl start. -
Enable
log_min_duration_statementwith threshold: 200-500ms OLTP starting point; avoid logging all statements. -
Use
csvlogfor 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_repackis 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_factoron large tables or bloat returns. -
Index advisor output is input to human review: pganalyze/OtterTune never auto-merge DDL (pganalyze / OtterTune).
-
CREATE INDEX CONCURRENTLYfor 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.
FAQs
Minimum toolset for a startup on RDS?
AWS automated backups + pg_stat_statements + PR EXPLAIN review. Add pgbadger when log export is configured. pgBackRest applies when moving self-hosted.
How prove backup works without restore?
pgbackrest check and verify reduce risk but do not replace quarterly restore drill. Managed providers: test snapshot restore to new instance.
Who owns pgbadger reports?
On-call DBA or platform rotation publishes weekly link; app teams own query fixes. Escalate recurring offenders to architecture review.
Related
- Tools Basics - toolkit layout
- pgBackRest - backup configuration
- pgbadger - slow query reports
- pg_repack - bloat reclamation
- PITR Drill Runbook - restore exercises
Stack versions: This page was written for PostgreSQL 18.4, pgBackRest 2.55+, pgBadger 12+, and pg_repack 1.5+.