pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedpromote a deferred standby without applying WALs
2+ messages / 2 participants
[nested] [flat]
* promote a deferred standby without applying WALs
@ 2024-07-16 08:04 Zwettler Markus (OIZ) <Markus.Zwettler@zuerich.ch>
0 siblings, 1 reply; 2+ messages in thread
From: Zwettler Markus (OIZ) @ 2024-07-16 08:04 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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?
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: promote a deferred standby without applying WALs
@ 2024-07-16 08:40 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 08:40 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 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
Attachments:
[application/pgp-signature] signature.asc (869B, ../../32b72ef82aba2e54a1eaaab248197a47091d675c.camel@cybertec.at/2-signature.asc)
download
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2024-07-16 08:40 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 08:04 promote a deferred standby without applying WALs Zwettler Markus (OIZ) <Markus.Zwettler@zuerich.ch>
2024-07-16 08:40 ` 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