pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedSwitchover of Master and Slave roles
11+ messages / 7 participants
[nested] [flat]
* Switchover of Master and Slave roles
@ 2011-06-07 16:32 A J <s5aly@yahoo.com>
2011-06-07 18:12 ` Re: Switchover of Master and Slave roles Ray Stell <stellr@cns.vt.edu>
0 siblings, 1 reply; 11+ messages in thread
From: A J @ 2011-06-07 16:32 UTC (permalink / raw)
To: pgsql-admin
Hello,
I am trying to switch the master and slave roles in a test I am doing with
streaming replication in 9.1 beta.
To start with, I have one master (N1 node) and one slave (N2 node).
I stop N1 and promote N2 as primary (by touching the trigger file).
Now I wish for N1 to come back up as slave of N2. How should I do that ?
I tried creating a recovery.conf in N1's directory, along with cleaning up its
pg_xlog and pg_log.
But when I start N1 and try to connect to it, I get the message:
psql: FATAL: the database system is starting up
And the log file of N1 says:
FATAL: timeline 2 of the primary does not match recovery target timeline 1
Any ideas on what could I be missing ?
Thanks,
AJ
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Switchover of Master and Slave roles
2011-06-07 16:32 Switchover of Master and Slave roles A J <s5aly@yahoo.com>
@ 2011-06-07 18:12 ` Ray Stell <stellr@cns.vt.edu>
2011-06-07 18:33 ` Re: Switchover of Master and Slave roles A J <s5aly@yahoo.com>
0 siblings, 1 reply; 11+ messages in thread
From: Ray Stell @ 2011-06-07 18:12 UTC (permalink / raw)
To: A J <s5aly@yahoo.com>; +Cc: pgsql-admin
On Tue, Jun 07, 2011 at 09:32:31AM -0700, A J wrote:
> Hello,
> I am trying to switch the master and slave roles in a test I am doing with
> streaming replication in 9.1 beta.
>
http://www.postgresql.org/docs/9.1/static/warm-standby-failover.html
Once failover to the standby occurs, there is only a single server in
operation. This is known as a degenerate state. The former standby is
now the primary, but the former primary is down and might stay down. To
return to normal operation, a standby server must be recreated, either
on the former primary system when it comes up, or on a third, possibly
new, system. Once complete, the primary and standby can be considered
to have switched roles.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Switchover of Master and Slave roles
2011-06-07 16:32 Switchover of Master and Slave roles A J <s5aly@yahoo.com>
2011-06-07 18:12 ` Re: Switchover of Master and Slave roles Ray Stell <stellr@cns.vt.edu>
@ 2011-06-07 18:33 ` A J <s5aly@yahoo.com>
2011-06-07 18:40 ` Re: Switchover of Master and Slave roles Guillaume Lelarge <guillaume@lelarge.info>
2011-06-07 18:44 ` Re: Switchover of Master and Slave roles Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 2 replies; 11+ messages in thread
From: A J @ 2011-06-07 18:33 UTC (permalink / raw)
To: Ray Stell <stellr@cns.vt.edu>; +Cc: pgsql-admin
>>To return to normal operation, a standby server must be recreated, either on the
>>former primary system when it comes up<<
What does it exactly mean to 'recreate a standby server' ? Can I not use the
datafiles on the former primary and just let it sync and get the incremental
from the new primary ? Do I have to remove all the data files from the former
primary and get all the datafiles through rsync (or other similar manner) from
the new primary ?
________________________________
From: Ray Stell <stellr@cns.vt.edu>
To: A J <s5aly@yahoo.com>
Cc: pgsql-admin@postgresql.org
Sent: Tue, June 7, 2011 2:12:44 PM
Subject: Re: [ADMIN] Switchover of Master and Slave roles
On Tue, Jun 07, 2011 at 09:32:31AM -0700, A J wrote:
> Hello,
> I am trying to switch the master and slave roles in a test I am doing with
> streaming replication in 9.1 beta.
>
http://www.postgresql.org/docs/9.1/static/warm-standby-failover.html
Once failover to the standby occurs, there is only a single server in
operation. This is known as a degenerate state. The former standby is
now the primary, but the former primary is down and might stay down. To
return to normal operation, a standby server must be recreated, either
on the former primary system when it comes up, or on a third, possibly
new, system. Once complete, the primary and standby can be considered
to have switched roles.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Switchover of Master and Slave roles
2011-06-07 16:32 Switchover of Master and Slave roles A J <s5aly@yahoo.com>
2011-06-07 18:12 ` Re: Switchover of Master and Slave roles Ray Stell <stellr@cns.vt.edu>
2011-06-07 18:33 ` Re: Switchover of Master and Slave roles A J <s5aly@yahoo.com>
@ 2011-06-07 18:40 ` Guillaume Lelarge <guillaume@lelarge.info>
1 sibling, 0 replies; 11+ messages in thread
From: Guillaume Lelarge @ 2011-06-07 18:40 UTC (permalink / raw)
To: A J <s5aly@yahoo.com>; +Cc: Ray Stell <stellr@cns.vt.edu>; pgsql-admin
On Tue, 2011-06-07 at 11:33 -0700, A J wrote:
> >>To return to normal operation, a standby server must be recreated,
> either on the former primary system when it comes up<<
>
>
> What does it exactly mean to 'recreate a standby server' ? Can I not
> use the datafiles on the former primary and just let it sync and get
> the incremental from the new primary ? Do I have to remove all the
> data files from the former primary and get all the datafiles through
> rsync (or other similar manner) from the new primary ?
>
>
Don't remove them. This way, rsync will only send the changed files.
--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Switchover of Master and Slave roles
2011-06-07 16:32 Switchover of Master and Slave roles A J <s5aly@yahoo.com>
2011-06-07 18:12 ` Re: Switchover of Master and Slave roles Ray Stell <stellr@cns.vt.edu>
2011-06-07 18:33 ` Re: Switchover of Master and Slave roles A J <s5aly@yahoo.com>
@ 2011-06-07 18:44 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-06-07 18:59 ` Re: Switchover of Master and Slave roles A J <s5aly@yahoo.com>
1 sibling, 1 reply; 11+ messages in thread
From: Kevin Grittner @ 2011-06-07 18:44 UTC (permalink / raw)
To: Ray Stell <stellr@cns.vt.edu>; A J <s5aly@yahoo.com>; +Cc: pgsql-admin
A J <s5aly@yahoo.com> wrote:
> What does it exactly mean to 'recreate a standby server' ? Can I
> not use the datafiles on the former primary and just let it sync
> and get the incremental from the new primary ? Do I have to remove
> all the data files from the former primary and get all the
> datafiles through rsync (or other similar manner) from the new
> primary ?
The old server will probably be close enough that judicious use of
rsync with the old copy as the target will allow a base backup to be
taken very quickly.
-Kevin
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Switchover of Master and Slave roles
2011-06-07 16:32 Switchover of Master and Slave roles A J <s5aly@yahoo.com>
2011-06-07 18:12 ` Re: Switchover of Master and Slave roles Ray Stell <stellr@cns.vt.edu>
2011-06-07 18:33 ` Re: Switchover of Master and Slave roles A J <s5aly@yahoo.com>
2011-06-07 18:44 ` Re: Switchover of Master and Slave roles Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2011-06-07 18:59 ` A J <s5aly@yahoo.com>
2011-06-07 19:11 ` Re: Switchover of Master and Slave roles Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-06-07 19:41 ` Re: Switchover of Master and Slave roles Ray Stell <stellr@cns.vt.edu>
2011-06-08 01:51 ` Re: Switchover of Master and Slave roles Fujii Masao <masao.fujii@gmail.com>
0 siblings, 3 replies; 11+ messages in thread
From: A J @ 2011-06-07 18:59 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; Ray Stell <stellr@cns.vt.edu>; +Cc: pgsql-admin
Ok. So if I understand it correctly, as far as Postgres is concerned the 'mirror
is broken'. It is a one-time cutover.
We then rely on filesystem tools (or other third party tools) to get the
original master in sync with the new master efficiently and then make it join as
slave.
Right ?
________________________________
From: Kevin Grittner <Kevin.Grittner@wicourts.gov>
To: Ray Stell <stellr@cns.vt.edu>; A J <s5aly@yahoo.com>
Cc: pgsql-admin@postgresql.org
Sent: Tue, June 7, 2011 2:44:41 PM
Subject: Re: [ADMIN] Switchover of Master and Slave roles
A J <s5aly@yahoo.com> wrote:
> What does it exactly mean to 'recreate a standby server' ? Can I
> not use the datafiles on the former primary and just let it sync
> and get the incremental from the new primary ? Do I have to remove
> all the data files from the former primary and get all the
> datafiles through rsync (or other similar manner) from the new
> primary ?
The old server will probably be close enough that judicious use of
rsync with the old copy as the target will allow a base backup to be
taken very quickly.
-Kevin
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Switchover of Master and Slave roles
2011-06-07 16:32 Switchover of Master and Slave roles A J <s5aly@yahoo.com>
2011-06-07 18:12 ` Re: Switchover of Master and Slave roles Ray Stell <stellr@cns.vt.edu>
2011-06-07 18:33 ` Re: Switchover of Master and Slave roles A J <s5aly@yahoo.com>
2011-06-07 18:44 ` Re: Switchover of Master and Slave roles Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-06-07 18:59 ` Re: Switchover of Master and Slave roles A J <s5aly@yahoo.com>
@ 2011-06-07 19:11 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2 siblings, 0 replies; 11+ messages in thread
From: Kevin Grittner @ 2011-06-07 19:11 UTC (permalink / raw)
To: Ray Stell <stellr@cns.vt.edu>; A J <s5aly@yahoo.com>; +Cc: pgsql-admin
A J <s5aly@yahoo.com> wrote:
> Ok. So if I understand it correctly, as far as Postgres is
> concerned the 'mirror is broken'. It is a one-time cutover.
> We then rely on filesystem tools (or other third party tools) to
> get the original master in sync with the new master efficiently
> and then make it join as slave. Right ?
Right. There's been talk of building in something, but it would
take a lot of work to get something which would be as fast as rsync.
So far nobody has stepped up offering to re-invent that wheel.
-Kevin
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Switchover of Master and Slave roles
2011-06-07 16:32 Switchover of Master and Slave roles A J <s5aly@yahoo.com>
2011-06-07 18:12 ` Re: Switchover of Master and Slave roles Ray Stell <stellr@cns.vt.edu>
2011-06-07 18:33 ` Re: Switchover of Master and Slave roles A J <s5aly@yahoo.com>
2011-06-07 18:44 ` Re: Switchover of Master and Slave roles Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-06-07 18:59 ` Re: Switchover of Master and Slave roles A J <s5aly@yahoo.com>
@ 2011-06-07 19:41 ` Ray Stell <stellr@cns.vt.edu>
2011-06-07 19:46 ` Re: Switchover of Master and Slave roles Simon Riggs <simon@2ndQuadrant.com>
2 siblings, 1 reply; 11+ messages in thread
From: Ray Stell @ 2011-06-07 19:41 UTC (permalink / raw)
To: A J <s5aly@yahoo.com>; +Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>; pgsql-admin
On Tue, Jun 07, 2011 at 11:59:07AM -0700, A J wrote:
> We then rely on filesystem tools (or other third party tools) to get the
> original master in sync with the new master efficiently and then make it join as
> slave.
the doc would not be corrupted by an additional few words that stated as such:
"To return to normal operation, a standby server must be recreated using
external tools such as rsync, either on the former primary system when
it comes up, or on a third, possibly new, system."
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Switchover of Master and Slave roles
2011-06-07 16:32 Switchover of Master and Slave roles A J <s5aly@yahoo.com>
2011-06-07 18:12 ` Re: Switchover of Master and Slave roles Ray Stell <stellr@cns.vt.edu>
2011-06-07 18:33 ` Re: Switchover of Master and Slave roles A J <s5aly@yahoo.com>
2011-06-07 18:44 ` Re: Switchover of Master and Slave roles Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-06-07 18:59 ` Re: Switchover of Master and Slave roles A J <s5aly@yahoo.com>
2011-06-07 19:41 ` Re: Switchover of Master and Slave roles Ray Stell <stellr@cns.vt.edu>
@ 2011-06-07 19:46 ` Simon Riggs <simon@2ndQuadrant.com>
2011-06-08 00:53 ` Re: Switchover of Master and Slave roles Jerry Sievers <gsievers19@comcast.net>
0 siblings, 1 reply; 11+ messages in thread
From: Simon Riggs @ 2011-06-07 19:46 UTC (permalink / raw)
To: Ray Stell <stellr@cns.vt.edu>; +Cc: A J <s5aly@yahoo.com>; Kevin Grittner <Kevin.Grittner@wicourts.gov>; pgsql-admin
On Tue, Jun 7, 2011 at 8:41 PM, Ray Stell <stellr@cns.vt.edu> wrote:
> On Tue, Jun 07, 2011 at 11:59:07AM -0700, A J wrote:
>> We then rely on filesystem tools (or other third party tools) to get the
>> original master in sync with the new master efficiently and then make it join as
>> slave.
>
> the doc would not be corrupted by an additional few words that stated as such:
>
> "To return to normal operation, a standby server must be recreated using
> external tools such as rsync, either on the former primary system when
> it comes up, or on a third, possibly new, system."
You need repmgr: http://www.repmgr.org/
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Switchover of Master and Slave roles
2011-06-07 16:32 Switchover of Master and Slave roles A J <s5aly@yahoo.com>
2011-06-07 18:12 ` Re: Switchover of Master and Slave roles Ray Stell <stellr@cns.vt.edu>
2011-06-07 18:33 ` Re: Switchover of Master and Slave roles A J <s5aly@yahoo.com>
2011-06-07 18:44 ` Re: Switchover of Master and Slave roles Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-06-07 18:59 ` Re: Switchover of Master and Slave roles A J <s5aly@yahoo.com>
2011-06-07 19:41 ` Re: Switchover of Master and Slave roles Ray Stell <stellr@cns.vt.edu>
2011-06-07 19:46 ` Re: Switchover of Master and Slave roles Simon Riggs <simon@2ndQuadrant.com>
@ 2011-06-08 00:53 ` Jerry Sievers <gsievers19@comcast.net>
0 siblings, 0 replies; 11+ messages in thread
From: Jerry Sievers @ 2011-06-08 00:53 UTC (permalink / raw)
To: Simon Riggs <simon@2ndQuadrant.com>; +Cc: Ray Stell <stellr@cns.vt.edu>; A J <s5aly@yahoo.com>; Kevin Grittner <Kevin.Grittner@wicourts.gov>; pgsql-admin
Simon Riggs <simon@2ndQuadrant.com> writes:
> On Tue, Jun 7, 2011 at 8:41 PM, Ray Stell <stellr@cns.vt.edu> wrote:
>
>> On Tue, Jun 07, 2011 at 11:59:07AM -0700, A J wrote:
>>> We then rely on filesystem tools (or other third party tools) to get the
>>> original master in sync with the new master efficiently and then make it join as
>>> slave.
>>
>> the doc would not be corrupted by an additional few words that stated as such:
>>
>> "To return to normal operation, a standby server must be recreated using
>> external tools such as rsync, either on the former primary system when
>> it comes up, or on a third, possibly new, system."
>
> You need repmgr: http://www.repmgr.org/
Well, I am not sure on the 9.x versions but, we used to do reversible
master/standby with log shipping on the 8.x versions quite routinely,
sometimes reshaping clusters of multiple standbys., designating a new
master, restarting the old master in recovery mode and repointing the
remaining standbys..
The procedure was similar to...
apps down and locked out
pg_rotate_xlog() on master
verify final log received on standbys and consumed
bring up new master
kill immediate on old master and remaining standbys
configure old master as standby w/recovery.conf and point to new xlog
repository
repoint other standbys to new repository and restart
There was no need to reinitialize from scratch or rsync the old master
or other standbys.
Somewhere between 8.2 and 8.4 there was a change that would require
you to increment the recovery target timeline during this operation.
It took me a bit of head scratching to get it right.
This capability was apparently little known and used, perhaps due to
uncertainty that it was viable, given the undocumented nature of
this.
Disclaimer: I do not know if this still works on 9.x using either log
shipping or streaming replication.
I covered this strategy briefly during my talk at the 2010 Pg-East
conference in Philly.
YMMV
> --
> Simon Riggs http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Training & Services
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>
--
Jerry Sievers
Postgres DBA/Development Consulting
e: gsievers19@comcast.net
p: 305.321.1144
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Switchover of Master and Slave roles
2011-06-07 16:32 Switchover of Master and Slave roles A J <s5aly@yahoo.com>
2011-06-07 18:12 ` Re: Switchover of Master and Slave roles Ray Stell <stellr@cns.vt.edu>
2011-06-07 18:33 ` Re: Switchover of Master and Slave roles A J <s5aly@yahoo.com>
2011-06-07 18:44 ` Re: Switchover of Master and Slave roles Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-06-07 18:59 ` Re: Switchover of Master and Slave roles A J <s5aly@yahoo.com>
@ 2011-06-08 01:51 ` Fujii Masao <masao.fujii@gmail.com>
2 siblings, 0 replies; 11+ messages in thread
From: Fujii Masao @ 2011-06-08 01:51 UTC (permalink / raw)
To: A J <s5aly@yahoo.com>; +Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>; Ray Stell <stellr@cns.vt.edu>; pgsql-admin
On Wed, Jun 8, 2011 at 3:59 AM, A J <s5aly@yahoo.com> wrote:
> Ok. So if I understand it correctly, as far as Postgres is concerned the
> 'mirror is broken'. It is a one-time cutover.
> We then rely on filesystem tools (or other third party tools) to get the
> original master in sync with the new master efficiently and then make it
> join as slave.
If the standby is completely sync with the master when failover happens,
and if those two servers share the archive area, you can start new standby
without taking a fresh base backup. You can do that by setting
recovery_target_timeline to 'latest' in the recovery.conf and just starting
new standby.
Regards,
--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2011-06-08 01:51 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-06-07 16:32 Switchover of Master and Slave roles A J <s5aly@yahoo.com>
2011-06-07 18:12 ` Ray Stell <stellr@cns.vt.edu>
2011-06-07 18:33 ` A J <s5aly@yahoo.com>
2011-06-07 18:40 ` Guillaume Lelarge <guillaume@lelarge.info>
2011-06-07 18:44 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-06-07 18:59 ` A J <s5aly@yahoo.com>
2011-06-07 19:11 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-06-07 19:41 ` Ray Stell <stellr@cns.vt.edu>
2011-06-07 19:46 ` Simon Riggs <simon@2ndQuadrant.com>
2011-06-08 00:53 ` Jerry Sievers <gsievers19@comcast.net>
2011-06-08 01:51 ` Fujii Masao <masao.fujii@gmail.com>
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