PSA a test script that demonstrates all the documented steps for
setting up n-way bidirectional replication. These steps are the same
as those documented [1] on the new page "Bidirectional logical
replication".
This script works using the current latest v20* patch set. Each of the
sections of 31.11.1 - 31.11.5 (see below) can be run independently
(just edit the script and at the bottom uncomment the part you want to
test):
31.11.1. Setting bidirectional replication between two nodes
31.11.2. Adding a new node when there is no data in any of the nodes
31.11.3. Adding a new node when data is present in the existing nodes
31.11.4. Adding a new node when data is present in the new node
31.11.5. Generic steps for adding a new node to an existing set of nodes
~~
Some sample output is also attached.
------
[1]
https://www.postgresql.org/message-id/attachment/134464/v20-0004-Document-bidirectional-logical-replication-steps.patch
Kind Regards,
Peter Smith.
Fujitsu Australia
Clean up
pg_ctl: directory "data_N1" does not exist
pg_ctl: directory "data_N2" does not exist
pg_ctl: directory "data_N3" does not exist
pg_ctl: directory "data_N4" does not exist
rm: cannot remove ‘data_N1’: No such file or directory
rm: cannot remove ‘data_N2’: No such file or directory
rm: cannot remove ‘data_N3’: No such file or directory
rm: cannot remove ‘data_N4’: No such file or directory
Set up
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "en_AU.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
creating directory data_N1 ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Australia/Sydney
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
initdb: warning: enabling "trust" authentication for local connections
initdb: hint: You can change this by editing pg_hba.conf or using the option
-A, or --auth-local and --auth-host, the next time you run initdb.
Success. You can now start the database server using:
pg_ctl -D data_N1 -l logfile start
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "en_AU.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
creating directory data_N2 ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Australia/Sydney
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
initdb: warning: enabling "trust" authentication for local connections
initdb: hint: You can change this by editing pg_hba.conf or using the option
-A, or --auth-local and --auth-host, the next time you run initdb.
Success. You can now start the database server using:
pg_ctl -D data_N2 -l logfile start
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "en_AU.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
creating directory data_N3 ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Australia/Sydney
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
initdb: warning: enabling "trust" authentication for local connections
initdb: hint: You can change this by editing pg_hba.conf or using the option
-A, or --auth-local and --auth-host, the next time you run initdb.
Success. You can now start the database server using:
pg_ctl -D data_N3 -l logfile start
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "en_AU.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
creating directory data_N4 ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Australia/Sydney
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
initdb: warning: enabling "trust" authentication for local connections
initdb: hint: You can change this by editing pg_hba.conf or using the option
-A, or --auth-local and --auth-host, the next time you run initdb.
Success. You can now start the database server using:
pg_ctl -D data_N4 -l logfile start
waiting for server to start.... done
server started
waiting for server to start.... done
server started
waiting for server to start.... done
server started
waiting for server to start.... done
server started
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
***************************************************************************
TEST STEPS FOR 31.11.1. Setting bidirectional replication between two nodes
***************************************************************************
CREATE PUBLICATION
CREATE PUBLICATION
locking N1 tables
locking N2 tables
NOTICE: created replication slot "sub_node2_node1" on publisher
CREATE SUBSCRIPTION
NOTICE: created replication slot "sub_node1_node2" on publisher
CREATE SUBSCRIPTION
bdr setup complete
BEGIN
BEGIN
LOCK TABLE
LOCK TABLE
pg_sleep
----------
(1 row)
pg_sleep
----------
(1 row)
[1]- Done psql -p $port_N1 -c "BEGIN work; LOCK TABLE tbl
IN EXCLUSIVE MODE; select pg_sleep(60);"
[2]+ Done psql -p $port_N2 -c "BEGIN work; LOCK TABLE tbl
IN EXCLUSIVE MODE; select pg_sleep(60);"
No initial data for any nodes N1,N2
n1
----
(0 rows)
n2
----
(0 rows)
n3
----
(0 rows)
n4
----
(0 rows)
INSERT 0 1
INSERT 0 1
Incremental data N1(11),N2(21) should be shared
n1
----
11
21
(2 rows)
n2
----
11
21
(2 rows)
n3
----
(0 rows)
n4
----
(0 rows)
************************************************************************************
TEST STEPS FOR 31.11.3. Adding a new node when data is present in the existing
nodes
************************************************************************************
CREATE PUBLICATION
locking N2 tables
locking N3 tables
NOTICE: created replication slot "sub_node1_node3" on publisher
CREATE SUBSCRIPTION
NOTICE: created replication slot "sub_node2_node3" on publisher
CREATE SUBSCRIPTION
NOTICE: created replication slot "sub_node3_node1" on publisher
CREATE SUBSCRIPTION
NOTICE: created replication slot "sub_node3_node2" on publisher
CREATE SUBSCRIPTION
bdr setup complete
BEGIN
BEGIN
LOCK TABLE
LOCK TABLE
pg_sleep
----------
(1 row)
pg_sleep
----------
(1 row)
[1]- Done psql -p $port_N2 -c "BEGIN work; LOCK TABLE tbl
IN EXCLUSIVE MODE; select pg_sleep(60);"
[2]+ Done psql -p $port_N3 -c "BEGIN work; LOCK TABLE tbl
IN EXCLUSIVE MODE; select pg_sleep(60);"
Initial data of N1(11,21), N2(11,21) should be seen on N3
n1
----
11
21
(2 rows)
n2
----
11
21
(2 rows)
n3
----
11
21
(2 rows)
n4
----
(0 rows)
INSERT 0 1
INSERT 0 1
INSERT 0 1
Incremental data N1(13), N2(23) ,N3(33) should be shared
n1
----
11
13
21
23
33
(5 rows)
n2
----
11
13
21
23
33
(5 rows)
n3
----
11
13
21
23
33
(5 rows)
n4
----
(0 rows)
Initial data is on all nodes; node4 is not yet attached to the others
n1
----
11
13
21
23
33
(5 rows)
n2
----
11
13
21
23
33
(5 rows)
n3
----
11
13
21
23
33
(5 rows)
n4
----
(0 rows)
************************************************************************************
TEST STEPS FOR 31.11.5. Generic steps to add a new node to the existing set of
nodes
************************************************************************************
Step-1: Create a publication on the new node.
CREATE PUBLICATION
Step-2: Lock the required tables of the new node in EXCLUSIVE mode until the
setup is complete.
locking N4 tables
Step-3. Create subscriptions on existing nodes to the publication on the new
node with origin = local and copy_data = off.
NOTICE: created replication slot "sub_node1_node4" on publisher
CREATE SUBSCRIPTION
NOTICE: created replication slot "sub_node2_node4" on publisher
CREATE SUBSCRIPTION
NOTICE: created replication slot "sub_node3_node4" on publisher
CREATE SUBSCRIPTION
Step-4. Lock the required tables of the existing nodes except the first node in
EXCLUSIVE mode until the setup is complete.
locking N1 tables
locking N2 tables
locking N3 tables
Step-5. Create a subscription on the new node to the publication on the first
node with origin = local and copy_data = force.
NOTICE: created replication slot "sub_node4_node1" on publisher
CREATE SUBSCRIPTION
Step-6. Create subscriptions on the new node to publications on the remaining
nodes with origin = local and copy_data = off.
NOTICE: created replication slot "sub_node4_node2" on publisher
CREATE SUBSCRIPTION
NOTICE: created replication slot "sub_node4_node3" on publisher
CREATE SUBSCRIPTION
bdr setup complete
BEGIN
LOCK TABLE
pg_sleep
----------
(1 row)
BEGIN
LOCK TABLE
pg_sleep
----------
(1 row)
BEGIN
LOCK TABLE
pg_sleep
----------
(1 row)
BEGIN
LOCK TABLE
pg_sleep
----------
(1 row)
[1] Done psql -p $port_N4 -c "BEGIN work; LOCK TABLE tbl
IN EXCLUSIVE MODE; select pg_sleep(60);"
[2] Done psql -p $port_N1 -c "BEGIN work; LOCK TABLE tbl
IN EXCLUSIVE MODE; select pg_sleep(60);"
[3]- Done psql -p $port_N2 -c "BEGIN work; LOCK TABLE tbl
IN EXCLUSIVE MODE; select pg_sleep(60);"
[4]+ Done psql -p $port_N3 -c "BEGIN work; LOCK TABLE tbl
IN EXCLUSIVE MODE; select pg_sleep(60);"
Initial data (5 rows) of N1,N2,N3,N4 should be shared to all
n1
----
11
13
21
23
33
(5 rows)
n2
----
11
13
21
23
33
(5 rows)
n3
----
11
13
21
23
33
(5 rows)
n4
----
11
13
21
23
33
(5 rows)
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
Incremental data at N1(15),N2(25),N3(35),N4(45) should be shared
n1
----
11
13
15
21
23
25
33
35
45
(9 rows)
n2
----
11
13
15
21
23
25
33
35
45
(9 rows)
n3
----
11
13
15
21
23
25
33
35
45
(9 rows)
n4
----
11
13
15
21
23
25
33
35
45
(9 rows)
#!/bin/bash
port_N1=7651
port_N2=7652
port_N3=7653
port_N4=7654
common_tbl="create table tbl (a int primary key);"
copy_false="only_local=true,copy_data=false"
copy_force="only_local=true,copy_data=force"
function show_table_on_all_nodes()
{
echo $1
psql -p $port_N1 -c "select a as N1 from tbl order by a"
psql -p $port_N2 -c "select a as N2 from tbl order by a"
psql -p $port_N3 -c "select a as N3 from tbl order by a"
psql -p $port_N4 -c "select a as N4 from tbl order by a"
}
function cleanup_nodes()
{
echo 'Clean up'
pg_ctl stop -D data_N1
pg_ctl stop -D data_N2
pg_ctl stop -D data_N3
pg_ctl stop -D data_N4
rm -r data_N1 data_N2 data_N3 data_N4 *log
}
function setup_nodes()
{
echo 'Set up'
initdb -D data_N1
initdb -D data_N2
initdb -D data_N3
initdb -D data_N4
cat << EOF >> data_N1/postgresql.conf
wal_level = logical
port = $port_N1
max_logical_replication_workers=100
max_replication_slots=40
autovacuum = off
EOF
cat << EOF >> data_N2/postgresql.conf
wal_level = logical
port = $port_N2
max_logical_replication_workers=100
max_replication_slots=40
autovacuum = off
EOF
cat << EOF >> data_N3/postgresql.conf
wal_level = logical
port = $port_N3
max_logical_replication_workers=100
max_replication_slots=40
autovacuum = off
EOF
cat << EOF >> data_N4/postgresql.conf
wal_level = logical
port = $port_N4
max_logical_replication_workers=100
max_replication_slots=40
autovacuum = off
EOF
pg_ctl -D data_N1 start -w -l N1.log
pg_ctl -D data_N2 start -w -l N2.log
pg_ctl -D data_N3 start -w -l N3.log
pg_ctl -D data_N4 start -w -l N4.log
#pg_ctl -D data_N1 start -w
#pg_ctl -D data_N2 start -w
#pg_ctl -D data_N3 start -w
#pg_ctl -D data_N4 start -w
# Create the common table on all nodes
psql -p $port_N1 -c "$common_tbl"
psql -p $port_N2 -c "$common_tbl"
psql -p $port_N3 -c "$common_tbl"
psql -p $port_N4 -c "$common_tbl"
}
locker_N1=0
locker_N2=0
locker_N3=0
locker_N4=0
function lock_table_at_N1()
{
echo 'locking N1 tables'
psql -p $port_N1 -c "BEGIN work; LOCK TABLE tbl IN EXCLUSIVE MODE; select pg_sleep(60);" &
locker_N1=$!
}
function unlock_table_at_N1()
{
kill $locker_N1
echo 'unlocked N1 tables'
}
function lock_table_at_N2()
{
echo 'locking N2 tables'
psql -p $port_N2 -c "BEGIN work; LOCK TABLE tbl IN EXCLUSIVE MODE; select pg_sleep(60);" &
locker_N2=$!
}
function unlock_table_at_N2()
{
kill $locker_N2
}
function lock_table_at_N3()
{
echo 'locking N3 tables'
psql -p $port_N3 -c "BEGIN work; LOCK TABLE tbl IN EXCLUSIVE MODE; select pg_sleep(60);" &
locker_N3=$!
}
function unlock_table_at_N3()
{
kill $locker_N3
}
function lock_table_at_N4()
{
echo 'locking N4 tables'
psql -p $port_N4 -c "BEGIN work; LOCK TABLE tbl IN EXCLUSIVE MODE; select pg_sleep(60);" &
locker_N1=$!
}
function unlock_table_at_N4()
{
kill $locker_N4
}
# =====================================================================================================================
# See PG DOCS section 31.11.1
# The following steps demonstrate how to create a two-node bidirectional replication when there is no table data present in both nodes node1 and node2
function dotest_1()
{
echo '***************************************************************************'
echo 'TEST STEPS FOR 31.11.1. Setting bidirectional replication between two nodes'
echo '***************************************************************************'
#Create a publication in node1:
psql -p $port_N1 << EOF
CREATE PUBLICATION pub_node1 FOR TABLE tbl;
EOF
sleep 5s
#Create a publication in node2:
psql -p $port_N2 << EOF
CREATE PUBLICATION pub_node2 FOR TABLE tbl;
EOF
sleep 5s
#Lock the table t1 in node1 and node2 in EXCLUSIVE mode until the setup is completed.
lock_table_at_N1
lock_table_at_N2
#Create a subscription in node2 to subscribe to node1:
psql -p $port_N2 << EOF
CREATE SUBSCRIPTION sub_node2_node1
CONNECTION 'port=$port_N1'
PUBLICATION pub_node1
WITH (copy_data = off, origin = local);
EOF
sleep 10s
#Create a subscription in node1 to subscribe to node2:
psql -p $port_N1 << EOF
CREATE SUBSCRIPTION sub_node1_node2
CONNECTION 'port=$port_N2'
PUBLICATION pub_node2
WITH (copy_data = off, origin = local);
EOF
sleep 10s
echo 'bdr setup complete'
#Sleep 60s will surely give enough time for the table lock to be released
sleep 60s
# Confirm no initial data
show_table_on_all_nodes "No initial data for any nodes N1,N2"
#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.
psql -p $port_N1 -c "insert into tbl values (11);"
psql -p $port_N2 -c "insert into tbl values (21);"
sleep 10s
show_table_on_all_nodes "Incremental data N1(11),N2(21) should be shared"
}
# =====================================================================================================================
# See PG DOCS section 31.11.2
# The following steps demonstrate adding a new node node3 to the existing node1 and node2 when there is no t1 data in any of the nodes. This requires creating subscriptions in node1 and node2 to replicate the data from node3 and creating subscriptions in node3 to replicate data from node1 and node2.
function dotest_2()
{
# Note: These steps assume that the bidirectional logical replication between node1 and node2 is already completed.
dotest_1
psql -p $port_N1 -c "TRUNCATE TABLE tbl";
psql -p $port_N2 -c "TRUNCATE TABLE tbl";
sleep 10s
echo '***********************************************************************************'
echo 'TEST STEPS FOR 31.11.2. Adding a new node when there is no data in any of the nodes'
echo '***********************************************************************************'
#Create a publication in node3:
psql -p $port_N3 << EOF
CREATE PUBLICATION pub_node3 FOR TABLE tbl;
EOF
sleep 5s
# Lock table t1 in all the nodes node1, node2 and node3 in EXCLUSIVE mode until the setup is completed.
lock_table_at_N1
lock_table_at_N2
lock_table_at_N3
#Create a subscription in node1 to subscribe to node3:
psql -p $port_N1 << EOF
CREATE SUBSCRIPTION sub_node1_node3
CONNECTION 'port=$port_N3'
PUBLICATION pub_node3
WITH (copy_data = off, origin = local);
EOF
sleep 10s
#Create a subscription in node2 to subscribe to node3:
psql -p $port_N2 << EOF
CREATE SUBSCRIPTION sub_node2_node3
CONNECTION 'port=$port_N3'
PUBLICATION pub_node3
WITH (copy_data = off, origin = local);
EOF
sleep 10s
#Create a subscription in node3 to subscribe to node1:
psql -p $port_N3 << EOF
CREATE SUBSCRIPTION sub_node3_node1
CONNECTION 'port=$port_N1'
PUBLICATION pub_node1
WITH (copy_data = off, origin = local);
EOF
sleep 10s
#Create a subscription in node3 to subscribe to node2:
psql -p $port_N3 << EOF
CREATE SUBSCRIPTION sub_node3_node2
CONNECTION 'port=$port_N2'
PUBLICATION pub_node2
WITH (copy_data = off, origin = local);
EOF
sleep 10s
echo 'bdr setup complete'
#Sleep 60s will surely give enough time for the table lock to be released
sleep 60s
# Confirm no initial data
show_table_on_all_nodes "No initial data for any nodes N1,N2,N3"
# Now the bidirectional logical replication setup is complete between node1, node2 and node3. Incremental changes made in any node will be replicated to the other two nodes.
psql -p $port_N1 -c "insert into tbl values (12)"
psql -p $port_N2 -c "insert into tbl values (22);"
psql -p $port_N3 -c "insert into tbl values (32);"
sleep 10s
show_table_on_all_nodes "Incremental data N1(12),N2(22),N3(32) should be shared"
}
# =====================================================================================================================
# See PG DOCS section 31.11.3
# 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.
function dotest_3()
{
# 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 in both those nodes.
dotest_1
echo '************************************************************************************'
echo 'TEST STEPS FOR 31.11.3. Adding a new node when data is present in the existing nodes'
echo '************************************************************************************'
#Create a publication in node3:
psql -p $port_N3 << EOF
CREATE PUBLICATION pub_node3 FOR TABLE tbl;
EOF
sleep 5s
#Lock table t1 in node2 and node3 in EXCLUSIVE mode until the setup is completed. There is no need to lock table t1 in node1 because any data changes made will be synchronized while creating the subscription with copy_data = force.
lock_table_at_N2
lock_table_at_N3
#Create a subscription in node1 to subscribe to node3:
psql -p $port_N1 << EOF
CREATE SUBSCRIPTION sub_node1_node3
CONNECTION 'port=$port_N3'
PUBLICATION pub_node3
WITH (copy_data = off, origin = local);
EOF
sleep 10s
#Create a subscription in node2 to subscribe to node3:
psql -p $port_N2 << EOF
CREATE SUBSCRIPTION sub_node2_node3
CONNECTION 'port=$port_N3'
PUBLICATION pub_node3
WITH (copy_data = off, origin = local);
EOF
sleep 10s
#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:
psql -p $port_N3 << EOF
CREATE SUBSCRIPTION sub_node3_node1
CONNECTION 'port=$port_N1'
PUBLICATION pub_node1
WITH (copy_data = force, origin = local);
EOF
sleep 10s
#Create a subscription in node3 to subscribe to node2. Use copy_data = off because the initial table data would have been already copied in the previous step:
psql -p $port_N3 << EOF
CREATE SUBSCRIPTION sub_node3_node2
CONNECTION 'port=$port_N2'
PUBLICATION pub_node2
WITH (copy_data = off, origin = local);
EOF
sleep 10s
echo 'bdr setup complete'
#Sleep 60s will surely give enough time for the table lock to be released
sleep 60s
# Confirm that initial data is shared to the new node3
show_table_on_all_nodes "Initial data of N1(11,21), N2(11,21) should be seen on N3"
# Confirm that incremental data is shared to all nodes
psql -p $port_N1 -c "insert into tbl values (13)"
psql -p $port_N2 -c "insert into tbl values (23);"
psql -p $port_N3 -c "insert into tbl values (33);"
sleep 10s
show_table_on_all_nodes "Incremental data N1(13), N2(23) ,N3(33) should be shared"
}
# =====================================================================================================================
# See PG DOCS section 31.11.4
function dotest_4()
{
echo '******************************************************************************'
echo 'TEST STEPS FOR 31.11.4. Adding a new node when data is present in the new node'
echo '******************************************************************************'
echo 'Adding a new node when data is present in the new node tables is not supported.'
}
# =====================================================================================================================
# See PG DOCS section 31.11.5
function dotest_5()
{
#(Let's begin with 3 nodes with existing data and then add a 4th node with no data
dotest_3
sleep 10s
show_table_on_all_nodes "Initial data is on all nodes; node4 is not yet attached to the others"
echo '************************************************************************************'
echo 'TEST STEPS FOR 31.11.5. Generic steps to add a new node to the existing set of nodes'
echo '************************************************************************************'
echo 'Step-1: Create a publication on the new node.'
psql -p $port_N4 -c "CREATE PUBLICATION pub_node4 FOR TABLE tbl;"
sleep 5s
echo '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 in the new node because if data modifications occurred 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 the subscription in Step-5. This would result in inconsistent data).
lock_table_at_N4
echo '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)
psql -p $port_N1 << EOF
CREATE SUBSCRIPTION sub_node1_node4
CONNECTION 'port=$port_N4'
PUBLICATION pub_node4
WITH (copy_data = off, origin = local);
EOF
psql -p $port_N2 << EOF
CREATE SUBSCRIPTION sub_node2_node4
CONNECTION 'port=$port_N4'
PUBLICATION pub_node4
WITH (copy_data = off, origin = local);
EOF
psql -p $port_N3 << EOF
CREATE SUBSCRIPTION sub_node3_node4
CONNECTION 'port=$port_N4'
PUBLICATION pub_node4
WITH (copy_data = off, origin = local);
EOF
#Wait for data to be copied from the new node to existing nodes.
sleep 10s
echo '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 in node1 because any data changes made will be synchronized while creating the subscription with copy_data = force).
lock_table_at_N1
lock_table_at_N2
lock_table_at_N3
echo '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).
psql -p $port_N4 << EOF
CREATE SUBSCRIPTION sub_node4_node1
CONNECTION 'port=$port_N1'
PUBLICATION pub_node1
WITH (copy_data = force, origin = local);
EOF
echo '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).
psql -p $port_N4 << EOF
CREATE SUBSCRIPTION sub_node4_node2
CONNECTION 'port=$port_N2'
PUBLICATION pub_node2
WITH (copy_data = off, origin = local);
EOF
psql -p $port_N4 << EOF
CREATE SUBSCRIPTION sub_node4_node3
CONNECTION 'port=$port_N3'
PUBLICATION pub_node3
WITH (copy_data = off, origin = local);
EOF
sleep 10s
echo 'bdr setup complete'
#Sleep 60s will surely give enough time for the table lock to be released
sleep 60s
# Confirm that initial data is shared to the new node4
show_table_on_all_nodes "Initial data (5 rows) of N1,N2,N3,N4 should be shared to all"
# Confirm that incremental data is shared to all nodes
psql -p $port_N1 -c "insert into tbl values (15)"
psql -p $port_N2 -c "insert into tbl values (25);"
psql -p $port_N3 -c "insert into tbl values (35);"
psql -p $port_N4 -c "insert into tbl values (45);"
sleep 10s
show_table_on_all_nodes "Incremental data at N1(15),N2(25),N3(35),N4(45) should be shared"
}
# =====================================================================================================================
# Main
# =====================================================================================================================
cleanup_nodes
setup_nodes
# Test instructions: UNCOMMENT whatever test you want to run. They are all self-contained.
#dotest_1
#dotest_2
#dotest_3
#dotest_4
dotest_5
#!/bin/bash
echo 'Clean up'
pg_ctl stop -D data_N1
pg_ctl stop -D data_N2
pg_ctl stop -D data_N3
pg_ctl stop -D data_N4
pg_ctl stop -D data_N5
rm -r data_N1 data_N2 data_N3 data_N4 data_N5
