From: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> |
---|---|
To: | "Zwettler Markus (OIZ)" <Markus(dot)Zwettler(at)zuerich(dot)ch>, "pgsql-admin(at)lists(dot)postgresql(dot)org" <pgsql-admin(at)lists(dot)postgresql(dot)org> |
Subject: | Re: AW: [Extern] Re: promote a deferred standby without applying WALs |
Date: | 2024-07-16 09:21:32 |
Message-ID: | 9720a2c9245df13906591abc4ede6cb10db32ab3.camel@cybertec.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Wasim Devale | 2024-07-16 12:48:51 | Re: indexes on partitioned tables |
Previous Message | Zwettler Markus (OIZ) | 2024-07-16 09:01:43 | AW: [Extern] Re: promote a deferred standby without applying WALs |