Download as PDF, PPTX
![17 Copyright © 2013 NTT DATA Corporation Monitoring =# SELECT * FROM pg_stat_replication; -[ RECORD 1 ]----+------------------------------ procpid | 26531 usesysid | 10 usename | postgres application_name | tokyo client_addr | 192.168.1.2 client_hostname | client_port | 39654 backend_start | 2012-02-01 18:54:49.429459+09 state | streaming sent_location | 0/406E7CC write_location | 0/406E7CC flush_location | 0/406E7CC replay_location | 0/406E1B0 sync_priority | 1 sync_state | sync Replication progress How far has master sent WAL? How far has standby written, flushed or replayed WAL? Replication connection information Standby IP address, Port number, ROLE for replication、 Replication start time, etc Replication status Current synchronous mode, Standby has already caught up with master?](https://image.slidesharecdn.com/postgresqlreplication-141001030645-phpapp02/75/PostgreSQL-replication-17-2048.jpg)
PostgreSQL replication
- 1. Copyright © 2013NTT DATA Corporation 27 April 2013 Fujii Masao (twitter: @fujii_masao) PostgreSQL replication Inaugural Indian PostgreSQL User Group meetup!
- 2. 2 Copyright ©2013NTT DATA Corporation Who am I ? Database engineer in NTT DATA •NTT DATA is global IT service and consulting company PostgreSQL developer since 2008 One of main authors of PostgreSQL replication
- 3. 3 Copyright ©2013NTT DATA Corporation Index What's replication? History PostgreSQL replication 1. Features 2. Synchronous vs. Asynchronous
- 4. Copyright © 2013NTT DATA Corporation 4 What's replication?
- 5. Copyright © 2013NTT DATA Corporation 5 What's replication? Client Client Change Change DB servers Change Replicate DB servers Change Proxy server Create a replica of the database on multiple servers
- 6. Copyright © 2013NTT DATA Corporation 6 Why is replication required? Replication is an essential feature to get the system to work properly 24/7! High Availability Load Balancing If one server crashes, another can keep providing the service We can easily reduce the downtime of the system The load of query execution can be distributed to multiple servers We can improve the performance of whole system Client Client SQL SQL SQL High Availability Load Balancing DB servers DB servers
- 7. Copyright © 2013NTT DATA Corporation 7 History
- 8. Copyright © 2013NTT DATA Corporation 8 History of PostgreSQL replication 2007 2008 2009 2010 2012 2011 9.0(Sep 2010 release) • Async reploication 9.1(Sep 2011 release) • Synchronous replication • Improve replication monitoring • pg_basebackup 9.2(Sep 2012 release) • Cascade replication • Backup from standby • New sync rep mode Slony-I Bucardo Londiste Sequoia PGCluster PostgresForest Postgres-R Mammoth PL/Proxy pgpool-II rubyrep Postgres-XC GridSQL syncreplicator Replication war ! Historically the community policy has been to avoid putting replication into core. But, because of large user needs, replication has been introduced in 9.0. Then, replication keeps evolving steady.
- 9. Copyright © 2013NTT DATA Corporation 9 Features
- 10. Copyright © 2013NTT DATA Corporation 10 Single master / Multiple standbys Single master Multiple standbys Multiple standbys Replicate Replicate Write SQL Read SQL Read SQL Read SQL Replication from single master to multiple standbys Cascade replication Only master accepts write query, both accepts read query Read scalable, not write scalable
- 11. 11 Copyright ©2013 NTT DATA Corporation Read-only query on standby Online backup –(Logical) pg_dump –(Physical) pg_basebackup Maintenance command –VACUUM, ANALYZE ※No need to do VACUUM and ANALYZE on standby because the result of mantenance execution on master is automatically replicated to standby Allow Query access –SELECT –PREPARE, EXECUTE –CURSOR operation Disallow DML –INSERT, UPDATE, DELETE –SELECT FOR UPDATE DDL –CREATE, DROP, ALTER Temporary table
- 12. Copyright © 2013NTT DATA Corporation 12 Log-shipping Transaction log (WAL) is shipped from master to standby Standby is in recovery mode Standby keeps the database current by replaying shipped WAL Master Standby Recovery Write SQL Client WAL writing WAL shipping WAL writing
- 13. 13 Copyright ©2013 NTT DATA Corporation Limitation by log-shipping The followings must be the same between master and standby H/W and OS architecture PostgreSQL major version Slony-I can be used for replication between different architectures and major versions Master Standby 64bit OS PostgreSQL9.2.1 PostgreSQL9.1.0 32bit OS 64bit OS PostgreSQL9.2.0 NG NG OK 2 1
- 14. 14 Copyright ©2013 NTT DATA Corporation All database objects are replicated Per-table granularity is not allowed Slony-I supports per-table granularity replication Per database cluster granularity Per database cluster Per table Master Standby Master Standby
- 15. Copyright © 2013NTT DATA Corporation 15 SQL distribution PostgreSQL doesn't provide SQL distribution feature Implement SQL distribution logic into client application Use SQL distributor like pgpool-II Client Client Write SQL Read SQL マスタ Write SQL Write/Read SQL Distributor スタンバイ Read SQL Master Standby Implement logic Use SQL distributor
- 16. Copyright © 2013NTT DATA Corporation 16 Failover PostgreSQL doesn't provide automatic failover feature Standby can be promoted to master anytime(pg_ctl promote) Automatic error detection and faliover requires clusterware Client Client Master pgpool-II Master スタンバイ Standby Pacemaker pgpool-II VIP Pacemaker supports the resource agent for HA cluster using PostgreSQL replication!
- 17. 17 Copyright ©2013 NTT DATA Corporation Monitoring =# SELECT * FROM pg_stat_replication; -[ RECORD 1 ]----+------------------------------ procpid | 26531 usesysid | 10 usename | postgres application_name | tokyo client_addr | 192.168.1.2 client_hostname | client_port | 39654 backend_start | 2012-02-01 18:54:49.429459+09 state | streaming sent_location | 0/406E7CC write_location | 0/406E7CC flush_location | 0/406E7CC replay_location | 0/406E1B0 sync_priority | 1 sync_state | sync Replication progress How far has master sent WAL? How far has standby written, flushed or replayed WAL? Replication connection information Standby IP address, Port number, ROLE for replication、 Replication start time, etc Replication status Current synchronous mode, Standby has already caught up with master?
- 18. Copyright © 2013NTT DATA Corporation 18 Synchronous vs. Asynchronous
- 19. 19 Copyright ©2013 NTT DATA Corporation Replication mode Replication mode is configurable Asynchronous Synchronous
- 20. Copyright © 2013NTT DATA Corporation 20 Asynchronous replication COMMIT doesn't wait for the completion of replication No guarantee that WAL has already arrived at standby at end of COMMIT Data loss window on failover Query on standby may see outdated data Low performance impact on master Master Standby Recovery COMMIT Client WAL writing WAL shipping WAL writing OK 1 2 3 4 5 6 Committed data gets lost if failover happens between and operations 3 4
- 21. Copyright © 2013NTT DATA Corporation 21 Synchronous replication COMMIT waits for completion of replication WAL is guaranteed to be flushed in master and standby at end of COMMIT No data loss on failover! Relatively high performance impact on master Query on standby may see outdated data 「Synchronous ≠ Committed data is available on standby immediately」 Recovery Master Standby COMMIT Client WAL writing WAL shipping WAL writing OK 1 2 6 3 4 7 5 Reply WAL shipping is synchronous, but recovery is asynchronous
- 22. Copyright © 2011NTT DATA Corporation Copyright © 2013 NTT DATA Corporation (Please omit notations when unnecessary) This document contains confidential Company information. Do not disclose it to third parties without permission from the Company.