From 689f8f1d78023220bb9682b26c5209d6377cfcc1 Mon Sep 17 00:00:00 2001 From: Vigneshwaran C Date: Tue, 31 May 2022 11:53:32 +0530 Subject: [PATCH v18 4/4] Document bidirectional logical replication steps in various scenarios. Document the steps for the following: a) Create a two-node bidirectional replication when there is no data in both the nodes. b) Adding a new node when there is no data in any of the nodes. c) Adding a new node when data is present in the existing nodes. d) Generic steps to add a new node to the existing set of nodes. --- doc/src/sgml/logical-replication.sgml | 310 ++++++++++++++++++++++ doc/src/sgml/ref/create_subscription.sgml | 5 +- 2 files changed, 314 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/logical-replication.sgml b/doc/src/sgml/logical-replication.sgml index 145ea71d61..e1506b51c1 100644 --- a/doc/src/sgml/logical-replication.sgml +++ b/doc/src/sgml/logical-replication.sgml @@ -1267,4 +1267,314 @@ CREATE SUBSCRIPTION mysub CONNECTION 'dbname=foo host=bar user=repuser' PUBLICAT incremental changes to those tables. + + + Bidirectional logical replication + + + Bidirectional replication is useful in creating a multi-master database + which helps in performing read/write operations from any of the nodes. + The steps to create a bidirectional replication in various scenarios are + given below. + + + + + Setting up bidirectional logical replication across nodes 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 steps to create a two-node bidirectional replication when there is no + data in both the nodes node1 and + node2 are given below: + + + + Create a publication in node1: + +node1=# CREATE PUBLICATION pub_node1 FOR TABLE t1; +CREATE PUBLICATION + + + + Create a publication in node2: + +node2=# CREATE PUBLICATION pub_node2 FOR TABLE t1; +CREATE PUBLICATION + + + + Lock the required tables of node1 and + node2 in EXCLUSIVE mode until the + setup is completed. + + + + Create a subscription in 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 in 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 the incremental changes from + node2 will be replicated to node1. + + + + + Adding a new node when there is no data in any of the nodes + + Adding a new node node3 to the existing + node1 and node2 when there is no data + in any of the nodes requires setting up subscription in + node1 and node2 to replicate the data + from node3 and setting up subscription in + node3 to replicate data from node1 + and node2. + + + + + It is assumed that the bidirectional logical replication between + node1 and node2 is already completed. + + + + + Create a publication in node3: + +node3=# CREATE PUBLICATION pub_node3 FOR TABLE t1; +CREATE PUBLICATION + + + + Lock the required tables of all the nodes node1, + node2 and node3 in + EXCLUSIVE mode until the setup is completed. + + + + Create a subscription in 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 in 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 in 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 in 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 + node2. Any subsequent changes in one node will + replicate the changes to the other nodes. + + + + + Adding a new node when data is present in the existing nodes + + Adding a new node node3 which has no data to the + existing node1 and node2 when data + is present in existing nodes node1 and + node2 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 to + receive the existing data during initial data synchronization. + + + + + It is assumed that the bidirectional logical replication between + node1 and node2 is already completed. + The nodes node1 and node2 has some + pre-existing data in table t1 that is synchronized in both the nodes. + + + + + Create a publication in node3: + +node3=# CREATE PUBLICATION pub_node3 FOR TABLE t1; +CREATE PUBLICATION + + + + Lock the required tables of node2 and + node3 in EXCLUSIVE mode until the + setup is completed. No need to lock the tables in node1 + as any data changes made will be synchronized while creating the + subscription with copy_data specified as + force. + + + + Create a subscription in 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 in 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 in node3 to subscribe to + node1. Use copy_data specified as + 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 in node3 to subscribe to + node2. Use copy_data specified as + 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 + node2. Any subsequent changes in one node will + replicate the changes to the other nodes. + + + + + Adding a new node when data is present in the new node + + + Adding a new node node3 to the existing + node1 and node2 when data is present + in the new node node3 is not possible. + + + + + + Generic steps to add a new node to the existing set of nodes + + 1. Create the required publication on the new node. + + + 2. Lock the required tables of the new node in EXCLUSIVE + mode until the setup is complete. This is required to prevent any + modifications from happening in the new node. If data modifications occur + after step-3, there is a chance that the modifications will be published to + the first node and then synchronized back to the new node while creating + subscription in step-5 resulting in inconsistent data. + + + 3. Create subscriptions on existing nodes pointing to publication on + the new node with origin parameter specified as + local and copy_data specified as + off. + + + 4. Lock the required tables of the existing nodes except the first node in + EXCLUSIVE mode until the setup is complete. This is + required to prevent any modifications from happening. If data modifications + occur, there is a chance that the modifications done in between step-5 and + step-6 will not be synchronized to the new node resulting in inconsistent + data. No need to lock the tables in the first node as any data changes + made will be synchronized while creating the subscription with + copy_data specified as force. + + + 5. Create a subscription on the new node pointing to publication on the + first node with origin parameter specified as + local and copy_data parameter + specified as force. + + + 6. Create subscriptions on the new node pointing to publications on the + remaining nodes with origin parameter specified as + local and copy_data parameter + specifiedas off. + + + + diff --git a/doc/src/sgml/ref/create_subscription.sgml b/doc/src/sgml/ref/create_subscription.sgml index a691f438c8..39c671c7f3 100644 --- a/doc/src/sgml/ref/create_subscription.sgml +++ b/doc/src/sgml/ref/create_subscription.sgml @@ -404,7 +404,10 @@ CREATE SUBSCRIPTION subscription_namecopy_data = force. + copy_data = force. Refer to the + on how + copy_data and origin can be used + in bidirectional replication. -- 2.32.0