PostgreSQL Replication Solutions - Bruce Momjian

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