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 !!
