PostgreSQL Replication Solutions BRUCE MOMJIAN Replication is a complex feature. POSTGRESQL supports a variety of replication options. Creative Commons Attribution License http://momjian.us/presentations Last updated: April, 2017 1 / 18 Uses for Replication https://www.flickr.com/photos/eugenius/ 2 / 18 Fail Over 111111 000000 0 1 0 1 000000 111111 0 1 0 1 000000 111111 0 1 0 0 1 1 0 1 0 1 0 1 000000 111111 0 1 0 1 000000 111111 0 1 0 1 000000 111111 0 1 0 1 000000 111111 0 1 0 1 0 1 0 1 0 1 0 0 1 1 0 1 0 1 0 1 00000 11111 0 1 0 1 00000 11111 0 1 0 1 00000 11111 0 1 0 1 00000 11111 0 1 0 1 00000 11111 0 1 0 1 00000 11111 0 1 0 00000 11111 0 1 1 0 1 00000 11111 0 1 0 1 11111 00000 0 1 0 1 00000 11111 0 1 0 1 00000 11111 0 1 0 0 1 1 0 1 0 1 0 1 000000 111111 0 1 0 1 000000 111111 0 1 0 1 000000 111111 0 1 0 1 000000 111111 0 1 0 1 0 1 0 1 0 1 0 0 1 1 0 1 0 1 0 1 00000 11111 0 1 0 1 00000 11111 0 1 0 1 00000 11111 0 1 0 1 00000 11111 0 1 0 1 00000 11111 0 1 0 1 00000 11111 0 1 0 00000 11111 0 1 1 0 1 00000 11111 0 1 0 1 3 / 18 Data Warehousing 0 1 0 111111 000000 0 1 0 1 1 000000 111111 0 0 1 1 000000 111111 0 0 1 1 0 1 0 1 0 0 1 1 0 0 1 000000 111111 0 1 0 1 1 000000 111111 0 1 0 1 000000 111111 0 0 1 1 000000 111111 0 0 1 1 0 0 1 1 0 1 0 0 1 0 1 1 00000 11111 0 0 1 1 00000 11111 0 0 1 1 00000 11111 0 0 1 1 00000 11111 0 1 0 00000 11111 0 1 0 1 1 00000 11111 0 0 1 1 00000 11111 0 0 1 1 00000 11111 0 0 1 1 00000 11111 0 1 0 11111 00000 0 1 0 1 1 00000 11111 0 0 1 1 00000 11111 0 0 1 1 0 1 0 1 0 0 1 1 0 0 1 000000 111111 0 1 0 1 1 000000 111111 0 1 0 1 000000 111111 0 0 1 1 000000 111111 0 0 1 1 0 0 1 1 0 1 0 0 1 0 1 1 00000 11111 0 0 1 1 00000 11111 0 0 1 1 00000 11111 0 0 1 1 00000 11111 0 1 0 00000 11111 0 1 0 1 1 00000 11111 0 0 1 1 00000 11111 0 0 1 1 00000 11111 0 0 1 1 00000 11111 4 / 18 Load Balancing 111111 000000 0 1 0 1 000000 111111 0 1 0 1 000000 111111 0 1 0 1 0 0 1 1 0 0 1 000000 111111 0 1 1 0 1 000000 111111 0 0 1 1 000000 111111 0 0 1 1 000000 111111 0 1 0 1 0 1 0 1 0 1 0 0 1 0 1 1 0 0 1 1 00000 11111 0 1 0 1 00000 11111 0 0 1 1 00000 11111 0 0 1 1 00000 11111 0 1 0 1 00000 11111 0 0 1 00000 11111 0 1 1 0 1 00000 11111 0 0 1 00000 11111 0 1 0 1 1 11111 00000 0 1 0 1 00000 11111 0 1 0 1 00000 11111 0 1 0 1 0 0 1 1 0 0 1 000000 111111 0 1 1 0 1 000000 111111 0 0 1 1 000000 111111 0 0 1 1 000000 111111 0 1 0 1 0 1 0 1 0 1 0 0 1 0 1 1 0 0 1 1 00000 11111 0 1 0 1 00000 11111 0 0 1 1 00000 11111 0 0 1 1 00000 11111 0 1 0 1 00000 11111 0 0 1 00000 11111 0 1 1 0 1 00000 11111 0 0 1 00000 11111 0 1 0 1 1 5 / 18 Remote Servers 0 1 0 1 11111 00000 1 1 0 1 0 00000 11111 1 0 0 00000 11111 1 1 0 0 0 1 0 1 1 1 0 000000 111111 1 1 0 0 0 000000 111111 1 1 0 0 000000 111111 0 1 0 1 000000 111111 1 1 0 1 0 1 0 0 1 1 0 1 0 0 1 0 0 1 0 1 00000 11111 1 1 0 0 00000 11111 1 1 0 0 00000 11111 1 1 0 1 0 00000 11111 0 1 0 00000 11111 0 1 0 1 00000 11111 1 1 0 0 00000 11111 1 1 0 0 00000 11111 1 0 1 0 0 1 0 1 111111 000000 1 1 0 1 0 000000 111111 1 0 0 000000 111111 1 1 0 0 0 1 0 1 1 1 0 000000 111111 1 1 0 0 0 000000 111111 1 1 0 0 000000 111111 0 1 0 1 000000 111111 1 1 0 1 0 1 0 0 1 1 0 1 0 0 1 0 0 1 0 1 00000 11111 1 1 0 0 00000 11111 1 1 0 0 00000 11111 1 1 0 1 0 00000 11111 0 1 0 00000 11111 0 1 0 1 00000 11111 1 1 0 0 00000 11111 1 1 0 0 00000 11111 1 0 1 0 6 / 18 Mobile Servers 11111 00000 0 0 1 1 00000 11111 0 1 0 1 00000 11111 0 0 1 1 0 1 0 1 0 0 1 1 000000 111111 0 1 0 1 000000 111111 0 0 1 1 000000 111111 0 0 1 1 000000 111111 0 1 0 1 0 0 1 0 1 0 1 1 0 1 0 1 0 1 0 1 00000 11111 0 1 0 1 00000 11111 0 0 1 1 00000 11111 0 0 1 1 00000 11111 0 1 0 1 00000 11111 0 0 1 1 00000 11111 0 1 0 1 00000 11111 0 0 1 00000 11111 0 1 0 1 1 7 / 18 Replication Solutions https://www.flickr.com/photos/paulbence/ 8 / 18 Shared Storage 0 1 0 1 0000 1111 0 0 1 0000 1111 01 0 1 1 1111 0000 0 1 0 1 0000 1111 0 0 1 1 0000 1111 0 0 1 1 0000 1111 0 1 0 1 0 1 0 1 0 1 0 00000 11111 01 0 1 1 00000 11111 0 0 1 1 00000 11111 0 1 0 00000 11111 01 0 1 00000 11111 01 1 0 1 00000 11111 ◮ No overhead ◮ No data loss on fail-over ◮ Slave cannot execute queries 0 1 0 1 0000 1111 0 0 1 0000 1111 01 0 1 1 1111 0000 0 1 0 1 0000 1111 0 0 1 1 0000 1111 0 0 1 1 0000 1111 0 1 0 1 0 1 0 1 0 1 0 00000 11111 01 0 1 1 00000 11111 0 0 1 1 00000 11111 0 1 0 00000 11111 01 0 1 00000 11111 01 1 0 1 00000 11111 NAS or SAN 9 / 18 Storage Mirroring 0 1 0 1 0000 1111 0 0 1 1 0000 1111 0 0 1 1 1111 0000 0 1 0 1 0000 1111 0 0 1 1 0000 1111 0 0 1 1 0000 1111 0 1 0 1 0 1 0 1 0 1 0 1 00000 11111 0 0 1 1 00000 11111 0 0 1 1 00000 11111 0 1 0 1 00000 11111 0 0 1 1 00000 11111 0 1 0 1 00000 11111 0 1 0000 1111 0 1 0000 1111 0 1 1111 0000 0 1 0000 1111 0 1 0000 1111 0 1 0000 1111 0 1 0 1 0 1 00000 11111 0 1 00000 11111 0 1 00000 11111 0 1 00000 11111 0 1 00000 11111 0 1 00000 11111 ◮ No overhead on master ◮ Synchronous or asynchronous ◮ Possible data loss on fail-over when using asynchronous ◮ Slave cannot execute queries DRBD 10 / 18 Streaming Replication 0 1 0 1 0000 1111 0 1 0 1 0000 1111 0 1 0 1 1111 0000 0 1 0 1 0000 1111 0 1 0 1 0000 1111 0 1 0 1 0000 1111 0 1 0 1 0 1 0 1 0 1 0 1 00000 11111 0 1 0 1 00000 11111 0 1 0 1 00000 11111 0 1 0 1 00000 11111 0 1 0 1 00000 11111 0 1 0 1 00000 11111 Write Ahead Log (WAL) 0 1 0000 1111 0 1 0000 1111 0 1 1111 0000 0 1 0000 1111 0 1 0000 1111 0 1 0000 1111 0 1 0 1 0 1 00000 11111 0 1 00000 11111 0 1 00000 11111 0 1 00000 11111 0 1 00000 11111 0 1 00000 11111 ◮ No overhead on master ◮ Slaves can execute queries ◮ Possible data loss on fail-over when using asynchronous mode ◮ Synchronous option available (Postgres 9.1) 11 / 18 Slony 0 1 0 1 00000 11111 0 1 0 11111 00000 0 1 1 0 1 00000 11111 0 1 0 0 1 1 0 1 0 1 0 1 0 1 0 000000 111111 0 1 1 0 1 000000 111111 0 1 0 1 000000 111111 0 1 0 000000 111111 0 1 1 0 1 0 1 0 0 1 1 0 1 0 1 0 1 00000 11111 0 1 0 00000 11111 0 1 1 0 1 00000 11111 0 1 0 1 00000 11111 0 1 0 00000 11111 0 1 1 0 1 00000 11111 0 1 0 00000 11111 0 1 1 0 1 00000 11111 0 1 0 1 00000 11111 Asynchronous 0 1 0 1 000000 111111 0 1 0 111111 000000 0 1 1 0 1 000000 111111 0 1 0 0 1 1 0 1 0 1 0 1 0 1 0 000000 111111 0 1 1 0 1 000000 111111 0 1 0 1 000000 111111 0 1 0 000000 111111 0 1 1 0 1 0 1 0 0 1 1 0 1 0 1 0 1 00000 11111 0 1 0 00000 11111 0 1 1 0 1 00000 11111 0 1 0 1 00000 11111 0 1 0 00000 11111 0 1 1 0 1 00000 11111 0 1 0 00000 11111 0 1 1 0 1 00000 11111 0 1 0 1 00000 11111 ◮ Triggers add overhead to the master ◮ Possible data loss on fail-over ◮ Replication possible even over slow links ◮ Slave can execute read-only queries ◮ Table-level granularity allows complex data partitioning configurations 12 / 18 Slony Internals User 111111111 000000000 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 111111111 000000000 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 User Origin 11111111 00000000 000 111 00000000 11111111 000 111 00000000 11111111 000 111 00000000 11111111 000 111 00000000 11111111 000 111 00000000 11111111 000 111 00000000 11111111 000 111 00000000 11111111 000 111 00000000 11111111 000 111 00000000 11111111 000 111 00000000 11111111 000 111 00000000 11111111 000 111 00000000 11111111 000 111 00000000 11111111 000 111 00000000 11111111 000 111 00000000 11111111 000 111 sl_log 111111111 000000000 000000000 111111111 000000000 111111111 000000000 111111111 000000000 111111111 000000000 111111111 000000000 111111111 000000000 111111111 000000000 111111111 000000000 111111111 000000000 111111111 000000000 111111111 000000000 111111111 000000000 111111111 00000000 11111111 000 111 11111111 00000000 000 111 00000000 11111111 000 111 00000000 11111111 000 111 00000000 11111111 000 111 00000000 11111111 000 111 00000000 11111111 000 111 00000000 11111111 000 111 CREATE TRIGGER fills sl_log Subscriber sl_log 111111111 000000000 000000000 111111111 000000000 111111111 000000000 111111111 000000000 111111111 000000000 111111111 000000000 111111111 000000000 111111111 000000000 111111111 000000000 111111111 000000000 111111111 000000000 111111111 000000000 111111111 000000000 111111111 Other Subscribers CREATE TRIGGER prevents modifications CREATE TABLE Slonik ... 13 / 18 Slony Master Switching User 000000000 111111111 000 111 111111111 000000000 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 111111111 000000000 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 User 111111111 000000000 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 111111111 000000000 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 111111111 000000000 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 Origin sl_log 11111111 00000000 00000000 11111111 00000000 11111111 00000000 11111111 00000000 11111111 00000000 11111111 00000000 11111111 00000000 11111111 00000000 11111111 00000000 11111111 00000000 11111111 00000000 11111111 00000000 11111111 00000000 11111111 Subscriber sl_log 00000000 11111111 11111111 00000000 00000000 11111111 00000000 11111111 00000000 11111111 00000000 11111111 00000000 11111111 00000000 11111111 00000000 11111111 00000000 11111111 00000000 11111111 00000000 11111111 00000000 11111111 User 000000000 111111111 000 111 111111111 000000000 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 111111111 000000000 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 User 111111111 000000000 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 111111111 000000000 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 111111111 000000000 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 000000000 111111111 000 111 Subscriber sl_log 11111111 00000000 00000000 11111111 00000000 11111111 00000000 11111111 00000000 11111111 00000000 11111111 00000000 11111111 00000000 11111111 00000000 11111111 00000000 11111111 00000000 11111111 00000000 11111111 00000000 11111111 00000000 11111111 Origin sl_log 00000000 11111111 11111111 00000000 00000000 11111111 00000000 11111111 00000000 11111111 00000000 11111111 00000000 11111111 00000000 11111111 00000000 11111111 00000000 11111111 00000000 11111111 00000000 11111111 00000000 11111111 14 / 18 Bucardo 00000 11111 0 1 0 1 00000 11111 0 1 0 1 00000 11111 0 1 0 1 0 1 0 0 1 1 0 1 111111 000000 0 1 0 1 000000 111111 0 1 0 000000 111111 0 1 1 0 1 000000 111111 0 1 0 0 1 1 0 1 0 1 0 1 0 1 0 0 1 1 0 1 00000 11111 0 1 0 1 00000 11111 0 1 0 00000 11111 0 1 1 0 1 00000 11111 0 1 0 00000 11111 0 1 1 0 1 00000 11111 0 1 0 1 00000 11111 0 1 0 00000 11111 0 1 1 0 1 Asynchronous ◮ Similar to Slony, except multi-master with conflict resolution ◮ Conflict resolution rules are user-configurable 000000 111111 0 1 0 1 000000 111111 0 1 0 1 000000 111111 0 1 0 1 0 1 0 0 1 1 0 1 111111 000000 0 1 0 1 000000 111111 0 1 0 000000 111111 0 1 1 0 1 000000 111111 0 1 0 0 1 1 0 1 0 1 0 1 0 1 0 0 1 1 0 1 00000 11111 0 1 0 1 00000 11111 0 1 0 00000 11111 0 1 1 0 1 00000 11111 0 1 0 00000 11111 0 1 1 0 1 00000 11111 0 1 0 1 00000 11111 0 1 0 00000 11111 0 1 1 0 1 with Conflict Resolution 15 / 18 Pgpool II INSERT, UPDATE, DELETE to all hosts 0 1 0 1 1111 0000 0 1 0 1111 0000 01 1 0 1 0000 1111 0 1 0 1111 0000 01 1 0 1 0000 1111 0 1 0 0000 1111 01 1 0 1 0 1 0 01 1 0 1 00000 11111 0 1 0 00000 11111 01 1 0 1 00000 11111 0 1 0 00000 11111 01 1 0 1 00000 11111 0 1 0 1 00000 11111 0 1 0 1 1111 0000 0 1 0 1 1111 0000 0 1 0 1 0 1 0 1 1111 0000 0 1 0 1 0000 1111 0 1 0 1 0000 1111 0 1 0 1 0 1 0 1 0 1 0 1 00000 11111 0 1 0 1 00000 11111 0 1 0 1 00000 11111 0 1 0 1 00000 11111 0 1 0 1 00000 11111 0 1 0 1 00000 11111 0 1 0 1 1111 0000 0 1 0 1 1111 0000 0 1 0 1 0000 1111 0 1 0 1 1111 0000 0 1 0 1 0000 1111 0 1 0 1 0000 1111 0 1 0 1 0 1 0 1 0 1 0 1 00000 11111 0 1 0 1 00000 11111 0 1 0 1 00000 11111 0 1 0 1 00000 11111 0 1 0 1 00000 11111 0 1 0 1 00000 11111 ◮ Automatically load-balances read queries ◮ Queries with non-deterministic behavior can cause inconsistency ◮ Allows parallel query execution on all nodes ◮ Also does connection pooling and query caching pgpool SELECT to any host 0 1 0 1 1111 0000 0 1 0 1111 0000 01 1 0 1 0000 1111 0 1 0 1111 0000 01 1 0 1 0000 1111 0 1 0 0000 1111 01 1 0 1 0 1 0 01 1 0 1 00000 11111 0 1 0 00000 11111 01 1 0 1 00000 11111 0 1 0 00000 11111 01 1 0 1 00000 11111 0 1 0 1 00000 11111 16 / 18 Pgpool II With Streaming Replication INSERT, UPDATE, DELETE to master host 0 1 0 1 0000 1111 0 1 0 1 0000 1111 0 1 0 01 1 0 1 0000 1111 0 1 0 1111 0000 01 1 0 1 0000 1111 0 1 0 01 1 0 1 0 1 0 00000 11111 01 1 0 1 00000 11111 0 1 0 00000 11111 01 1 0 1 00000 11111 0 1 0 00000 11111 01 1 0 1 00000 11111 0 1 0 1 0000 1111 0 1 0 1 0000 1111 0 1 0 1 0 1 0 1 0000 1111 0 1 0 1 1111 0000 0 1 0 1 0000 1111 0 1 0 1 0 1 0 1 0 1 0 1 00000 11111 0 1 0 1 00000 11111 0 1 0 1 00000 11111 0 1 0 1 00000 11111 0 1 0 1 00000 11111 0 1 0 1 00000 11111 0 1 0 1 1111 0000 0 1 0 1 0000 1111 pgpool SELECT to any host 0 1 0 1 0 1 0 1 streaming 0000 1111 0 1 0 1 0000 1111 0 1 0 1 0000 1111 0 1 0 1 replication 00000 0 1 0 1 0 1 0 1 11111 0 1 0 1 00000 11111 0 1 0 1 11111 00000 0 1 0 1 00000 11111 0 1 0 1 00000 11111 0 1 0 1 00000 11111 0 1 0 1 0000 1111 0 1 0 1 0000 1111 0 1 0 01 1 0 1 0000 1111 0 1 0 1111 0000 01 1 0 1 0000 1111 0 1 0 01 1 0 1 0 1 0 00000 11111 01 1 0 1 00000 11111 0 1 0 00000 11111 01 1 0 1 00000 11111 0 1 0 00000 11111 01 1 0 1 00000 11111 Slave Slave Master Streaming replication avoids the problem of non-deterministic queries producing different results on different hosts. replication 17 / 18 Summary Feature Most Popular Implementation Communication Method No Special hardware required Allows multiple master servers No master server overhead No waiting for multiple servers Master failure will never lose data Slaves accept read-only queries Per-table granularity No conflict resolution necessary StatementBased Replication Middleware Asynchchronous MultiMaster Replic. Synchchronous MultiMaster Replic. table rows & row locks Shared Disk Fail-over File System Replic. Transaction WAL Log Shipping Triggerbased Replic. NAS DRBD disk blocks Log shipping pgpool-II WAL Slony table rows SQL Bucardo table rows • • • • • • • • • shared disk • • • • • • • • • • • • • • • • • • • • • • • • http://momjian.us/presentations 18 / 18