Complete the platform with tested backup/PITR, a disposable physical standby, explicit fencing/promotion/routing procedures, core monitoring and SLO-derived alerts, supported-version upgrade checks, and incident runbooks.

Implement PITR, Replication/Failover, Monitoring, Alerts, Upgrades, and Incident Runbooks

Intermediate → Advanced240–330 minutesProduction capstone · ServiceHubPostgreSQL 18.4 baseline verified 2026-08-18; re-check current minor before productionCore PostgreSQL mandatory path; external poolers/HA control planes are optionalDisposable local cluster ports: primary 55480 · standby 55481 · restore 55482Administrative labs require PostgreSQL server utilities and a disposable local data directoryNo paid service required; destructive drills target only ch24_* lab resourcesLast reviewed: August 2026

Learning outcomes

A fast, secure primary is still a single point of operational failure if nobody can restore it, understand replication lag, fence a failed writer, or execute a supported upgrade. This lesson turns the ServiceHub database into an operable platform. PostgreSQL core supplies physical streaming replication, WAL archiving, recovery targets, promotion, statistics, and logs; it does not supply a complete automatic failover control plane, fencing system, DNS/proxy router, or off-site immutable storage service.

Configure WAL archiving and create a manifest-bearing physical base backup in the disposable cluster.

Verify the backup and perform a named-target PITR restore into a separate data directory/port.

Seed and observe a physical standby, including send/write/flush/replay positions and replication-slot retention.

Write a failover runbook that fences the old primary before promotion and explicitly reroutes clients.

Derive alerts from Chapter 24 SLOs and connect backup, WAL, replication, locks, capacity, and supported-version upgrades to runbooks.

1. Turn on the recovery/replication prerequisites deliberately

wal_level, max_wal_senders, max_replication_slots, and archive_mode are startup-sensitive settings. The archive command is platform-specific and must return success only after a WAL segment is safely archived. This lab stores the archive on the same machine for learning; production RPO requires a separate durability/failure domain.

conf · primary postgresql.conf — Unix archive example

wal_level = replicamax_wal_senders = 6max_replication_slots = 6archive_mode = on# Replace with your absolute CH24_ARCHIVE path.# If a file already exists, succeed only when it is byte-identical.archive_command = 'test ! -f /absolute/path/ch24_lab/archive/%f && cp %p /absolute/path/ch24_lab/archive/%f || cmp -s %p /absolute/path/ch24_lab/archive/%f'log_checkpoints = onlog_lock_waits = onlog_autovacuum_min_duration = '5s'

conf · Windows archive_command alternative

wal_level = replicamax_wal_senders = 6max_replication_slots = 6archive_mode = on# Replace C:\ABS\... with the real absolute lab path.# fc /b returns success only when the already-archived file is identical.archive_command = 'if exist "C:\\ABS\\ch24_lab\\archive\\%f" (fc /b "%p" "C:\\ABS\\ch24_lab\\archive\\%f" >NUL) else (copy "%p" "C:\\ABS\\ch24_lab\\archive\\%f" >NUL)'log_checkpoints = onlog_lock_waits = onlog_autovacuum_min_duration = '5s'

Restart the primary after changing the startup-context settings, then inspect what PostgreSQL actually accepted.

shell · restart after startup-context changes

pg_ctl -D "$CH24_PRIMARY" restart -m fast \  -l "$CH24_ROOT/primary.log" \  -o "-p 55480 -c listen_addresses=127.0.0.1"# Windows PowerShell uses the same pg_ctl.exe arguments:# pg_ctl.exe -D $env:CH24_PRIMARY restart -m fast `#   -l (Join-Path $env:CH24_ROOT "primary.log") `#   -o "-p 55480 -c listen_addresses=127.0.0.1"

sql · configuration source/scope verification

SELECT name,setting,context,source,pending_restartFROM pg_settingsWHERE name IN (  'wal_level','max_wal_senders','max_replication_slots',  'archive_mode','archive_command',  'log_checkpoints','log_lock_waits','log_autovacuum_min_duration')ORDER BY name;SELECT archived_count,failed_count,last_archived_wal,last_failed_wal,       last_archived_time,last_failed_time,stats_resetFROM pg_stat_archiver;

2. Take and verify a physical base backup before the recovery target

PITR can only target WAL positions reachable after the physical base backup's recovery starting point. Therefore take the good base backup first, then create the named restore point, then inject the bad change. This ordering is a correctness requirement, not a cosmetic runbook preference.

shell · base backup with WAL and manifest

rm -rf "$CH24_BACKUP"pg_basebackup \  -h 127.0.0.1 -p 55480 -U postgres \  -D "$CH24_BACKUP" \  -Fp -X stream \  --manifest-checksums=SHA256 \  --progresspg_verifybackup "$CH24_BACKUP"

powershell · Windows equivalent base backup

if (Test-Path $env:CH24_BACKUP) {  Remove-Item -Recurse -Force $env:CH24_BACKUP}pg_basebackup.exe `  -h 127.0.0.1 -p 55480 -U postgres `  -D $env:CH24_BACKUP `  -Fp -X stream `  --manifest-checksums=SHA256 `  --progresspg_verifybackup.exe $env:CH24_BACKUP

pg_verifybackup verifies the backup against its manifest, but a successful result still does not prove PostgreSQL can start with your configuration, extensions, permissions, external secrets, or that business invariants are correct. The restore drill closes that gap.

3. Create a named target, inject the bad change, and force archival

The base backup now represents a known-good starting point. Create a restore point after that backup, commit a bad change, then switch WAL so the archive receives the segment containing the target and subsequent change.

sql · target then bad change

SELECT pg_create_restore_point('ch24_before_bad_change');SELECT pg_current_wal_lsn() AS restore_boundary_lsn,       clock_timestamp() AS restore_boundary_time;UPDATE app.work_ordersSET status='cancelled',    updated_at=clock_timestamp()WHERE tenant_id = (  SELECT tenant_id FROM app.tenants WHERE tenant_code='tenant-a')AND status='queued';SELECT count(*) AS cancelled_after_bad_changeFROM app.work_ordersWHERE tenant_id = (  SELECT tenant_id FROM app.tenants WHERE tenant_code='tenant-a')AND status='cancelled';SELECT pg_switch_wal();

sql · wait for archive evidence before restoring

SELECT archived_count,failed_count,       last_archived_wal,last_archived_time,       last_failed_wal,last_failed_timeFROM pg_stat_archiver;-- Repeat after a short interval until the switched WAL has archived-- and failed_count is not increasing.

4. Restore to the named point in a separate directory

Copy the verified base backup to the restore directory, point restore_command at the WAL archive, select the named target, and create recovery.signal. Because the target was created after the base backup, recovery can replay forward to that point and pause before the deliberately bad change.

shell · Unix PITR restore target

rm -rf "$CH24_RESTORE"cp -a "$CH24_BACKUP" "$CH24_RESTORE"cat >> "$CH24_RESTORE/postgresql.auto.conf" <<EOFport = 55482restore_command = 'cp $CH24_ARCHIVE/%f %p'recovery_target_name = 'ch24_before_bad_change'recovery_target_action = 'pause'recovery_target_timeline = 'latest'EOFtouch "$CH24_RESTORE/recovery.signal"pg_ctl -D "$CH24_RESTORE" \  -l "$CH24_ROOT/restore.log" start

powershell · Windows PITR restore target

if (Test-Path $env:CH24_RESTORE) {  Remove-Item -Recurse -Force $env:CH24_RESTORE}Copy-Item -Recurse $env:CH24_BACKUP $env:CH24_RESTORE$archive = $env:CH24_ARCHIVE.Replace('\','\\')Add-Content (Join-Path $env:CH24_RESTORE "postgresql.auto.conf") @"port = 55482restore_command = 'copy "$archive\\%f" "%p" >NUL'recovery_target_name = 'ch24_before_bad_change'recovery_target_action = 'pause'recovery_target_timeline = 'latest'"@New-Item -ItemType File -Force (Join-Path $env:CH24_RESTORE "recovery.signal") | Out-Nullpg_ctl.exe -D $env:CH24_RESTORE `  -l (Join-Path $env:CH24_ROOT "restore.log") start

5. Verify PITR before promotion

sql · run against restore port 55482

SELECT pg_is_in_recovery(),       pg_get_wal_replay_pause_state(),       pg_last_wal_replay_lsn();SELECT count(*) AS queued,       count(*) FILTER (WHERE status='cancelled') AS cancelledFROM app.work_ordersWHERE tenant_id = (  SELECT tenant_id FROM app.tenants WHERE tenant_code='tenant-a');SELECT pg_promote(wait_seconds => 60);

Only promote after application-level invariants match the recovery objective. Promotion creates a new timeline; retain timeline history and the WAL required for future recovery. A PITR restore is a new branch of history, not a way to merge the restored state back into a still-writable old primary.

6. Seed a physical standby with a dedicated replication slot

For the local-only lab, temporarily allow the replication role from loopback using trust to avoid storing a password in course files. This is deliberately not a production authentication pattern; use SCRAM/certificate/TLS policy in production.

conf · disposable-loopback pg_hba.conf addition

# LOCAL CAPSTONE LAB ONLY — never broad production trusthost replication servicehub_cap_repl 127.0.0.1/32 trust

sql · reload and inspect HBA parse state

SELECT pg_reload_conf();SELECT line_number,type,database,user_name,address,auth_method,errorFROM pg_hba_file_rulesWHERE database @> ARRAY['replication']::text[]ORDER BY line_number;

sql · repeatable standby-slot preflight on the primary

SELECT slot_name,active,slot_typeFROM pg_replication_slotsWHERE slot_name='ch24_standby_slot';-- If a previous disposable run left the slot inactive, remove it before -C:SELECT pg_drop_replication_slot('ch24_standby_slot')WHERE EXISTS (  SELECT 1  FROM pg_replication_slots  WHERE slot_name='ch24_standby_slot'    AND NOT active);

shell · create and start standby

rm -rf "$CH24_STANDBY"pg_basebackup \  -h 127.0.0.1 -p 55480 \  -U servicehub_cap_repl \  -D "$CH24_STANDBY" \  -Fp -X stream -R \  -C -S ch24_standby_slot \  --progresspg_ctl -D "$CH24_STANDBY" \  -l "$CH24_ROOT/standby.log" \  -o "-p 55481" start

powershell · Windows create/start standby

if (Test-Path $env:CH24_STANDBY) {  Remove-Item -Recurse -Force $env:CH24_STANDBY}pg_basebackup.exe `  -h 127.0.0.1 -p 55480 `  -U servicehub_cap_repl `  -D $env:CH24_STANDBY `  -Fp -X stream -R `  -C -S ch24_standby_slot `  --progresspg_ctl.exe -D $env:CH24_STANDBY `  -l (Join-Path $env:CH24_ROOT "standby.log") `  -o "-p 55481" start

7. Observe send/write/flush/replay and slot retention

sql · primary replication evidence

SELECT application_name,state,sync_state,       sent_lsn,write_lsn,flush_lsn,replay_lsn,       write_lag,flush_lag,replay_lagFROM pg_stat_replicationORDER BY application_name;SELECT slot_name,slot_type,active,       restart_lsn,       wal_status,       safe_wal_sizeFROM pg_replication_slotsWHERE slot_name='ch24_standby_slot';

sql · standby receiver evidence — port 55481

SELECT pg_is_in_recovery();SELECT status,sender_host,sender_port,slot_name,       written_lsn,flushed_lsn,latest_end_lsn,latest_end_timeFROM pg_stat_wal_receiver;

Lag columns are not promises and can become NULL on an idle stream. Slot retention can grow if a standby stops consuming WAL; an inactive slot can therefore create a primary-disk incident. The runbook monitors both replication state and retained WAL/capacity.

8. Failover runbook: fence before promote

text · operator sequence

1. Declare incident and stop automated/ambiguous write routing.2. Prove the old primary cannot accept writes:   - stop/disable service, isolate network, revoke storage lease, or equivalent fencing.3. Check candidate standby:   - pg_is_in_recovery()   - last replay position / application heartbeat   - known replication lag and RPO impact4. Promote:   SELECT pg_promote(wait_seconds => 60);5. Verify:   - pg_is_in_recovery() = false   - business write/read transaction succeeds   - timeline changed as expected6. Reroute clients through the application/proxy/DNS/service layer.7. Do NOT simply restart the old primary as another writer.8. Rebuild/rewind/reseed the old node according to the new timeline.9. Record actual RPO, RTO, lost/duplicate work, and follow-up actions.

PostgreSQL promotion does not fence the old primary and does not update application routing. Those are external operational controls. Two writable primaries without conflict coordination are a split-brain risk.

9. Derive alerts from the SLO and evidence chain

sql · core health snapshot

SELECT now() AS observed_at,       numbackends,xact_commit,xact_rollback,       deadlocks,temp_bytes,blk_read_time,blk_write_timeFROM pg_stat_databaseWHERE datname=current_database();SELECT pid,usename,application_name,state,       wait_event_type,wait_event,       now()-xact_start AS xact_age,       now()-query_start AS query_age,       query_idFROM pg_stat_activityWHERE datname=current_database()ORDER BY xact_start NULLS LAST;SELECT archived_count,failed_count,       last_archived_time,last_failed_timeFROM pg_stat_archiver;

Signal	SLO-derived alert idea	Runbook
archive failure/backlog	any sustained failure threatens the 60 s recovery-point design	check destination permissions/capacity; never delete pg_wal manually
replication lag/slot retention	warning before measured lag/capacity threatens RPO or primary disk	repair receiver/network or rebuild/drop slot only with topology decision
old transaction / lock queue	age above application transaction design and blocked waiter appears	identify blocker; cancel/terminate only after ownership/impact check
disk/capacity	forecast exhaustion occurs before procurement/resize lead time	growth attribution, WAL/archive/log/temp breakdown, expansion plan

10. Supported-version upgrade runbook

shell · pre-change version evidence

postgres --versionpsql --versionpg_upgrade --version# For a major-version change, use the NEW major's pg_upgrade:pg_upgrade --check   --old-bindir=/path/to/old/bin   --new-bindir=/path/to/new/bin   --old-datadir=/path/to/old/data   --new-datadir=/path/to/new/data

For a PostgreSQL 18.x minor update, read every intervening release note and apply the current 18.x binaries; a dump/restore or pg_upgrade is not normally required solely for a minor update. For a major upgrade, re-run extension/collation compatibility, pg_upgrade --check for the intended transfer mode, backup/rollback planning, and the Lesson 23 old/new workload comparison before approval.

11. Deliberately wrong HA response

Unsafe incident response

The primary stops responding, so an operator immediately promotes the standby while the old primary is still reachable from some application hosts. This can create two writers. The repair is to make fencing an explicit precondition, then promote and reroute only after the old writer is provably unable to accept writes.

Production judgment

Backup, standby, monitoring, alerting and upgrade procedures are only useful when practiced together. A backup file is not recovery; a replica is not fencing; a dashboard is not a runbook. Lesson 5 injects failures and measures whether this system actually satisfies the chapter's RPO/RTO/correctness targets.

Check your understanding

What does pg_verifybackup prove, and what does it not prove?
Why can an inactive physical replication slot threaten primary disk capacity?
What must happen before standby promotion in a single-writer design?
Why is a physical standby not sufficient protection from accidental DELETE?
Why are minor and major PostgreSQL upgrades operationally different?

Review the answers

pg_verifybackup verifies files against the backup manifest but not complete startup/application recoverability. A slot can retain WAL required by a lagging/inactive consumer. The old primary must be fenced before promotion to prevent split brain. A standby faithfully replays accidental DELETE, while PITR can restore before it. Minor releases keep the major's data format and are binary updates; major releases change compatibility and require pg_upgrade, logical migration, or dump/restore plus broader validation.

Authoritative references

Use current upstream PostgreSQL documentation and release/support pages as the source of truth for version-, security-, topology-, and recovery-sensitive behavior.

Keep knowledge open

Help the academy stay free and grow.

If these tutorials save you time, a small donation supports new lessons, technical review, diagrams, examples, and long-term maintenance.

ETHEthereum / ERC-20 only

0x716c4Ab160C4B66F31a28AE2448BfF68fc3a2ef0

Send only assets compatible with the Ethereum/ERC-20 network. Do not send TRC-20/TRON assets.