From: David Morton <david.morton@eroad.com>
To: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Subject: Re: Safe to gracefully switch 9.2 streaming replication roles
 multiple times ?
Thread-Topic: [ADMIN] Safe to gracefully switch 9.2 streaming replication
 roles multiple times ?
Thread-Index: AQHPm17XzficNItUUUK7rBjHDElMTQ==
Date: Wed, 9 Jul 2014 10:16:26 +0000
Message-ID: <82332E02-CCD2-406D-9B68-6E57AEC206B8@eroad.com>
References: 
 <b1fd3c1977e043998a3aceb7023231d6@HKXPR01MB247.apcprd01.prod.exchangelabs.com>
In-Reply-To: 
 <b1fd3c1977e043998a3aceb7023231d6@HKXPR01MB247.apcprd01.prod.exchangelabs.com>
Accept-Language: en-NZ, en-US
Content-Language: en-US
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

Still looking for a bit of commentary about this one ...=20

> On 8/07/2014, at 3:43 pm, "David Morton" <david.morton@eroad.com> wrote:
>=20
> Situation & background:
> - Want to cater for rollback of database (9.2) migration by gracefully al=
ternating master / slave roles.
> - Asynchronous streaming replication is setup prior to the below migratio=
n steps taking place.
> - To avoid switching timelines we are purposefully not using trigger file=
 or pg_ctl promotion mechanisms.
> - We are changing tablespace locations / symbolic links during this migra=
tion so base rsync's are best avoided if possible. The dataset is also very=
 large so not ideal from a time perspective.
> - Have performed the below in a sandbox environment and all 'appears' to =
work just fine.
> - WAL Archiving is used at both old and new locations but does NOT share =
a common repository
>=20
> Proposed migration and potential rollback steps:
> 1) Prohibit connections / data change
> 2) Stop master
> 3) Stop slave
> 4) Remove recovery.conf on slave (new master)
> 5) Put in place recovery.conf on master (new slave)
> 6) Start new master (old slave)
> 7) Start new slave (old master)
> 8) Allow connections / data change on new master here ...=20
> 9) Rollback (of database location, not data) determined to be required he=
re
> 10) Repeat above procedure gracefully stopping / starting and manipulatin=
g recovery files
>=20
> Questions:
> 1) Is the above a reasonable approach to the problem in general ?
> 2) Are there any risks with graceful / ordered switching of master and sl=
ave roles ?
> 3) If the above is not recommended ... why ?
> 4) If base data MUST be synced to safely recreate slave roles .... why ?
> 5) Should we force a checkpoint or pg_rotate_xlog() prior to the switchov=
ers ?
>=20
> General comments ?
>=20
> Many thanks !!
>=20
>=20
> --=20
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin


--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin