postgresql9.3 switchover/switchback
DESCRIPTION
This presentation is about feature included in PostgreSQL 9.3 for switchover and switchbackTRANSCRIPT
© 2013 EnterpriseDB Corporation - All rights reserved. 1
Streaming ReplicationSwitchover/SwitchbackVibhor Kumar
Principal System Engineer.
© 2013 EnterpriseDB Corporation - All rights reserved. 2
• PostgreSQL Streaming Replication
• Limitations in PG Version < 9.3
• Understanding Timelines
• New in PostgreSQL >=9.3
• Possible switchover/switchback
• Things to know about switchover/switchback
• recovery.conf
Agenda
© 2013 EnterpriseDB Corporation - All rights reserved. 3
PostgreSQL Streaming Replication (SR)
• Streaming Replication (SR) provides capability to continuously ship and apply WAL/XLOG records to any number of standbys.
• Introduced in PostgreSQL 9.0
• Currently two types supported:− Synchronous SR− Asynchronous SR
© 2013 EnterpriseDB Corporation - All rights reserved. 4
History: Streaming Replication
• 9.0: Asynchronous SR
• 9.1: − Synchronous SR− pg_basebackup
• 9.2:− Cascaded Replication− Backup from Standby− New Sync replication mode. (remote_write, local write)
• 9.3− Streaming only replication.− Follow timeline changes.
© 2013 EnterpriseDB Corporation - All rights reserved. 5
Limitations before 9.3
• Timeline streaming issue
• Intentional shutdown on master can leave Standby behind the master before 9.1.
• If Standby is behind master:− Promote lead to new timeline.− Difference in Master and Standby.− Old Master can’t be standby of new master.− Need to take fresh backup or manual copy timeline file to old master.
• Painful to take fresh backup if database size is large.
© 2013 EnterpriseDB Corporation - All rights reserved. 6
Understanding Timelines
• Introduced with Point-in-Time-Recovery in version 8.0
• Every time you do PITR, a new timeline is formed
• Timelines helps to differentiate WAL generated from different PITR.
• When new WAL generated after PITR, PostgreSQL shouldn’t overwrite old WAL.
© 2013 EnterpriseDB Corporation - All rights reserved. 7
More about Timelines
© 2013 EnterpriseDB Corporation - All rights reserved. 8
More about Timelines
© 2013 EnterpriseDB Corporation - All rights reserved. 9
Timeline history file
© 2013 EnterpriseDB Corporation - All rights reserved. 10
New in 9.3 SR
• Important Patches:− commit 985bd7d49726c9f178558491d31a570d47340459
− Author: Fujii Masao− Committed on Wed Jun 26 02:14:37 2013 +0900− Make sures when we shutdown the master, walsender should tries to send all outstandng WAL records to Standby, and then to exit.
− Back patched till 9.1− commit abfd192b1b5ba5216ac4b1f31dcd553106304b19
− Author: Heikki Linnakangas− Committed on Thu Dec 13 19:00:00 2012 +0200− Allow a streaming replication standby to follow a timeline switch. (Only in 9.3)
© 2013 EnterpriseDB Corporation - All rights reserved. 11
9.3: Now switchover/Switchback is possible
• Difference between Switchover and Failover. (add points)
• New commit in PostgreSQL helps:
− Clean shutdown for maintenance operation of master server
− Old master server can be new standby for New Master.
− No more fresh backup in intentional promotion.
© 2013 EnterpriseDB Corporation - All rights reserved. 12
Things to know: switchover/switchback
• Use –m fast or –m smart shutdown on Master for clean shutdown.
• Before promoting standby:− Make sure all WAL sent by Master applied on elected node for New Master.
− Use following function for when to promote:− pg_last_xlog_receive_location− pg_last_xlog_replay_location
− Have proper archive location accessible to old Master.
− Set proper archive_command
© 2013 EnterpriseDB Corporation - All rights reserved. 13
recovery.conf
recovery_target_timeline = 'latest'standby_mode = 'on’primary_conninfo = 'user=repuser password=repuser host=172.17.0.3 port=5432restore_command = ‘cp /location/%f %p’
© 2013 EnterpriseDB Corporation - All rights reserved. 14
EFM: Promote Steps
• When promote happens, EFM does following:− Send prepare information to Master, Witness and Slave
− Agent on Master get promote signal and does following:− Release VIP if any.− create dummy recovery.conf, so that next startup master shouldn’t come up.
− Message about Master failed.
− Standby signaled to be promoted by trigger.
• Explore EFM for possible switchover/switchback
© 2013 EnterpriseDB Corporation - All rights reserved. 15
Demo
© 2013 EnterpriseDB Corporation - All rights reserved. 16
Thanks