From ced698abc61994eb3c4f862c745a067fb08d9918 Mon Sep 17 00:00:00 2001 From: Vigneshwaran C Date: Mon, 27 Jun 2022 18:44:18 +0530 Subject: [PATCH v28 4/4] Document bidirectional logical replication steps in various scenarios. Document the steps for the following: a) Setting bidirectional replication between two nodes. b) Adding a new node when there is no table data on any of the nodes. c) Adding a new node when table data is present on the existing nodes. d) Generic steps for adding a new node to an existing set of nodes. --- doc/src/sgml/logical-replication.sgml | 301 ++++++++++++++++++++++ doc/src/sgml/ref/create_subscription.sgml | 5 +- 2 files changed, 305 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/logical-replication.sgml b/doc/src/sgml/logical-replication.sgml index bdf1e7b727..9d9d92c4c9 100644 --- a/doc/src/sgml/logical-replication.sgml +++ b/doc/src/sgml/logical-replication.sgml @@ -1479,4 +1479,305 @@ CREATE SUBSCRIPTION mysub CONNECTION 'dbname=foo host=bar user=repuser' PUBLICAT incremental changes to those tables. + + + Bidirectional logical replication + + + Bidirectional replication is useful for creating a multi-master database + environment for replicating read/write operations performed by any of the + member nodes. The steps to create a bidirectional replication in various + scenarios are given below. + + + + + Setting up bidirectional logical replication requires multiple steps to be + performed on various nodes. Because not all operations are transactional, + the user is advised to take backups. + + + + + Setting bidirectional replication between two nodes + + The following steps demonstrate how to create a two-node bidirectional + replication when there is no table data present on both nodes + node1 and node2: + + + + Create a publication on node1: + +node1=# CREATE PUBLICATION pub_node1 FOR TABLE t1; +CREATE PUBLICATION + + + + Create a publication on node2: + +node2=# CREATE PUBLICATION pub_node2 FOR TABLE t1; +CREATE PUBLICATION + + + + Lock the table t1 on node1 and + node2 in EXCLUSIVE mode until the + setup is completed. + + + + Create a subscription on node2 to subscribe to + node1: + +node2=# CREATE SUBSCRIPTION sub_node2_node1 +node2-# CONNECTION 'dbname=foo host=node1 user=repuser' +node2-# PUBLICATION pub_node1 +node2-# WITH (copy_data = off, origin = local); +CREATE SUBSCRIPTION + + + + Create a subscription on node1 to subscribe to + node2: + +node1=# CREATE SUBSCRIPTION sub_node1_node2 +node1-# CONNECTION 'dbname=foo host=node2 user=repuser' +node1-# PUBLICATION pub_node2 +node1-# WITH (copy_data = off, origin = local); +CREATE SUBSCRIPTION + + + + Now the bidirectional logical replication setup is complete between + node1 and node2. Any incremental + changes from node1 will be replicated to + node2, and any incremental changes from + node2 will be replicated to node1. + + + + + Adding a new node when there is no table data on any of the nodes + + The following steps demonstrate adding a new node node3 + to the existing node1 and node2 when + there is no t1 data on any of the nodes. This requires + creating subscriptions on node1 and + node2 to replicate the data from + node3 and creating subscriptions on + node3 to replicate data from node1 + and node2. Note: These steps assume that the + bidirectional logical replication between node1 and + node2 is already completed. + + + + Create a publication on node3: + +node3=# CREATE PUBLICATION pub_node3 FOR TABLE t1; +CREATE PUBLICATION + + + + Lock table t1 on all the nodes node1, + node2 and node3 in + EXCLUSIVE mode until the setup is completed. + + + + Create a subscription on node1 to subscribe to + node3: + +node1=# CREATE SUBSCRIPTION sub_node1_node3 +node1-# CONNECTION 'dbname=foo host=node3 user=repuser' +node1-# PUBLICATION pub_node3 +node1-# WITH (copy_data = off, origin = local); +CREATE SUBSCRIPTION + + + + Create a subscription on node2 to subscribe to + node3: + +node2=# CREATE SUBSCRIPTION sub_node2_node3 +node2-# CONNECTION 'dbname=foo host=node3 user=repuser' +node2-# PUBLICATION pub_node3 +node2-# WITH (copy_data = off, origin = local); +CREATE SUBSCRIPTION + + + + Create a subscription on node3 to subscribe to + node1: + +node3=# CREATE SUBSCRIPTION sub_node3_node1 +node3-# CONNECTION 'dbname=foo host=node1 user=repuser' +node3-# PUBLICATION pub_node1 +node3-# WITH (copy_data = off, origin = local); +CREATE SUBSCRIPTION + + + + Create a subscription on node3 to subscribe to + node2: + +node3=# CREATE SUBSCRIPTION sub_node3_node2 +node3-# CONNECTION 'dbname=foo host=node2 user=repuser' +node3-# PUBLICATION pub_node2 +node3-# WITH (copy_data = off, origin = local); +CREATE SUBSCRIPTION + + + + Now the bidirectional logical replication setup is complete between + node1, node2 and + node3. Incremental changes made on any node will be + replicated to the other two nodes. + + + + + Adding a new node when table data is present on the existing nodes + + The following steps demonstrate adding a new node node3 + which has no t1 data to the existing + node1 and node2 where + t1 data is present. This needs similar steps; the only + change required here is that node3 should create a + subscription with copy_data = force to one of the + existing nodes so it can receive the existing t1 data + during initial data synchronization. Note: These steps assume that the + bidirectional logical replication between node1 and + node2 is already completed, and the pre-existing data + in table t1 is already synchronized on both those + nodes. + + + + Create a publication on node3: + +node3=# CREATE PUBLICATION pub_node3 FOR TABLE t1; +CREATE PUBLICATION + + + + Lock table t1 on node2 and + node3 in EXCLUSIVE mode until the + setup is completed. There is no need to lock table t1 on + node1 because any data changes made will be synchronized + while creating the subscription with copy_data = force. + + + + Create a subscription on node1 to subscribe to + node3: + +node1=# CREATE SUBSCRIPTION sub_node1_node3 +node1-# CONNECTION 'dbname=foo host=node3 user=repuser' +node1-# PUBLICATION pub_node3 +node1-# WITH (copy_data = off, origin = local); +CREATE SUBSCRIPTION + + + + Create a subscription on node2 to subscribe to + node3: + +node2=# CREATE SUBSCRIPTION sub_node2_node3 +node2-# CONNECTION 'dbname=foo host=node3 user=repuser' +node2-# PUBLICATION pub_node3 +node2-# WITH (copy_data = off, origin = local); +CREATE SUBSCRIPTION + + + + Create a subscription on node3 to subscribe to + node1. Use copy_data = force so that + the existing table data is copied during initial sync: + +node3=# CREATE SUBSCRIPTION sub_node3_node1 +node3-# CONNECTION 'dbname=foo host=node1 user=repuser' +node3-# PUBLICATION pub_node1 +node3-# WITH (copy_data = force, origin = local); +CREATE SUBSCRIPTION + + + + Create a subscription on node3 to subscribe to + node2. Use copy_data = off + because the initial table data would have been + already copied in the previous step: + +node3=# CREATE SUBSCRIPTION sub_node3_node2 +node3-# CONNECTION 'dbname=foo host=node2 user=repuser' +node3-# PUBLICATION pub_node2 +node3-# WITH (copy_data = off, origin = local); +CREATE SUBSCRIPTION + + + + Now the bidirectional logical replication setup is complete between + node1, node2 and + node3. Incremental changes made on any node will be + replicated to the other two nodes. + + + + + Adding a new node when table data is present on the new node + + + Adding a new node when table data is present on the new node is not + supported. + + + + + + Generic steps for adding a new node to an existing set of nodes + + Step-1: Create a publication on the new node. + + + Step-2: Lock the required tables of the new node in + EXCLUSIVE mode until the setup is complete. (This lock + is necessary to prevent any modifications from happening on the new node. + If data modifications occurred after Step-3, there is a chance they could + be published to the first node and then synchronized back to the new node + while creating the subscription in Step-5. This would result in + inconsistent data). + + + Step-3. Create subscriptions on existing nodes to the publication on the + new node with origin = local and + copy_data = off. (The copy_data = off + is OK here because it is asserted that the published tables of the new node + will have no pre-existing data). + + + Step-4. Lock the required tables of the existing nodes except the first node + in EXCLUSIVE mode until the setup is complete. (This + lock is necessary to prevent any modifications from happening. If data + modifications occur, there is a chance that modifications done between + Step-5 and Step-6 will not be synchronized to the new node. This would + result in inconsistent data. There is no need to lock the required tables + on the first node because any data changes made will be synchronized while + creating the subscription with copy_data = force). + + + Step-5. Create a subscription on the new node to the publication on the + first node with origin = local and + copy_data = force. (This will copy the same table data + from the existing nodes to the new node). + + + Step-6. Create subscriptions on the new node to publications on the + remaining nodes with origin = local and + copy_data = off. (The copy_data = off is OK here because + the existing node data was already copied to the new node in Step-5). + + + + diff --git a/doc/src/sgml/ref/create_subscription.sgml b/doc/src/sgml/ref/create_subscription.sgml index f0bc2ba63d..431abda656 100644 --- a/doc/src/sgml/ref/create_subscription.sgml +++ b/doc/src/sgml/ref/create_subscription.sgml @@ -408,7 +408,10 @@ CREATE SUBSCRIPTION subscription_namecopy_data = force. + copy_data = force. Refer to + for how + copy_data and origin can be used to + set up bidirectional replication. -- 2.32.0