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: September, 2015 1 / 18 Uses for Replication Theolotech.com PostgreSQL, Replication Solutions 2 / 18 Fail Over 111111 000000 0 1 1 0 000000 111111 0 1 0 000000 111111 0 1 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 000000 111111 0 1 0 000000 111111 0 1 1 0 1 000000 111111 0 1 0 1 000000 111111 0 1 0 1 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 00000 11111 0 1 0 1 00000 11111 0 1 0 1 00000 11111 111111 000000 0 1 1 0 000000 111111 0 1 0 000000 111111 0 1 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 000000 111111 0 1 0 000000 111111 0 1 1 0 1 000000 111111 0 1 0 1 000000 111111 0 1 0 1 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 00000 11111 0 1 0 1 00000 11111 0 1 0 1 00000 11111 PostgreSQL, Replication Solutions 3 / 18 Data Warehousing 111111 000000 0 0 1 1 000000 111111 0 0 1 1 000000 111111 0 1 0 1 0 0 1 1 0 0 1 1 000000 111111 0 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 1 0 0 1 1 0 0 1 1 00000 11111 0 0 1 1 00000 11111 0 0 1 00000 11111 0 1 1 0 1 00000 11111 0 0 1 1 00000 11111 0 0 1 1 00000 11111 0 0 1 1 00000 11111 0 0 1 00000 11111 0 1 0 1 1 111111 000000 0 0 1 1 000000 111111 0 0 1 1 000000 111111 0 1 0 1 0 0 1 1 0 0 1 1 000000 111111 0 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 1 0 0 1 1 0 0 1 1 00000 11111 0 0 1 1 00000 11111 0 0 1 00000 11111 0 1 1 0 1 00000 11111 0 0 1 1 00000 11111 0 0 1 1 00000 11111 0 0 1 1 00000 11111 0 0 1 00000 11111 0 1 0 1 1 PostgreSQL, Replication Solutions 4 / 18 Load Balancing 11111 00000 0 0 1 1 00000 11111 0 0 1 1 00000 11111 0 1 0 1 0 0 1 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 0 1 1 0 1 0 1 0 0 1 1 0 0 1 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 0 1 1 00000 11111 0 0 1 1 00000 11111 0 0 1 1 00000 11111 0 1 0 1 00000 11111 11111 00000 0 0 1 1 00000 11111 0 0 1 1 00000 11111 0 1 0 1 0 0 1 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 0 1 1 0 1 0 1 0 0 1 1 0 0 1 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 0 1 1 00000 11111 0 0 1 1 00000 11111 0 0 1 1 00000 11111 0 1 0 1 00000 11111 PostgreSQL, Replication Solutions 5 / 18 Remote Servers 0 1 0 1 11111 00000 0 1 0 1 00000 11111 0 1 0 1 00000 11111 0 1 0 1 0 1 0 0 1 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 1 0 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 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 11111 00000 0 1 0 1 00000 11111 0 1 0 1 00000 11111 0 1 0 1 0 1 0 0 1 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 1 0 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 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 PostgreSQL, Replication Solutions 6 / 18 Mobile Servers 111111 000000 0 0 1 1 000000 111111 0 0 1 1 000000 111111 0 0 1 1 0 0 1 1 0 0 1 1 000000 111111 0 0 1 1 000000 111111 0 0 1 000000 111111 0 1 0 1 1 000000 111111 0 1 0 0 1 0 1 1 0 0 1 1 0 0 1 0 1 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 1 00000 11111 0 0 1 1 00000 11111 0 0 1 00000 11111 0 1 0 1 1 PostgreSQL, Replication Solutions 7 / 18 Replication Solutions Taotaomona Computing PostgreSQL, Replication Solutions 8 / 18 Shared Storage 1 1 0 0 0000 1111 0 1 0 1 0000 1111 1 1 0 1 1 00 0 1111 0000 0 1 0 1 0000 1111 0 1 0 1 0000 1111 0 1 0 11 1 0 0 1 1 0 0 00000 11111 0 1 0 1 00000 11111 1 1 0 00000 11111 00 1 0 1 00000 11111 0 1 0 00000 11111 1 1 01 0 00000 11111 1 1 0 0 0000 1111 0 1 0 1 0000 1111 1 1 0 0 1 1 0 0 1111 0000 0 1 0 1 0000 1111 0 1 0 1 0000 1111 0 1 0 1 1 1 0 0 1 1 0 0 00000 11111 0 1 0 1 00000 11111 1 1 0 0 00000 11111 0 1 0 1 00000 11111 0 1 0 1 00000 11111 1 1 0 0 00000 11111 ◮ No overhead ◮ No data loss on fail-over ◮ Slave cannot execute queries NAS or SAN PostgreSQL, Replication Solutions 9 / 18 Storage Mirroring 0 1 0 1 1111 0000 0 0 1 1 0000 1111 0 1 0 01 1 0 1 0000 1111 0 1 0 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 1 0 1 00000 11111 0 1 0 1 00000 11111 0 1 0 00000 11111 01 1 0 1 00000 11111 0 1 0 1 1111 0000 0 0 1 1 0000 1111 0 1 0 01 1 0 1 0000 1111 0 1 0 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 1 0 1 00000 11111 0 1 0 1 00000 11111 0 1 0 00000 11111 01 1 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 PostgreSQL, Replication Solutions 10 / 18 Streaming Replication 0 0 1 1 0000 1111 0 1 0 1 0000 1111 0 0 1 1 1111 0000 0 0 1 1 0000 1111 0 1 0 1 0000 1111 0 1 0 1 0000 1111 0 1 0 1 0 0 1 1 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 0 1 00000 11111 0 0 1 1 00000 11111 Write Ahead Log (WAL) 0 0 1 1 0000 1111 0 1 0 1 0000 1111 0 0 1 1 1111 0000 0 0 1 1 0000 1111 0 1 0 1 0000 1111 0 1 0 1 0000 1111 0 1 0 1 0 0 1 1 0 0 1 1 00000 11111 0 1 0 00000 11111 01 0 1 1 00000 11111 0 1 0 1 00000 11111 0 1 0 00000 11111 01 0 1 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) PostgreSQL, Replication Solutions 11 / 18 Slony 11111 00000 0 1 0 1 1 00000 11111 0 1 00000 11111 0 0 0 1 1 0 0 1 1 0 1 0 000000 111111 0 1 1 0 1 000000 111111 0 1 0 000000 111111 0 1 0 1 1 000000 111111 0 0 1 1 0 1 0 0 1 0 1 1 0 1 0 0 1 1 0 1 00000 11111 0 0 1 1 00000 11111 0 1 0 00000 11111 0 1 0 1 1 00000 11111 0 1 0 00000 11111 0 1 1 0 1 00000 11111 0 0 1 1 00000 11111 0 1 0 00000 11111 0 1 0 1 1 Asynchronous 11111 00000 0 1 0 1 1 00000 11111 0 1 00000 11111 0 0 0 1 1 0 0 1 1 0 1 0 000000 111111 0 1 1 0 1 000000 111111 0 1 0 000000 111111 0 1 0 1 1 000000 111111 0 0 1 1 0 1 0 0 1 0 1 1 0 1 0 0 1 1 0 1 00000 11111 0 0 1 1 00000 11111 0 1 0 00000 11111 0 1 0 1 1 00000 11111 0 1 0 00000 11111 0 1 1 0 1 00000 11111 0 0 1 1 00000 11111 0 1 0 00000 11111 0 1 0 1 1 ◮ 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 PostgreSQL, Replication Solutions 12 / 18 Slony Internals User 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 00000000 11111111 000 111 00000000 11111111 000 111 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 User Origin Subscriber 111111111 000000000 00 11 000000000 111111111 00 11 000000000 111111111 00 11 000000000 111111111 00 11 000000000 111111111 00 11 000000000 111111111 00 11 000000000 111111111 00 11 000000000 111111111 00 11 000000000 111111111 00 11 000000000 111111111 00 11 000000000 111111111 00 11 000000000 111111111 00 11 000000000 111111111 00 11 000000000 111111111 00 11 000000000 111111111 00 11 000000000 111111111 00 11 000000000 111111111 00 11 000000000 111111111 00 11 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 00000000 11111111 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 00000000 11111111 000000000 111111111 00 11 111111111 000000000 00 11 000000000 111111111 00 11 000000000 111111111 00 11 000000000 111111111 00 11 000000000 111111111 00 11 000000000 111111111 00 11 000000000 111111111 00 11 CREATE TRIGGER fills sl_log Other Subscribers CREATE TRIGGER prevents modifications CREATE TABLE Slonik ... PostgreSQL, Replication Solutions 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 PostgreSQL, Replication Solutions 14 / 18 Bucardo 00000 11111 0 1 1 0 00000 11111 0 1 0 1 00000 11111 0 1 0 1 1 1 0 0 0 1 0 1 111111 000000 0 1 0 000000 111111 0 1 1 0 1 000000 111111 0 1 0 1 1 1 000000 111111 0 1 0 0 1 0 0 1 0 0 1 1 0 1 0 1 0 1 00000 11111 1 1 0 1 0 00000 11111 0 1 0 00000 11111 1 1 0 1 0 00000 11111 0 1 00000 11111 0 0 1 0 1 00000 11111 0 1 0 1 00000 11111 0 1 1 0 1 00000 11111 1 0 0 00000 11111 Asynchronous ◮ Similar to Slony, except multi-master with conflict resolution ◮ Conflict resolution rules are user-configurable 00000 11111 0 1 1 0 00000 11111 0 1 0 1 00000 11111 0 1 0 1 1 1 0 0 0 1 0 1 111111 000000 0 1 0 000000 111111 0 1 1 0 1 000000 111111 0 1 0 1 1 1 000000 111111 0 1 0 0 1 0 0 1 0 0 1 1 0 1 0 1 0 1 00000 11111 1 1 0 1 0 00000 11111 0 1 0 00000 11111 1 1 0 1 0 00000 11111 0 1 00000 11111 0 0 1 0 1 00000 11111 0 1 0 1 00000 11111 0 1 1 0 1 00000 11111 1 0 0 00000 11111 with Conflict Resolution PostgreSQL, Replication Solutions 15 / 18 Pgpool II INSERT, UPDATE, DELETE to all hosts 0 0 1 1 1111 0000 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 0000 1111 01 1 0 1 0 0 1 1 0 1 0 11111 00000 01 1 0 1 00000 11111 0 1 0 00000 11111 01 1 0 1 00000 11111 0 0 1 00000 11111 01 1 0 1 00000 11111 0 1 0 1 1111 0000 0 0 1 1 1111 0000 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 0 1 1 00000 11111 0 1 0 1 00000 11111 0 1 0 1 00000 11111 0 0 1 1 1111 0000 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 0000 1111 0 1 0 1 0 0 1 1 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 0 1 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 0 1 1 1111 0000 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 0000 1111 01 1 0 1 0 0 1 1 0 1 0 11111 00000 01 1 0 1 00000 11111 0 1 0 00000 11111 01 1 0 1 00000 11111 0 0 1 00000 11111 01 1 0 1 00000 11111 PostgreSQL, Replication Solutions 16 / 18 Pgpool II With Streaming Replication INSERT, UPDATE, DELETE to master host 0 1 0 1 1111 0000 0 1 0 1 1111 0000 0 0 1 1 0 1 0 1 0000 1111 0 1 0 1 0000 1111 0 1 0 1 0000 1111 0 1 0 1 0 0 1 1 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 0 1 1 00000 11111 0 1 0 1 00000 11111 streaming replication Master 0 1 0 0000 1111 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 0000 1111 0 0 1 01 1 0 1 0 1 0 1 00000 11111 0 1 0 00000 11111 01 1 0 1 00000 11111 0 0 1 00000 11111 01 1 0 1 00000 11111 0 1 0 1 00000 11111 pgpool SELECT to any host 01 1 0 1 1111 0000 0 1 0 1111 0000 0 0 1 1 0 1 0 1 0000 1111 0 1 0 1 0000 1111 0 1 0 1 0000 1111 0 1 0 01 0 1 1 0 1 0 11111 00000 01 1 0 1 00000 11111 0 1 0 00000 11111 01 1 0 1 00000 11111 0 0 1 00000 11111 01 1 0 1 00000 11111 0 1 0 1 1111 0000 0 1 0 1 1111 0000 0 0 1 1 0 1 0 1 0000 1111 0 1 0 1 0000 1111 0 1 0 1 0000 1111 0 1 0 1 0 0 1 1 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 0 1 1 00000 11111 0 1 0 1 00000 11111 Slave Slave Streaming replication avoids the problem of non-deterministic queries producing different results on different hosts. replication PostgreSQL, Replication Solutions 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 PostgreSQL, Replication Solutions 18 / 18