Обсуждение: Activate a standby database and then revert to standby
I need to activate (open in read and write) a standby database, perform some action and then revert it to the previous status of standby and thus starting again loading wals from when it stopped. How should I proceed to be sure it's possible to get "back in time" to the standby db ? It's a huge DB and can't afford restarting sync from scratch
On Thu, Oct 28, 2021, 3:11 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Thu, 2021-10-28 at 11:12 +0200, Massimo Ortensi wrote:
> I need to activate (open in read and write) a standby database, perform
> some action and then revert it to the previous status of standby and
> thus starting again loading wals from when it stopped.
>
> How should I proceed to be sure it's possible to get "back in time" to
> the standby db ?
> It's a huge DB and can't afford restarting sync from scratch
Yes. For that, you have to enable "wal_log_hints" and set "wal_keep_size"
high enough. Then you can use "pg_rewind" to restore the promoted standby
to a state when it can be a standby again.
This helped me understand pg_rewind pretty quickly with a demo. Ofcourse the original docs are the best, but I like the channel.
repmgr, patroni ha solutions allow making use of switchover and switchback making use of pg_rewind internally if you use any of those options.
Else manually as above.