Postgres Pro
Downloads
Home   >   mailing lists

Thread: Promoting Hot standby after running select pg_xlog_replay_pause();

Promoting Hot standby after running select pg_xlog_replay_pause();

From
"Bellrose, Brian"
Date:

I have a scenario where we are doing an application release and they want to be able to potentially rollback to HA if things go bad. So the current process would be:

 

  1. Run select pg_xlog_replay_pause(); on standby to pause replication
  2. Apply changes
  3. If team needs to revert to older version of cluster we would run pg_ctl promote

 

My question is, would pg_ctl prmote start up from the point where replication was paused or would it try and sync before it opens? Thus applying all the changes I am trying to prevent?

 

Thanks,

Brian

 

This email and any attachments are only for use by the intended recipient(s) and may contain legally privileged, confidential, proprietary or otherwise private information. Any unauthorized use, reproduction, dissemination, distribution or other disclosure of the contents of this e-mail or its attachments is strictly prohibited. If you have received this email in error, please notify the sender immediately and delete the original. Neither this information block, the typed name of the sender, nor anything else in this message is intended to constitute an electronic signature unless a specific statement to the contrary is included in this message.

Re: Promoting Hot standby after running select pg_xlog_replay_pause();

From
Jerry Sievers
Date:
"Bellrose, Brian" <brian.bellrose@Wabtec.com> writes:

> I have a scenario where we are doing an application release and they
> want to be able to potentially rollback to HA if things go bad. So
> the current process would be:
>
>  
>
>  1. Run select pg_xlog_replay_pause(); on standby to pause
>     replication
>  2. Apply changes
>  3. If team needs to revert to older version of cluster we would run
>     pg_ctl promote
>
>  
>
> My question is, would pg_ctl prmote start up from the point where
> replication was paused or would it try and sync before it opens? Thus
> applying all the changes I am trying to prevent?

It will promote at the point where you were paused.

Consider also the named restore point feature.  Your deployment can
issue one just prior to running the invasive code and you can have your
standby seek to and pause at this precise WAL location.

At that point, you can either shutdown the standby, remove the
recovery_target_name from recovery.conf (or postgresql.conf >= v12) and
restart to keep it in standby service... or issue a
pg_wal_replay_resume() to the standby and it will promote.

Make certain that your standby had been config'd to use the setting and
that it had been restarted to observe it.

I have tested this feature only in an actual test and not used it in
production but it behaves as documented.

HTH
>
>  
>
> Thanks,
>
> Brian
>
>  
>
> This email and any attachments are only for use by the intended
> recipient(s) and may contain legally privileged, confidential,
> proprietary or otherwise private information. Any unauthorized use,
> reproduction, dissemination, distribution or other disclosure of the
> contents of this e-mail or its attachments is strictly prohibited. If
> you have received this email in error, please notify the sender
> immediately and delete the original. Neither this information block,
> the typed name of the sender, nor anything else in this message is
> intended to constitute an electronic signature unless a specific
> statement to the contrary is included in this message.
>
>

-- 
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net