Thread: promote a deferred standby without applying WALs
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:
- pg_ctl stop
- rm -rf standby.signal
- set primary_conninfo = ''
- pg_ctl start
Is there no single command on this?
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
Attachment
AW: [Extern] Re: promote a deferred standby without applying WALs
From
"Zwettler Markus (OIZ)"
Date:
> -----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?
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