3

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?

2
  • 1
    Normally, backups are taken before something like this happens. But I'm afraid you wouldn't be the first taking a backup afterwards, meanwhile overwriting the only real backup they had. You might also want to revoke some privileges from users prone to drop tables accidentally. It's not as if you would give the root password to peoply behaving carelessly. Commented Dec 14, 2021 at 18:59
  • Wait until you hear from someone with more PostgreSQL experience than me, but perhaps you can shutdown the standby, remove the standby lines from its postgresql.conf and restart it? Commented Dec 14, 2021 at 19:22

2 Answers 2

2

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 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.

0

Technically, it might be possible with everything set up just right, in advance.

Practically, you're looking at doing a Point-in-Time recovery to a copy of the database, extracting the table and then putting that data back into the original database.

The problem is just how fast your database is.

Sending the drop command from the primary to the standby doesn't take very long and, as soon as it's gone across and the standby has "noticed" it, the deed is done and table is, to all intents and purposes, gone from both databases.

OK, you have a 5 minute delay built in. How long does it normally take your Users/Developers to tell you that they've done something stupid?

  • Under a minute?
    They're Superhuman.
  • A couple of minutes?
    They're more honest than most and won't have tried to do anything to hide their mistake. Still unlikely.
  • Four or five minutes?
    This is getting more like it but that's most, if not all, of your Safety Margin already gone. You'll have only seconds to kill the replication and possibly save the day.

More often than not, you'll get an email after half an hour or more, by which time your standby database will be trashed as well.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.