Replay standby WAL to point in time without creating new timeline
1
vote
1
answer
117
views
We have a PostgreSQL 13 replication cluster where one of the standbys is setup with a
recovery_min_apply_delay
="x hr", which could be useful in the case of data corruption.
For a scenario where an accidental delete happened on the *master server*, but not yet applied to the *standby server*. In this case, I am trying to extract the data from standby server by removing the *WAL replay delay* and adding recovery_target_time
to a point before delete happened.
To this point I am successful, but once I done with pulling data from standby server to master (I am thinking of postgres_fdw), I need to resume replication. But when I run pg_wal_replay_resume()
, it is creating a new timeline and is no longer in the replication cluster.
Is there any way to replay WAL other than using recovery_target_time
?
Giving an example of what I am trying to achieve,
*Let's say recovery_min_apply_delay=2hr , an accidental delete happened at 03:50 AM, at this time transactions committed at standby is transactions that happened on or before 01:50 AM. If I wait another 2hr and at 05:49 AM if I am able to pause standby WAL replay I will be able see a snapshot of data which is "right before" DELETE. Then I will use postgres_fdw to pull data from standby.*
How do I achieve "right before" in a precise systematic way? Also I would be able to resume WAL replay.
Asked by goodfella
(595 rep)
Feb 17, 2025, 04:47 AM
Last activity: Feb 18, 2025, 09:56 AM
Last activity: Feb 18, 2025, 09:56 AM