MySQL Group Replication vs PostgreSQL Bi-Directional Replication

1 .Percona Live 2019 Austin, Texas | May 28th – 30th, 2019 

2 .MySQL Group Replication vs PostgreSQL Bi-Directional Replication Mariella Di Giacomo Dennis Rich Viasat 

3 .Goal 

4 . Goal Inspire everyone to use both MySQL Group Replication and PostgreSQL Bi-Directional Replication to take advantage of their numerous capabilities. 4 

5 .Outline 

6 . Outline • Definition and brief introduction of Database Replication • Brief introduction to PostgreSQL Replication • Brief introduction to MySQL Replication • Main features of PostgreSQL Bi-Directional Replication 1 (Postgres-BDR) • Main features of MySQL Group Replication • Similarities and Differences between MySQL Group Replication and Postgres-BDR • Conclusions 6 

7 .Database Replication 

8 . Database Replication • The process of copying data from one location to another. • The frequent electronic copying of data from one or more databases in one server to another server so that all users share the same level of information. • Key feature for High Available (Fault-Tolerant) and Scalable environments. 8 

9 . Database Replication • Database servers can work together to allow a second server to take over quickly if the primary server fails (high availability), or to allow several computers to serve the same data in read and/or write mode (load balancing and high availability). • PostgreSQL and MySQL open source relational database management systems (RDBMS) support database replication. 9 

10 . Database Replication The main high availability (HA) solutions for MySQL and PostgreSQL can be achieved using one of the following replication designs (topologies): • Master Slave (single master to one slave, standby, or to multiple slaves) • Master Master (active master and passive master) • Multi-Master (two or more active masters) 10 

11 . Database Replication • Database Replication is used with many database management systems (DBMS), usually with a master slave relationship between the original and the copies. • For example: the master (original) node logs the changes, which then ripple through to one or more slave (copy) nodes. Each slave node records a message stating that it has received the change successfully. • The successful execution of a modification will allow subsequent changes. 11 

12 . Database Replication • Failover (master master) replication (high availability) is a special case of master slave database replication. • For example: one (or more) passive slave server(s) is replicating the master data in order to prepare for takeover in the event the master stops functioning. The active master is the only server that will accept writes from clients. 12 

13 . Database Replication • Multi-Master replication: a method of database replication which allows data to be stored by a group of database servers, and updated by any member of the group. • Servers are responsive to client data requests. The entire system is responsible for two main tasks: 1. Propagating data modifications made by each member to the rest of the group. 2. Resolving conflicts between concurrent changes made by different members. 13 

14 . Database Replication Two topologies of Multi-Master Replication 14 

15 .PostgreSQL Replication 

16 . PostgreSQL Replication PostgreSQL supports two main types of replication: 1. Physical (Block Level) Streaming Replication • Sends Write-Ahead Logging (WAL) transaction log records to replicas. • Is suitable for Local High Availability (HA), High Bandwidth. • Is streamed before committed (saved permanently and visible to users) changes (commit). 2. Logical (Row Level) Streaming Replication • Sends data only (row level changes) to replicas. • Is suitable for Remote High Availability (HA), Lower Bandwidth. • Is streamed at committed changes (commit). 16 

17 . PostgreSQL Replication Physical (Block Level) Streaming Replication 17 

18 .PostgreSQL Replication Logical (Row Level) Streaming Replication 18 

19 . PostgreSQL Internal Replication PostgreSQL supports core (internal) solutions for replications: • Physical Asynchronous Write-Ahead Logging (WAL) transaction log records (Warm Standby) • Physical Asynchronous Streaming Replication (Hot Standby) 19 

20 . PostgreSQL Internal Replication • Streaming Synchronous Replication • Cascading Replication 20 

21 . PostgreSQL Internal Replication • Logical Decoding • Logical Streaming Replication (PostgreSQL version v10+) • Replication over SSL 21 

22 . PostgreSQL External Replication PostgreSQL supports external solutions for replications: • Focus: Bi-Directional Replication (BDR) (Latest BDR3 requires PostgreSQL v10+) • Slony • Bucardo • Londiste • PGLogical 22 

23 .MySQL Replication 

24 . MySQL Replication MySQL supports two main types of replication: 1. Event Based Replication • Sends events to each slave server. • Sends events in statement, row or mixed format. 2. Global Transaction Identifiers (GTID) Based Replication • Sends GTIDs to each slave server. • Is completely transaction-based. • Sends the information using statement-based or row-based replication. 24 

25 . MySQL Replication MySQL Replication with Events from Binary Logs 25 

26 . MySQL Replication MySQL Replication with GTIDs from Binary Logs 26 

27 . MySQL Internal Replication MySQL supports core (internal) replication solutions: • Asynchronous • Synchronous and Semi-Synchronous • Statement, Row or Mixed Replication with Events from Binary Log • Replication with Global Transaction Identifiers (GTIDs) from Binary Log with Row and Statement Replication • Replication over SSL • Multi-Source Replication 27 

28 . MySQL External Replication MySQL supports external replication solutions: • Tungsten (replicator across different RDBMS) • Galera Cluster 28 

29 . PostgreSQL Bi-Directional Replication (Postgres-BDR)