Thread: Using old master as new replica after clean switchover
Hello Postgres Gurus,
After searching (on www.postgresql.org/Google) I found that the following steps can be used to perform a switchover in Postgres (version 9.3):
Step 1. Do clean shutdown of Primary (-m fast or smart).
Step 2. Check for sync status and recovery status of Standby before promoting it.
Once Standby is in complete sync. At this stage we are safe to promote it as Primary.
Step 3. Open the Standby as new Primary by pg_ctl promote or creating a trigger file.
Step 4. Restart old Primary as standby and allow to follow the new timeline by passing "recovery_target_timline='latest'" in \
$PGDATA/recovery.conf file.
But I also read in one of the google post that this procedure requires the WAL archive location to exist on a shared storage to which both the Master and Slave should have access to.
So wanted to clarify if this procedure really requires the WAL archive location on a shared storage ?
Thanks
Raj
On Tue, Feb 19, 2019 at 04:27:02PM -0800, RSR999GMAILCOM wrote: > So wanted to clarify if this procedure really requires the WAL archive > location on a shared storage ? Shared storage for WAL archives is not a requirement. It is perfectly possible to use streaming replication to get correct WAL changes. Using an archive is recommended for some deployments and depending on your requirements and data retention policy, still you could have those archives on a different host and have the restore_command of the standbyt in recovery or the archive_command of the primary save the segments to it. Depending on the frequency new WAL segments are generated, this depends of course. -- Michael
Attachment
Is there any link where the required setup and the step by step procedure for performing the controlled switchover are listed?
Thanks
Raj
On Tue, Feb 19, 2019 at 4:44 PM Michael Paquier <michael@paquier.xyz> wrote:
On Tue, Feb 19, 2019 at 04:27:02PM -0800, RSR999GMAILCOM wrote:
> So wanted to clarify if this procedure really requires the WAL archive
> location on a shared storage ?
Shared storage for WAL archives is not a requirement. It is perfectly
possible to use streaming replication to get correct WAL changes.
Using an archive is recommended for some deployments and depending on
your requirements and data retention policy, still you could have
those archives on a different host and have the restore_command of the
standbyt in recovery or the archive_command of the primary save the
segments to it. Depending on the frequency new WAL segments are
generated, this depends of course.
--
Michael
On Tue, Feb 19, 2019 at 9:44 PM Michael Paquier <michael@paquier.xyz> wrote: > > On Tue, Feb 19, 2019 at 04:27:02PM -0800, RSR999GMAILCOM wrote: > > So wanted to clarify if this procedure really requires the WAL archive > > location on a shared storage ? > > Shared storage for WAL archives is not a requirement. It is perfectly > possible to use streaming replication to get correct WAL changes. > Using an archive is recommended for some deployments and depending on > your requirements and data retention policy, still you could have > those archives on a different host and have the restore_command of the > standbyt in recovery or the archive_command of the primary save the > segments to it. Depending on the frequency new WAL segments are > generated, this depends of course. If I'm not mistaken, if you don't have WAL archive set up (a shared filesystem isn't necessary, but the standby has to be able to restore WAL segments from the archive), a few transactions that haven't been streamed at primary shutdown could be lost, since the secondary won't be able to stream anything after the primary has shut down. WAL archive can always be restored even without a primary running, hence why a WAL archive is needed. Or am I missing something?
On Thu, Feb 21, 2019 at 03:38:21PM -0300, Claudio Freire wrote: > If I'm not mistaken, if you don't have WAL archive set up (a shared > filesystem isn't necessary, but the standby has to be able to restore > WAL segments from the archive), a few transactions that haven't been > streamed at primary shutdown could be lost, since the secondary won't > be able to stream anything after the primary has shut down. WAL > archive can always be restored even without a primary running, hence > why a WAL archive is needed. > > Or am I missing something? Well, my point is that you may not need an archive if you are able to stream the changes from a primary using streaming if the primary has a replication slot or if a checkpoint has not recycled yet the segments that a standby may need. If the primary is offline, and you need to recover a standby, then an archive is mandatory. When recovering from an archive, the standby would be able to catch up to the end of the segment archived as we don't enforce a segment switch when a node shuts down. If using pg_receivewal as a form of archiving with its --synchronous mode, it is also possible to stream up to the point where the primary has generated its shutdown checkpoint, so you would not lose data included on the last segment the primary was working on when stopped. -- Michael
Attachment
On Thu, Feb 21, 2019 at 10:26:37AM -0800, RSR999GMAILCOM wrote: > Is there any link where the required setup and the step by step procedure > for performing the controlled switchover are listed? Docs about failover are here: https://www.postgresql.org/docs/current/warm-standby-failover.html Now I don't recall that we have a section about a step-by-step procedure for one case of failover or another. The docs could be perhaps improved regarding that, particularly for the case mentioned here where it is possible to relink a previous master to a promoted standby without risks of corruption: - Stop cleanly the primary with smart or fast mode. - Promote the standby. - Add recovery.conf to the previous primary. - Restart the previous primary as a new standby. -- Michael
Attachment
On Thu, 21 Feb 2019 15:38:21 -0300 Claudio Freire <klaussfreire@gmail.com> wrote: > On Tue, Feb 19, 2019 at 9:44 PM Michael Paquier <michael@paquier.xyz> wrote: > > > > On Tue, Feb 19, 2019 at 04:27:02PM -0800, RSR999GMAILCOM wrote: > > > So wanted to clarify if this procedure really requires the WAL archive > > > location on a shared storage ? > > > > Shared storage for WAL archives is not a requirement. It is perfectly > > possible to use streaming replication to get correct WAL changes. > > Using an archive is recommended for some deployments and depending on > > your requirements and data retention policy, still you could have > > those archives on a different host and have the restore_command of the > > standbyt in recovery or the archive_command of the primary save the > > segments to it. Depending on the frequency new WAL segments are > > generated, this depends of course. > > If I'm not mistaken, if you don't have WAL archive set up (a shared > filesystem isn't necessary, but the standby has to be able to restore > WAL segments from the archive), a few transactions that haven't been > streamed at primary shutdown could be lost, since the secondary won't > be able to stream anything after the primary has shut down. This has been fixed in 9.3. The primary node wait for all WAL records to be streamed to the connected standbys before shutting down. Including its shutdown checkpoint. See: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=985bd7d49726c9f178558491d31a570d47340459 Because a standby could disconnect because of some failure during the shutdown process, you still need to make sure the standby-to-be-promoted received the shutdown checkpoint though. > WAL archive can always be restored even without a primary running, hence > why a WAL archive is needed. No. Primary does not force a WAL switch/archive during shutdown. -- Jehan-Guillaume de Rorthais Dalibo
On Fri, Feb 22, 2019 at 5:47 AM Jehan-Guillaume de Rorthais <jgdr@dalibo.com> wrote: > > On Thu, 21 Feb 2019 15:38:21 -0300 > Claudio Freire <klaussfreire@gmail.com> wrote: > > > On Tue, Feb 19, 2019 at 9:44 PM Michael Paquier <michael@paquier.xyz> wrote: > > > > > > On Tue, Feb 19, 2019 at 04:27:02PM -0800, RSR999GMAILCOM wrote: > > > > So wanted to clarify if this procedure really requires the WAL archive > > > > location on a shared storage ? > > > > > > Shared storage for WAL archives is not a requirement. It is perfectly > > > possible to use streaming replication to get correct WAL changes. > > > Using an archive is recommended for some deployments and depending on > > > your requirements and data retention policy, still you could have > > > those archives on a different host and have the restore_command of the > > > standbyt in recovery or the archive_command of the primary save the > > > segments to it. Depending on the frequency new WAL segments are > > > generated, this depends of course. > > > > If I'm not mistaken, if you don't have WAL archive set up (a shared > > filesystem isn't necessary, but the standby has to be able to restore > > WAL segments from the archive), a few transactions that haven't been > > streamed at primary shutdown could be lost, since the secondary won't > > be able to stream anything after the primary has shut down. > > This has been fixed in 9.3. The primary node wait for all WAL records to be > streamed to the connected standbys before shutting down. Including its shutdown > checkpoint. See: > > https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=985bd7d49726c9f178558491d31a570d47340459 > > Because a standby could disconnect because of some failure during the shutdown > process, you still need to make sure the standby-to-be-promoted received the > shutdown checkpoint though. > > > WAL archive can always be restored even without a primary running, hence > > why a WAL archive is needed. > > No. Primary does not force a WAL switch/archive during shutdown. That's good to know, both of the above.