A database replica does not replace a backup. How do you recover before a bad change?

A replica receives the change you did not want too

A physical standby continuously replays WAL, the change records received from the primary server. If those records include a committed deletion, replaying the relevant records brings its result to the replica too. Low lag therefore does not demonstrate protection from that mistake. It means a small distance from a state that may already be unwanted.

During an incident, first establish whether the problem is a failed machine or incorrect data. If the replica has applied the change, switching clients to it alone will not bring old records back. Preserve the timeline, operation identity and available evidence. Decide how to handle further writes according to service impact; automatic server failover cannot replace that assessment.

Going back in time requires history prepared in advance

Point-in-time recovery, or PITR, needs a suitable physical base backup and an unbroken WAL sequence from its start through the target. That backup must already be complete at the chosen point. pg_basebackup can take a base backup of a running cluster. Distinguish it from pg_dump, which represents a logical database snapshot. Verify the backup format and recovery procedure in advance.

In pg_stat_archiver, monitor failed_count and last_archived_time. The last successful archive operation alone does not establish that every older segment is present. Archiving works on completed WAL segments, so account for shipment delay when write volume is low. Assess backup and WAL retention together. Check that the required files and access remain available throughout the agreed recovery window.

Verify the recovery target outside production first

Choose a point before the unwanted committed transaction. For recovery_target_time, specify an unambiguous time zone, such as a numeric UTC offset. recovery_target_inclusive also controls inclusion of transactions with exactly the target commit time. Do not automatically treat the last-modified time shown in the application as a verified target. Confirm the choice through a test restore and inspection of the affected records.

PITR through this method restores the entire database cluster, not an individual table. Start the recovered copy in a separate environment with controlled access and outbound integrations disabled. Compare the state before the unwanted change with legitimate operations that happened later. Moving production entirely back in time would discard those later changes too; the data recovery plan must account for their meaning and relationships.

Measure success through correct data and service behaviour

pg_verifybackup checks the integrity of a supported physical backup against backup_manifest. Its documentation explicitly says this does not replace test restores and data checks. Record the backup used, the point reached and the validation result. A successful database start or healthy replication alone does not establish that you recovered the required version of the records.

In a safe exercise, prepare a known state, make a controlled test change and recover a copy to before it. Check specific records, relationships and the relevant application feature. Measure the whole procedure, including obtaining files and access. Repeat the exercise after changing backup tooling or retention. The replica and backup will then have verified roles in two different scenarios.

• Distinguish server failure from an unwanted data change.
• Verify a suitable base backup and continuous WAL history.
• Confirm the recovery point and content in a separate copy.
• Plan data recovery while preserving legitimate later changes.