Re: Safe to gracefully switch 9.2 streaming replication roles multiple times ?

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

> On 8/07/2014, at 3:43 pm, "David Morton" <david(dot)morton(at)eroad(dot)com> wrote:
>
> Situation & background:
> - Want to cater for rollback of database (9.2) migration by gracefully alternating master / slave roles.
> - Asynchronous streaming replication is setup prior to the below migration 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 migration 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
>
> 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 ...
> 9) Rollback (of database location, not data) determined to be required here
> 10) Repeat above procedure gracefully stopping / starting and manipulating recovery files
>
> 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 slave 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 switchovers ?
>
> General comments ?
>
> Many thanks !!
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin