pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedActivate a standby database and then revert to standby
3+ messages / 3 participants
[nested] [flat]
* Activate a standby database and then revert to standby
@ 2021-10-28 09:12 Massimo Ortensi <mortensi@unimaticaspa.it>
2021-10-28 09:41 ` Re: Activate a standby database and then revert to standby Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 3+ messages in thread
From: Massimo Ortensi @ 2021-10-28 09:12 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Activate a standby database and then revert to standby
2021-10-28 09:12 Activate a standby database and then revert to standby Massimo Ortensi <mortensi@unimaticaspa.it>
@ 2021-10-28 09:41 ` Laurenz Albe <laurenz.albe@cybertec.at>
2021-10-28 09:50 ` Re: Activate a standby database and then revert to standby Vijaykumar Jain <vijaykumarjain.github@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Laurenz Albe @ 2021-10-28 09:41 UTC (permalink / raw)
To: Massimo Ortensi <mortensi@unimaticaspa.it>; pgsql-admin@lists.postgresql.org
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.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Activate a standby database and then revert to standby
2021-10-28 09:12 Activate a standby database and then revert to standby Massimo Ortensi <mortensi@unimaticaspa.it>
2021-10-28 09:41 ` Re: Activate a standby database and then revert to standby Laurenz Albe <laurenz.albe@cybertec.at>
@ 2021-10-28 09:50 ` Vijaykumar Jain <vijaykumarjain.github@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Vijaykumar Jain @ 2021-10-28 09:50 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Massimo Ortensi <mortensi@unimaticaspa.it>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
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.
>
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2021-10-28 09:50 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-10-28 09:12 Activate a standby database and then revert to standby Massimo Ortensi <mortensi@unimaticaspa.it>
2021-10-28 09:41 ` Laurenz Albe <laurenz.albe@cybertec.at>
2021-10-28 09:50 ` Vijaykumar Jain <vijaykumarjain.github@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox