PostgreSQL Replication in 10 Minutes - SCALE
Download as odp, pdf29 likes23,407 views
This document summarizes a presentation about PostgreSQL replication. It discusses different replication terms like master/slave and primary/secondary. It also covers replication mechanisms like statement-based and binary replication. The document outlines how to configure and administer replication through files like postgresql.conf and recovery.conf. It discusses managing replication including failover, failback, remastering and replication lag. It also covers synchronous replication and cascading replication setups.
More Related Content
What's hot (20)
Similar to PostgreSQL Replication in 10 Minutes - SCALE (20)
More from PostgreSQL Experts, Inc. (20)
Recently uploaded (20)
PostgreSQL Replication in 10 Minutes - SCALE
- 1. PostgreSQL Replication in 10 Minutes Josh Berkus PostgreSQL Experts, Inc. SCALE 2014
- 2. 1. See a multistage replication demo 2. Hear how to set up and configure replication 3. Understand different setups and replication issues
- 7. replication terms master / slave master / standby master / replica primary / secondary primary / replica
- 8. replication mechanisms 1. statement 2. row 3. binary
- 9. replication mechanisms 1. queries 2. rows 3. data pages
- 10. also called ... ● streaming replication – ● refers to the ability to stream new data pages over a network connection hot standby – refers to the ability of standbys to run readonly queries while in standby mode
- 11. more terms ● recovery – ● snapshot, clone, basebackup – ● binary replication came from binary backup, i.e. Point In Time Recovery taking a moment-in-time copy of a running database server standalone – a lone read-write server, neither master nor replica
- 13. configuration files ● postgresql.conf – ● same settings for master, replica recovery.conf – – presence turns on replication must be in $PGDATA
- 14. views & functions process list ● pg_stat_replication ● pg_is_in_recovery() ● pg_xlog* functions ●
- 15. permissions & security A. B. C. D. replication permission pg_hba.conf max_wal_senders firewall/network
- 17. archiving replication 1. 2. 3. 4. 5. 6. set up archiving start archiving pg_start_backup('label') rsync all files pg_stop_backup() bring up replica
- 18. reasons to archive replica out-of-sync ● combine with PITR or DR ● very erratic connection to master ● need remastering before 9.3 ●
- 19. archiving tips use a script which handles copy failure ● use a shared drive ● put archive on a partition ● monitor for archive growth ● compression ●
- 21. more terms ● failover, promotion – ● failback – ● making a replica into a master/standalone returning the original master to master status remastering – designating a new master in a group of servers
- 22. replica promotion pg_ctl promote ● trigger file ● rm recovery.conf & restart ●
- 23. failover has 3 parts 1. failing over the database 2. failing over the connections 3. STONITH
- 24. manual failover ● advantages: – – ● easy to set up fewer accidental failovers disadvantages: – – downtime being woken up at 3am
- 25. automated failover ● advantages: – – ● low downtime sleep through the night disadvantages: – – – hard to set up correctly need broker server accidentally triggered failovers
- 27. www.handyrep.org
- 28. STONITH use corosync/VIP ● use connection failover ● use peer broker server ●
- 29. remastering need the replica which is “furthest ahead” ● measure both receive point and replay point ● need 9.3 for “streaming-only” remastering ●
- 31. reasons for lag ● network delay – speed of light replica too busy ● file operations block ● – – VACUUM DROP TABLE
- 32. replication lag issues inconsistency (if load-balancing) ● query cancel ● – applications need to retry queries catch-up speed ● burden on master ●
- 34. what synch rep does guarantee against data loss
- 35. what it doesn't ● enforce global consistency – – ● master can be behind replica snapshot can be behind help availability
- 36. “I would rather be down than potentially lose data.”
- 37. how to synch rep 1. pick one (or a pool) of servers to be your synch replicas 2. change application_name 3. change master's postgresql.conf 4. reload
- 38. Postgres specialities implements only 1-redundant model ● synch is per-transaction ● – – not per replica synch only important transactions
- 39. synch rep design 1 replica is synch replica ● several asych replicas ● load-balance to asynch only ● always failover to synch replica ●
- 41. how to cascade 1. 2. 3. 4. have master & replica clone the master or the replica point primary_conninfo to the replica bring up the new replica
- 42. why to cascade limit connections to master ● don't clone master ● know which replica is ahead ●
- 43. why to cascade Los Angeles walsender Shanghai walsender walreciever walreciever walreciever
- 44. why not cascade? complexity ● cycles ● increases SPOFs ●
- 46. use a shared archive WAL-E
- 47. ephemeral replicas no sync to disk ● do not recover from crash ● – ● spin up a replacement instead turn off all logging/disk – fsync off, bgwriter off, full_page_writes off
- 48. sharding and replication server 1 server 2
- 49. sharding and replication server 1 server 2
- 50. changes coming up ● in dev: pg_rewind – failback without full clone 9.4: merge recovery.conf & postgresql.conf ● 9.4+: “logical” streaming replication ●
- 51. questions? ● HandyRep: www.handyrep.org ● Josh Berkus: josh@pgexperts.com – – ● PGX: www.pgexperts.com Blog: www.databasesoup.com Upcoming Events – – NYC pgDay: April 2-3 pgCon, Ottawa, May 21-24 Copyright 2013 PostgreSQL Experts Inc. Released under the Creative Commons Share-Alike 3.0 License. All images are the property of their respective owners. The WAL-E image is the property of Disney Inc. and is use here as parody.