Transcription of PostgreSQL Replication Solutions - Momjian
1 PostgreSQLR eplication SolutionsBRUCEMOMJIANR eplication is a complex feature. PostgreSQL supports a varietyof Replication Commons Attribution updated: July, 20181/18 Uses for Over 3/18 Data Warehousing 4/18 Load Balancing 5/18 Remote Servers 6/18 Mobile Servers 7/18 Replication StorageNAS or SAN No overhead No data loss on fail-over Slave cannot executequeries9/18 Storage MirroringDRBD No overhead on master Synchronous orasynchronous Possible data loss onfail-over when usingasynchronous Slave cannot executequeries10/18 Streaming ReplicationWriteAheadLog(WAL) No overhead on master Slaves can execute queries Possible data loss onfail-over when usingasynchronous mode Synchronous optionavailable (Postgres )11/18 SlonyAsynchronous Triggers add overhead tothe master Possible data loss onfail-over Replication possible evenover slow links Slave can executeread-only queries Table-level granularityallows complex datapartitioning configurations12/18 Slony InternalsSubscribersOther Slonik.
2 CREATETABLE modificationsSubscriberOriginUserUsersl_ logsl_logCREATETRIGGER fills sl_logTRIGGERCREATE prevents13/18 Slony Master Switching Usersl_logsl_logSubscriberOriginUserUser sl_logsl_logSubscriberOriginUser14/18 Bucardowith Conflict ResolutionAsynchronous Similar to Slony, exceptmulti-master with conflictresolution Conflict resolution rulesare user-configurable15/18 Pgpool IISELECTINSERT, UPDATE,DELETE to allhostspgpoolto any host Automaticallyload-balances read queries Queries withnon-deterministic behaviorcan cause inconsistency Allows parallel queryexecution on all nodes Also does connectionpooling and query caching16/18 Pgpool II With Streaming ReplicationSELECTINSERT, UPDATE,DELETE to masterhostSlaveSlaveMasterreplicationrep licationto any hostpgpoolstreaming Streaming Replication avoidsthe problem ofnon-deterministic queriesproducing different results ondifferent PopularImplementationNASDRBDLog shippingSlonypgpool-IIBucardoCommunicati ondisktabletabletable rowsMethodshared diskblocksWALrowsSQLrows& row locksNo Specialhardware required Allows multiplemaster servers No master serveroverhead No waiting formultiple servers Master failure willnever lose data Slaves acceptread-only queries Per-tablegranularity No conflictresolutionnecessary
