What is it? #
This lesson pulls the whole track together into something you can work through before putting a PostgreSQL database in front of real users — or against a database that is already live, to find the gaps.
The items are grouped by what they protect: access, data, availability and knowledge.
Not every item applies to every system. A small internal tool does not need synchronous replication. But every item should be a decision rather than an oversight, and the difference between those two is what this checklist is for.
If you do only three things from this entire track: do not let the application connect as a superuser, take backups you have actually restored, and alert on disk space and transaction id age. Those three prevent most serious PostgreSQL incidents.
Think of it like this #
The walk-round a pilot does before takeoff.
None of it is difficult, and an experienced pilot could describe every item from memory. The checklist exists anyway, because the failure mode is not ignorance — it is being busy, distracted, or assuming that something obvious was already handled by someone else.
Databases fail the same way. Almost nobody loses data because the concepts were too hard. They lose it because the backup had been quietly failing for six weeks and nobody had checked.
Simple example #
A database has been running fine for a year. Then one morning it stops accepting writes.
The disk is full. WAL archiving had been failing since a credentials change in March, so pg_wal had been growing for months. Nothing alerted, because nothing was watching.
Every item on this list exists because of an incident roughly like that one.
Code #
=====================================================================
1. AUTHENTICATION AND ACCESS
=====================================================================
[ ] The application does NOT connect as a superuser
[ ] The application does NOT connect as the table owner
(an owner bypasses row-level security and can drop tables)
[ ] Separate roles per purpose: app, read-only/analytics, backup, admin
[ ] Group roles used, so access changes are one GRANT or REVOKE
[ ] scram-sha-256 authentication (not md5, never trust)
[ ] pg_hba.conf reviewed: rules are ordered correctly, ends with a
catch-all reject
[ ] listen_addresses restricted — NOT 0.0.0.0 unless genuinely required
[ ] Firewall allows 5432 only from the application servers
[ ] SSL enabled; clients use sslmode=verify-full, not require
[ ] Passwords stored in a secrets manager or .pgpass (chmod 600),
never in a committed config file
[ ] ALTER DEFAULT PRIVILEGES set, so new tables inherit correct grants
[ ] Row-level security enabled where multi-tenant, with USING and WITH CHECK# Quick audit commands for section 1:
psql -c "\du" # roles and attributes
psql -c "SHOW listen_addresses;"
psql -c "SHOW ssl;"
psql -c "SELECT rolname FROM pg_roles WHERE rolsuper;" # who is superuser?
sudo ss -tulpn | grep 5432 # what is it listening on
sudo grep -v '^#' /etc/postgresql/16/main/pg_hba.conf | grep -v '^$'=====================================================================
2. BACKUPS (the section that matters most)
=====================================================================
[ ] Automated backups run on a schedule
[ ] Backup script uses set -euo pipefail and alerts on failure
[ ] Retention runs ONLY after the new backup is verified
[ ] pg_dumpall --globals-only included (roles and passwords)
[ ] Backups stored OFF this server
[ ] Offsite storage has versioning or object lock (ransomware protection)
[ ] Retention long enough to predate a slow-burning corruption
(e.g. daily x7, weekly x4, monthly x12)
[ ] A RESTORE HAS BEEN PERFORMED AND VERIFIED <- the critical one
[ ] Restore test is automated and runs monthly
[ ] Restore DURATION is measured and written down
[ ] A dead-man's-switch alerts if backups stop running entirely=====================================================================
3. WAL AND POINT-IN-TIME RECOVERY
=====================================================================
[ ] wal_level = replica (or logical if needed)
[ ] archive_mode = on, if PITR is required
[ ] archive_command returns 0 ONLY on genuine success
[ ] archive_timeout set, so quiet periods still archive (caps data loss)
[ ] pg_stat_archiver.failed_count is monitored and alerted
[ ] pg_wal filesystem size is monitored
[ ] No abandoned replication slots (they retain WAL until the disk fills)
[ ] max_slot_wal_keep_size set as a safety bound
[ ] A PITR HAS BEEN PRACTISED at least once, end to end
[ ] Base backup taken after any promotion or timeline change-- Quick audit for sections 2 and 3:
SELECT * FROM pg_stat_archiver;
SELECT slot_name, active,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained
FROM pg_replication_slots;
SELECT count(*), pg_size_pretty(sum(size)) FROM pg_ls_waldir();
SHOW wal_level; SHOW archive_mode; SHOW archive_timeout;=====================================================================
4. CONFIGURATION AND PERFORMANCE
=====================================================================
[ ] shared_buffers ~25% of RAM
[ ] effective_cache_size ~50-75% of RAM
[ ] work_mem conservative globally; raised per role/session for reports
[ ] maintenance_work_mem generous (VACUUM and index builds)
[ ] random_page_cost ~1.1 on SSD (default 4.0 assumes spinning disks)
[ ] max_connections reasonable; a connection pooler in front if needed
[ ] max_wal_size large enough that checkpoints_req stays low
[ ] Configuration in version control, with reasons recorded
[ ] pg_stat_statements enabled
[ ] Foreign key columns are indexed (PostgreSQL does not do this for you)
[ ] Unused indexes reviewed and removed=====================================================================
5. MAINTENANCE
=====================================================================
[ ] autovacuum is ON (never disable it)
[ ] autovacuum scale factors tuned on the largest, busiest tables
[ ] idle_in_transaction_session_timeout set
[ ] statement_timeout set (per role is often best)
[ ] lock_timeout set before running any DDL on a busy table
[ ] Dead tuple percentage monitored on large tables
[ ] age(datfrozenxid) monitored and ALERTED <- wraparound stops the database
[ ] ANALYZE runs after bulk loads and after every restore
[ ] Migrations use CREATE INDEX CONCURRENTLY=====================================================================
6. LOGGING AND MONITORING
=====================================================================
[ ] logging_collector on, with log rotation configured
[ ] log_min_duration_statement set (e.g. 1000ms)
[ ] log_checkpoints, log_lock_waits, log_temp_files, log_connections on
[ ] log_autovacuum_min_duration = 0
[ ] Logs shipped somewhere searchable, and rotated so they cannot fill the disk
PAGE-WORTHY ALERTS (keep this list short):
[ ] PostgreSQL not responding
[ ] Disk above 85% (data directory AND pg_wal)
[ ] age(datfrozenxid) above 1 billion
[ ] WAL archiving failing
[ ] Connections above 90% of max_connections
[ ] Backup job failed, or restore test failed
WARN-LEVEL:
[ ] Replication lag beyond tolerance
[ ] Queries blocked more than a minute
[ ] idle in transaction beyond 10 minutes
[ ] Cache hit ratio below 95% (OLTP)=====================================================================
7. AVAILABILITY
=====================================================================
[ ] RPO and RTO agreed with whoever owns the business risk
[ ] The current setup actually meets those numbers (measured, not assumed)
[ ] Standby configured if the RTO requires it
[ ] Replication lag monitored
[ ] If synchronous: at least TWO candidate standbys
('ANY 1 (s1, s2)') so losing one does not block all commits
[ ] Failover has been practised
[ ] Fencing plan exists for the old primary (to prevent split brain)
[ ] Everyone understands that the replica is NOT a backup=====================================================================
8. DOCUMENTATION AND RECOVERY PLAN
=====================================================================
The part that is always skipped, and always wanted at 3am.
[ ] Where backups live, and how to reach them
[ ] Step-by-step restore procedure, written as a checklist
[ ] Step-by-step PITR procedure, with the commands for YOUR version
[ ] Failover procedure
[ ] Connection details and where credentials are stored
[ ] PostgreSQL version, extensions installed, and why
[ ] What is NOT in the backup:
postgresql.conf, pg_hba.conf, OS-level extensions, cron jobs
[ ] Who to contact, and who has access
[ ] SOMEONE OTHER THAN THE AUTHOR has followed the restore procedure
successfully# =====================================================================
# A 10-MINUTE HEALTH CHECK you can run on any PostgreSQL server
# =====================================================================
psql -U postgres <<'SQL'
\echo '--- version ---'
SELECT version();
\echo '--- superusers (should be few, and NOT the app) ---'
SELECT rolname FROM pg_roles WHERE rolsuper;
\echo '--- database sizes ---'
SELECT datname, pg_size_pretty(pg_database_size(datname)) FROM pg_database
ORDER BY pg_database_size(datname) DESC;
\echo '--- connections vs limit ---'
SELECT count(*) AS current,
(SELECT setting FROM pg_settings WHERE name='max_connections') AS max
FROM pg_stat_activity;
\echo '--- wraparound risk (alert above 1 billion) ---'
SELECT datname, age(datfrozenxid) FROM pg_database
ORDER BY age(datfrozenxid) DESC LIMIT 5;
\echo '--- WAL archiving health ---'
SELECT archived_count, failed_count, last_archived_time, last_failed_time
FROM pg_stat_archiver;
\echo '--- replication slots (inactive ones retain WAL forever) ---'
SELECT slot_name, active FROM pg_replication_slots;
\echo '--- worst bloat ---'
SELECT relname, n_dead_tup,
round(100.0*n_dead_tup/NULLIF(n_live_tup+n_dead_tup,0),1) AS dead_pct
FROM pg_stat_user_tables WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC LIMIT 5;
\echo '--- longest running queries ---'
SELECT now()-query_start AS duration, state, left(query,60)
FROM pg_stat_activity WHERE state <> 'idle'
ORDER BY query_start LIMIT 5;
SQL
# And on the operating system:
df -h # disk, including the pg_wal filesystem
free -h # memory
uptime # loadHow it works #
The checklist is ordered roughly by how badly each section hurts when it is wrong.
Access control comes first because it is the only section where a mistake can lose everything at once. An application connecting as a superuser turns an SQL injection bug into total compromise; connecting as the table owner turns it into dropped tables. Separating roles costs an hour and changes the worst case fundamentally.
Backups rank second because they are the last line of defence for every other failure. The single most important line in the whole list is that a restore has actually been performed — because untested backups fail at exactly the moment they are needed, and by then there is no second option.
WAL and PITR determine whether recovery means "last night" or "one minute ago". The items about monitoring archiving exist because a broken archive_command is silent, fills the disk over weeks, and eventually stops the database — the example at the top of this lesson.
Configuration affects performance rather than survival, which is why it sits in the middle. The exception is max_connections and memory settings interacting badly, which can exhaust RAM.
Maintenance is where the two genuinely database-stopping problems live: transaction id wraparound and unbounded bloat. Both are entirely preventable with monitoring, and both are invisible until they are severe.
Monitoring is how every other section gets verified over time. A correct configuration that drifts, a backup that stops, archiving that breaks — all of these are only caught by watching.
Documentation is last on the list and first in importance during an incident. Recovery is performed under pressure, often by someone who did not build the system. The requirement that somebody other than the author has followed the restore procedure successfully is the one that turns a document into a usable procedure.
Real-world use #
Work through this against a real system and expect to find gaps. Most production databases fail several items, and finding them on a quiet afternoon is considerably better than finding them during an incident.
Prioritise by blast radius. Fix superuser access and untested backups before tuning work_mem. A perfectly tuned database with no working restore is one bad afternoon away from being gone.
Treat every unchecked item as a decision to record, not simply a gap. "No standby, because two hours of downtime is acceptable and agreed" is a legitimate position. "No standby" with nobody having thought about it is not — and the difference only becomes visible when the server dies.
Re-run the checklist periodically. Systems drift: a new table misses its grants, a credentials rotation breaks archiving, data growth outpaces the retention policy. Quarterly is reasonable, and it takes far less time the second time.
The ten-minute health check is worth keeping. Run it on any PostgreSQL server you inherit, and you will know more about it in ten minutes than most documentation would tell you.
Finally, the three things that matter most, restated because they genuinely prevent the majority of serious incidents: the application must not be a superuser, your backups must have been restored at least once, and something must alert you about disk space and transaction id age. Everything else on this list improves a system. Those three keep it alive.
Common mistakes #
- Treating unchecked items as acceptable gaps rather than decisions that were never actually made.
- Tuning performance settings while backups remain untested.
- Running the checklist once at launch and never again as the system drifts.
- Documenting the recovery plan but never having anyone else follow it successfully.
- Monitoring the database while leaving backups, archiving and restore tests unmonitored.
Practice #
Run the ten-minute health check against a database you have built during this track, and work through all eight sections honestly. Write down every item you cannot tick, and mark each one as either "will fix" or "accepted, because...". Then fix the three highest-impact gaps. If you have access to a real production system and permission to audit it, do the same there — the read-only queries in the health check are safe to run.