Re: Activate a standby database and then revert to standby

On Thu, Oct 28, 2021, 3:11 PM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)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.
>

https://youtu.be/qc7HTiJm_tQ

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.