PostgreSQL / Backup & Recovery / Runbooks

Teknik Uygulama RehbericriticalHazırlık 1 saat + tatbikat 1–4 saat (veri boyutuna bağlı)

postgresql (14–18)

Verify Your PostgreSQL Backup: First Restore Drill

A green backup job only means files were written. The only proof of recoverability is an isolated restore drill verifying data integrity. This runbook walks through your first drill end-to-end to deliver measured RTO, measured RPO, and a signed drill log.

Prerequisites & Safety Rules:

Isolated drill host/container (no access to prod network)Matching PostgreSQL major version and extensionsRead-only access to backup storageAt least 2x backup size in available disk spaceProduction reference metrics and cutoff row counts for verification

Summary: A backup job finishing with "success" only proves a file was written to disk. The only conclusive proof of recoverability is successfully restoring from that backup into an isolated sandbox and validating data integrity. This runbook guides you end-to-end through your first drill, concluding with measured RTO, measured RPO, and a signed drill audit log.

Who is this runbook for?

•Teams that back up regularly but have never performed an isolated recovery drill
•PostgreSQL 14–18 production environments utilizing pg_dump, pg_basebackup, pgBackRest, Barman, or WAL-G
•DBAs, SREs, and Platform Engineers required to provide verifiable evidence when auditors ask *"Are your backups working?"*

In cloud-managed services (AWS RDS, Aurora, GCP Cloud SQL, Azure Database for PostgreSQL), restore always creates a new instance. From Step 4 onwards, verification and drill record steps apply identically.

Drill Workflow

Mimari Akış & Topoloji Şeması (Mermaid)

Mermaid şeması çiziliyor...

Step 0 — Safety Rules (Do Not Skip)

The drill must never touch the production server, production data directory, or production WAL archive.

Rule	Rationale
Drill is hosted on a separate host, VM, or container	Accidental `-h` or `-D` flag errors could overwrite production
Set `archive_mode = off` on drill instance	Otherwise the restored instance might write a new timeline to production's WAL archive
Set `max_logical_replication_workers = 0` on physical restore	Restored subscriptions might connect to production publishers and mutate data
Disable cron jobs (`pg_cron`) and external outbound integrations	Drill instance must not send emails to customers or push messages to event queues
Sandbox adheres to production data security controls	Restored data is real personal data (GDPR/KVKK). Must be shredded after drill

Step 1 — Define Scope and Success Criteria

Before initiating the drill, document the following parameters. The exercise is evaluated strictly against these criteria:

Parameter	Example Value
Source database	`orders-prod` (PostgreSQL 16.x)
Target backup	Yesterday's nightly full backup (realistic scenario, not just the latest minute)
Scenario	"Accidental bulk DELETE at 14:30 yesterday; recover database to 14:29"
Target RTO	2 hours
Target RPO	5 minutes
Success criteria	Instance starts clean, row counts match baseline for top 10 tables, app smoke tests pass
Responsible roles	Operator + Independent Observer (two people minimum)

Tip: In your first drill, select a backup from a few days ago rather than the latest one. In real emergencies, the latest backup might be corrupt. Testing an older snapshot proves your retention chain actually works backward.

Step 2 — Collect Reference Baseline from Production

Verification cannot occur without a reference baseline. Near backup time (or from a read replica), execute and save the following metrics:

sql

-- Version and installed extensions
SELECT version();
SELECT extname, extversion FROM pg_extension ORDER BY extname;

-- Object counts per schema
SELECT n.nspname AS schema, c.relkind, count(*)
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
  AND n.nspname NOT LIKE 'pg_toast%'
GROUP BY 1, 2
ORDER BY 1, 2;

-- Database physical footprint
SELECT pg_size_pretty(pg_database_size(current_database()));

For critical business tables, record row counts prior to an immutable cutoff timestamp:

sql

SELECT count(*) AS orders_before_cutoff
FROM orders
WHERE created_at < '2026-10-13 00:00:00+03';

For small reference tables (e.g., currencies, tax_rates), calculate an MD5 content hash:

sql

SELECT md5(string_agg(t::text, '|' ORDER BY t.id)) AS content_hash
FROM tax_rates t;

Step 3 — Prepare Isolated Sandbox Environment

•Same major PostgreSQL version as production
•Exact same extension versions compiled/installed
•At least 2x backup footprint in free disk space (accounting for WAL replay buffer)
•Read-only access to the backup storage bucket/mount

Provision a quick containerized sandbox:

bash

docker run -d --name pg-restore-drill   -e POSTGRES_PASSWORD='<strong-password>'   -p 127.0.0.1:55432:5432   -v /drill/data:/var/lib/postgresql/data   postgres:16

Important: Port is bound strictly to 127.0.0.1. Never expose drill sandboxes to public or broad internal networks.

Record timestamp. RTO clock starts here:

bash

date -Iseconds | tee /drill/rto_start.txt

Step 4A — Logical Backup (pg_dump) Restore

▸4A.1 Verify Archive Header

bash

pg_restore --list /backups/orders-prod_2026-10-13.dump > /drill/toc.txt
wc -l /drill/toc.txt

This reads the table of contents (TOC). Passing without errors verifies header readability, but does not prove restore capability.

▸4A.2 Restore Global Objects (Roles and Tablespaces)

pg_dump does not include global roles. Without roles, ownership assignments and GRANT permissions fail:

bash

psql -h 127.0.0.1 -p 55432 -U postgres -f /backups/globals_2026-10-13.sql

Common finding in first drills: Teams discover global roles were never included in automated backup jobs. Finding this in a drill prevents a catastrophic outage later.

▸4A.3 Create Database and Restore Data

bash

createdb -h 127.0.0.1 -p 55432 -U postgres orders_drill

pg_restore -h 127.0.0.1 -p 55432 -U postgres   --dbname=orders_drill   --jobs=4   --exit-on-error   --verbose   /backups/orders-prod_2026-10-13.dump 2> /drill/restore.log

Update planner statistics following restore:

bash

psql -h 127.0.0.1 -p 55432 -U postgres -d orders_drill -c "ANALYZE;"

Step 4B — Physical Backup + PITR Restore

▸4B.1 Verify Backup Integrity with pg_verifybackup

bash

pg_verifybackup /backups/base_2026-10-13

Or with pgBackRest:

bash

pgbackrest --stanza=main verify

▸4B.2 Restore to Drill Data Directory

With pg_basebackup:

bash

sudo -u postgres cp -a /backups/base_2026-10-13/. /drill/pgdata/
sudo chmod 700 /drill/pgdata

With pgBackRest:

bash

pgbackrest --stanza=main --type=time --target="2026-10-13 14:29:00+03" --target-action=promote restore

▸4B.3 Configure Recovery Parameters

In /drill/pgdata/postgresql.auto.conf:

ini

restore_command = 'cp /backups/wal_archive/%f %p'
recovery_target_time = '2026-10-13 14:29:00+03'
recovery_target_action = 'promote'

# Sandbox Safety Overrides
archive_mode = off
max_logical_replication_workers = 0
port = 55433

Trigger recovery signal and start PostgreSQL:

bash

sudo -u postgres touch /drill/pgdata/recovery.signal
sudo -u postgres pg_ctl -D /drill/pgdata -l /drill/recovery.log start

▸4B.4 Verify Recovery Completion

sql

SELECT pg_is_in_recovery();          -- Must return false (promoted)
SELECT pg_last_wal_replay_lsn();     -- Final replayed LSN

Step 5 — Data Verification

Instance startup alone does not constitute success. Run validation against the Step 2 baseline:

sql

-- 1. Compare object counts
SELECT n.nspname AS schema, c.relkind, count(*)
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
  AND n.nspname NOT LIKE 'pg_toast%'
GROUP BY 1, 2 ORDER BY 1, 2;

-- 2. Verify extensions
SELECT extname, extversion FROM pg_extension ORDER BY extname;

-- 3. Verify cutoff row counts
SELECT count(*) FROM orders WHERE created_at < '2026-10-13 00:00:00+03';

-- 4. Check for invalid indexes
SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;

Run index and block integrity check via amcheck:

bash

psql -p 55433 -d orders_drill -c "CREATE EXTENSION IF NOT EXISTS amcheck;"
pg_amcheck -p 55433 -d orders_drill --heapallindexed --jobs=4

Test from application perspective by executing your critical query (e.g. customer dashboard fetch).

Step 6 — Measure RTO and RPO

bash

date -Iseconds | tee /drill/rto_end.txt

•Measured RTO: rto_end - rto_start (end-to-end, including sandbox staging)
•Measured RPO: Difference between target timestamp and newest restored record:

sql

SELECT max(created_at) AS last_tx_in_restore FROM orders;

Step 7 — Sign Drill Record and Action Plan

Document the drill in your team repository:

markdown

## Restore Drill Record — <Date>
- Target: orders-prod (PostgreSQL 16.x)
- Scenario: Point-in-Time Recovery to 2026-10-13 14:29
- Operator / Observer: <Name> / <Name>

| Metric | Target | Measured | Result |
|---|---|---|---|
| RTO | 2 hrs | 2 hrs 47 min | Failed SLA |
| RPO | 5 min | 40 sec | Passed |
| Schema Counts | Matches Baseline | Matched | Passed |
| amcheck | Clean | Clean | Passed |

### Findings and Corrective Actions
1. Global roles missing in backup job -> Added pg_dumpall --globals-only (Owner, Due Date)
2. Storage IAM delay cost 50 minutes -> Dedicated read-only restore IAM role provisioned

Step 8 — Secure Sandbox Teardown

bash

sudo -u postgres pg_ctl -D /drill/pgdata stop
docker rm -f pg-restore-drill
sudo rm -rf /drill/pgdata /drill/data

Related Next Steps and Architecture Guides

•Select the optimal backup toolchain for your throughput: How to Choose Your PostgreSQL Backup Approach (dataxtools.com)
•Standardize recovery readiness across your team: From Backup Success to Recovery Readiness: The Restore Drill (dataopsphere.com)

Community Benchmark & Contribution

Share Your Restore Drill Results or Experience

Contribute real-world RTO/RPO telemetry, PostgreSQL version, backup methodology, and obstacles encountered from your infrastructure to help build industry benchmarks.

3-SITE TRIAD SERIES0% Overlap, Cross-Disciplinary

Complementary Ecosystem Content: PostgreSQL Backup & Recovery: Tool Selection, Restore Drill & Readiness Framework

Complementary dataiotech publications covering this problem from different angles (diagnostics, tooling, escalation):

dataxtools.com · Decision Matrix

How to Choose Your PostgreSQL Backup Approach

Tool selection guide for pg_dump, pg_basebackup, pgBackRest, Barman, and WAL-G based on dataset size, RPO/RTO targets, and storage.

dataopsphere.com · Recovery Readiness & SRE

From Backup Success to Recovery Readiness: The Restore Drill

Standardizing recovery readiness across engineering teams: maturity tiers, RACI roles, pre/during/post-drill checklists, and audit templates.