pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedAW: [Extern] Re: promote a deferred standby without applying WALs
2+ messages / 2 participants
[nested] [flat]
* AW: [Extern] Re: promote a deferred standby without applying WALs
@ 2024-07-16 09:01 Zwettler Markus (OIZ) <Markus.Zwettler@zuerich.ch>
0 siblings, 1 reply; 2+ messages in thread
From: Zwettler Markus (OIZ) @ 2024-07-16 09:01 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
> -----Ursprüngliche Nachricht-----
> Von: Laurenz Albe <laurenz.albe@cybertec.at>
> Gesendet: Dienstag, 16. Juli 2024 10:41
> An: Zwettler Markus (OIZ) <Markus.Zwettler@zuerich.ch>; pgsql-
> admin@lists.postgresql.org
> Betreff: [Extern] Re: promote a deferred standby without applying WALs
>
> On Tue, 2024-07-16 at 08:04 +0000, Zwettler Markus (OIZ) wrote:
> > I have a standby database running 3 hours behind the primary
> (recovery_min_apply_delay = '3h').
> >
> > In case of a logical error on the primary I want to promote the standby database
> which still has correct data.
> >
> > The standby should not apply any more WAL in that case.
> >
> > It seems that this can only be done manually:
> >
> > 1. pg_ctl stop
> > 2. rm -rf standby.signal
> > 3. set primary_conninfo = ''
> > 4. pg_ctl start
> >
> > Is there no single command on this?
>
> I don't think there is a single command.
>
> I would just set "recovery_target_time" to the appropriate time and reload.
>
> Perhaps this could be the single command:
>
> psql -c "ALTER SYSTEM SET recovery_target_time = '2024-07-16 12:00:00'" -c
> "SELECT pg_reload_conf()"
>
> Yours,
> Laurenz Albe
setting recovery_target_time + reload would be step 0 in that case, before stop/start. correct?
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: AW: [Extern] Re: promote a deferred standby without applying WALs
@ 2024-07-16 09:21 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Zwettler Markus (OIZ) <Markus.Zwettler@zuerich.ch>
0 siblings, 0 replies; 2+ messages in thread
From: Laurenz Albe @ 2024-07-16 09:21 UTC (permalink / raw)
To: Zwettler Markus (OIZ) <Markus.Zwettler@zuerich.ch>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
On Tue, 2024-07-16 at 09:01 +0000, Zwettler Markus (OIZ) wrote:
> > On Tue, 2024-07-16 at 08:04 +0000, Zwettler Markus (OIZ) wrote:
> > > I have a standby database running 3 hours behind the primary (recovery_min_apply_delay = '3h').
> > >
> > > In case of a logical error on the primary I want to promote the standby database
> > > which still has correct data.
> > >
> > > The standby should not apply any more WAL in that case.
> > >
> > > It seems that this can only be done manually:
> > >
> > > 1. pg_ctl stop
> > > 2. rm -rf standby.signal
> > > 3. set primary_conninfo = ''
> > > 4. pg_ctl start
> > >
> > > Is there no single command on this?
> >
> > Perhaps this could be the single command:
> >
> > psql -c "ALTER SYSTEM SET recovery_target_time = '2024-07-16 12:00:00'" -c
> > "SELECT pg_reload_conf()"
>
> setting recovery_target_time + reload would be step 0 in that case, before stop/start. correct?
No, that would be the only step. A restart is not necessary, and PostgreSQL will
automatically promote the standby when the target time is reached (if you set
"recovery_target_action" to "promote").
Yours,
Laurenz Albe
Attachments:
[application/pgp-signature] signature.asc (869B, ../../9720a2c9245df13906591abc4ede6cb10db32ab3.camel@cybertec.at/2-signature.asc)
download
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2024-07-16 09:21 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-07-16 09:01 AW: [Extern] Re: promote a deferred standby without applying WALs Zwettler Markus (OIZ) <Markus.Zwettler@zuerich.ch>
2024-07-16 09:21 ` Laurenz Albe <laurenz.albe@cybertec.at>
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