PostgreSQL Replication

Transcript

1. SimScale Engineering PostgreSQL Replication Stefan Seelmann
2. PostgreSQL Replication Agenda SimScale © 2018 • Overview and 3rd Party Solutions • PG Binary Replication • PG Logical Replication • Use Cases • AWS RDS
3. PostgreSQL Replication Overview and History SimScale © 2018 https://github.com/amueller/word_cloud
4. 3rd Party Solutions SimScale © 2018 • Shared disk or Replicated file system ◦ DRBD, rsync, ... • Trigger based ◦ Slony, Londiste, Bucardo ◦ Causes overhead in master server • Multi-Master ◦ BDR, Postgres-XC, Bucardo, • Sharding ◦ Citus, pg_shard • PostgreSQL Replication
5. PostgreSQL Binary Replication SimScale © 2018 • Since 9.0 • Single master/primary + one or multiple slaves/replicas/standby • Replicates full database: ◦ Byte by byte ◦ Including users, DDL, sequences ◦ Excluding configuration files (postgres.conf, pg_hba.conf) • Asynchronous (default) or synchronous • Less overhead/pressure on master • Based on Write Ahead Log (WAL) PostgreSQL Replication
6. PostgreSQL Binary Replication SimScale © 2018 https://e-mc2.net/es/hot-standby-y-streaming-replication PostgreSQL Replication
7. PostgreSQL Binary Replication - Pitfalls SimScale © 2018 • WAL recycling on primary ◦ When replica cannot catch up or lost connection for too long ➢ Continuous archiving • Query conflicts ◦ When application of WAL conflicts with long running query ➢ hot_standby_feedback, max_standby_streaming_delay • Many configuration options PostgreSQL Replication
8. PostgreSQL Binary Replication - Configuration SimScale © 2018 PostgreSQL Replication https://github.com/amueller/word_cloud
9. PostgreSQL Logical Replication SimScale © 2018 • Since 10 ◦ Based on logical decoding (since 9.4, pglogical, BDR) • Publish/subscribe model with initial snapshot • Independent versions between publisher and subscriber • Flexible ◦ Partial data replication possible, e.g. only particular tables ◦ Multiple source DBs into one target DB • DDL changes and sequences are not replicated => conflicts and errors PostgreSQL Replication
10. Use Cases SimScale © 2018 • HA / failover: ◦ Continuous archiving + streaming replication + use replication slots ◦ If hot standby: no hot_standby_feedback, low max_standby_streaming_delay • Read replica for data analysis: ◦ File based log shipping and high max_standby_archive_delay ◦ Streaming replication and high max_standby_streaming_delay ◦ Logical replication • Write scaling ◦ Partitioning/sharding, multi-master: only if writes are mostly conflict free PostgreSQL Replication
11. AWS RDS SimScale © 2018 • Read replica: built-in streaming replication • Multi AZ: EBS level replication https://aws.amazon.com/blogs/database/amazon-rds-under-the-hood-multi-az/ PostgreSQL Replication
12. Questions? SimScale © 2018 Stefan Seelmann sseelmann@simscale.com https://www.simscale.com/ PostgreSQL Replication