patternsqlMinor
Postgres HOT standby backup
Viewed 0 times
standbybackuppostgreshot
Problem
Postgres (13) hot standby with streaming replication
Would like to be able to recover table from standby if user accidentally drop table on primary.
Standby is set with 5 minute replication delay
Once drop issued on primary, I immediately paused wal processing on standby and attempted to dump table (pg_dump) but process hangs and them times out.
Additional research showed as soon as drop issued on primary, Wal processing recognizes this and immediately puts exclusive lock on the table (even though command will not be applied until 5 minute delay has expired)- as a result unable to read the table until exclusive lock is released. and I am unable to issue pg_dump against the table.
Is there a way to take a backup (pg_dump) of a table on standby when drop has been issued on the primary?
Would like to be able to recover table from standby if user accidentally drop table on primary.
Standby is set with 5 minute replication delay
Once drop issued on primary, I immediately paused wal processing on standby and attempted to dump table (pg_dump) but process hangs and them times out.
Additional research showed as soon as drop issued on primary, Wal processing recognizes this and immediately puts exclusive lock on the table (even though command will not be applied until 5 minute delay has expired)- as a result unable to read the table until exclusive lock is released. and I am unable to issue pg_dump against the table.
Is there a way to take a backup (pg_dump) of a table on standby when drop has been issued on the primary?
Solution
Wow, that is kind of nasty. The problem is that the streaming delay is implemented upon commit (as that is the only WAL record which has the timestamp) but by that time the LOCK will have already been replayed.
You can shutdown the replica immediately (-mi switch to pg_ctl stop) and then edit the conf file to tell it to pause replay before the LOCK commands gets replayed. Then you would be able to run pg_dump. If this is important you should take a backup after the immediate shutdown, so that you can try again if you mess it up. (But if you already did a clean shutdown or had a restartpoint, then it is probably too late as you will no longer be able to pause replay prior to the LOCK being replayed.)
To try to make it pause before the lock is replayed, you can either set
This is pretty fragile, but then again using recovery_min_apply_delay as a backup tool is inherently fragile. Hopefully you have a backup backup method as well and were just using this as a convenience method.
In hindsight, it looks like adding a timestamp to LOCK records would have been a good idea, and pausing before their replay as well.
You can shutdown the replica immediately (-mi switch to pg_ctl stop) and then edit the conf file to tell it to pause replay before the LOCK commands gets replayed. Then you would be able to run pg_dump. If this is important you should take a backup after the immediate shutdown, so that you can try again if you mess it up. (But if you already did a clean shutdown or had a restartpoint, then it is probably too late as you will no longer be able to pause replay prior to the LOCK being replayed.)
To try to make it pause before the lock is replayed, you can either set
recovery_target = 'immediate', or you could use pg_waldump to find the LSN of the LOCK record and then set recovery_target_lsn to that value and set recovery_target_inclusive to off.This is pretty fragile, but then again using recovery_min_apply_delay as a backup tool is inherently fragile. Hopefully you have a backup backup method as well and were just using this as a convenience method.
In hindsight, it looks like adding a timestamp to LOCK records would have been a good idea, and pausing before their replay as well.
Context
StackExchange Database Administrators Q#303933, answer score: 2
Revisions (0)
No revisions yet.