On Thu, Apr 7, 2022 at 4:03 PM kuroda.hay...@fujitsu.com <kuroda.hay...@fujitsu.com> wrote: > > Dear Peter, > > > FYI, here is a test script that is using the current patch (v6) to > > demonstrate a way to share table data between different numbers of > > nodes (up to 5 of them here). > > Thanks for sharing your script! It's very helpful for us. > > While reading your script, however, I had a question about it. > Line 121-122, you defined subscriptions for 2-nodes cluster: > > psql -p $port_N1 -c "create subscription sub12 connection 'port=$port_N2' > publication pub2 with ($copy_force);" > psql -p $port_N2 -c "create subscription sub21 connection 'port=$port_N1' > publication pub1 with ($copy_force);" > > But I was not sure it works well. > N2 already have shared data from N1 when subscription sub21 is created. > Did you assume that the initial copying is not so quick and > data synchronization will be not done when creating sub21?
Oops. Good catch. Although the 2-way test was working OK for me, I think that it worked only because of lucky timing. e.g. When I put a delay between those 2 subscriptions then the 2nd one would cause the PK violation that probably you were anticipating would happen. I have modified the 2-way example to use the same truncate pattern as others. PSA the fixed test.sh script and accompanying files. ------ Kind Regards, Peter Smith. Fujitsu Australia
Clean up
waiting for server to shut down.... done
server stopped
waiting for server to shut down.... done
server stopped
waiting for server to shut down.... done
server stopped
waiting for server to shut down.... done
server stopped
waiting for server to shut down.... done
server stopped
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
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
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
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
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
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_N5 ... 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
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_N5 -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
waiting for server to start.... done
server started
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
****************************************
Initial table data for all nodes
****************************************
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
Initial data on all nodes
n1
----
10
(1 row)
n2
----
20
(1 row)
n3
----
30
(1 row)
n4
----
40
(1 row)
n5
----
50
(1 row)
****************************************
Share table data for nodes N1,N2
****************************************
CREATE PUBLICATION
CREATE PUBLICATION
NOTICE: created replication slot "sub12" on publisher
CREATE SUBSCRIPTION
ALTER PUBLICATION
TRUNCATE TABLE
NOTICE: created replication slot "sub21" on publisher
CREATE SUBSCRIPTION
ALTER PUBLICATION
Initial data of N1,N2 should be shared
n1
----
10
20
(2 rows)
n2
----
10
20
(2 rows)
n3
----
30
(1 row)
n4
----
40
(1 row)
n5
----
50
(1 row)
INSERT 0 1
INSERT 0 1
Data inserted at N1,N2 should be shared
n1
----
10
12
20
22
(4 rows)
n2
----
10
12
20
22
(4 rows)
n3
----
30
(1 row)
n4
----
40
(1 row)
n5
----
50
(1 row)
****************************************
Share table data for nodes N1,N2,N3
****************************************
CREATE PUBLICATION
NOTICE: created replication slot "sub13" on publisher
CREATE SUBSCRIPTION
NOTICE: created replication slot "sub23" on publisher
CREATE SUBSCRIPTION
ALTER PUBLICATION
TRUNCATE TABLE
NOTICE: created replication slot "sub31" on publisher
CREATE SUBSCRIPTION
NOTICE: created replication slot "sub32" on publisher
CREATE SUBSCRIPTION
ALTER PUBLICATION
Initial data of N1,N2,N3 should be shared
n1
----
10
12
20
22
30
(5 rows)
n2
----
10
12
20
22
30
(5 rows)
n3
----
10
12
20
22
30
(5 rows)
n4
----
40
(1 row)
n5
----
50
(1 row)
INSERT 0 1
INSERT 0 1
INSERT 0 1
Data inserted at N1,N2,N3 should be shared
n1
----
10
12
13
20
22
23
30
33
(8 rows)
n2
----
10
12
13
20
22
23
30
33
(8 rows)
n3
----
10
12
13
20
22
23
30
33
(8 rows)
n4
----
40
(1 row)
n5
----
50
(1 row)
****************************************
Share table data for nodes N1,N2,N3,N4
****************************************
CREATE PUBLICATION
NOTICE: created replication slot "sub14" on publisher
CREATE SUBSCRIPTION
NOTICE: created replication slot "sub24" on publisher
CREATE SUBSCRIPTION
NOTICE: created replication slot "sub34" on publisher
CREATE SUBSCRIPTION
ALTER PUBLICATION
TRUNCATE TABLE
NOTICE: created replication slot "sub41" on publisher
CREATE SUBSCRIPTION
NOTICE: created replication slot "sub42" on publisher
CREATE SUBSCRIPTION
NOTICE: created replication slot "sub43" on publisher
CREATE SUBSCRIPTION
ALTER PUBLICATION
Initial data of N1,N2,N3,N4 should be shared
n1
----
10
12
13
20
22
23
30
33
40
(9 rows)
n2
----
10
12
13
20
22
23
30
33
40
(9 rows)
n3
----
10
12
13
20
22
23
30
33
40
(9 rows)
n4
----
10
12
13
20
22
23
30
33
40
(9 rows)
n5
----
50
(1 row)
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
Data inserted at N1,N2,N3,N4 should be shared
n1
----
10
12
13
14
20
22
23
24
30
33
34
40
44
(13 rows)
n2
----
10
12
13
14
20
22
23
24
30
33
34
40
44
(13 rows)
n3
----
10
12
13
14
20
22
23
24
30
33
34
40
44
(13 rows)
n4
----
10
12
13
14
20
22
23
24
30
33
34
40
44
(13 rows)
n5
----
50
(1 row)
****************************************
Share table data for nodes N1,N2,N3,N4,N5
****************************************
CREATE PUBLICATION
NOTICE: created replication slot "sub15" on publisher
CREATE SUBSCRIPTION
NOTICE: created replication slot "sub25" on publisher
CREATE SUBSCRIPTION
NOTICE: created replication slot "sub35" on publisher
CREATE SUBSCRIPTION
NOTICE: created replication slot "sub45" on publisher
CREATE SUBSCRIPTION
ALTER PUBLICATION
TRUNCATE TABLE
NOTICE: created replication slot "sub51" on publisher
CREATE SUBSCRIPTION
NOTICE: created replication slot "sub52" on publisher
CREATE SUBSCRIPTION
NOTICE: created replication slot "sub53" on publisher
CREATE SUBSCRIPTION
NOTICE: created replication slot "sub54" on publisher
CREATE SUBSCRIPTION
ALTER PUBLICATION
Initial data of N1,N2,N3,N4,N5 should be shared
n1
----
10
12
13
14
20
22
23
24
30
33
34
40
44
50
(14 rows)
n2
----
10
12
13
14
20
22
23
24
30
33
34
40
44
50
(14 rows)
n3
----
10
12
13
14
20
22
23
24
30
33
34
40
44
50
(14 rows)
n4
----
10
12
13
14
20
22
23
24
30
33
34
40
44
50
(14 rows)
n5
----
10
12
13
14
20
22
23
24
30
33
34
40
44
50
(14 rows)
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
Data inserted at N1,N2,N3,N4,N5 should be shared
n1
----
10
12
13
14
15
20
22
23
24
25
30
33
34
35
40
44
45
50
55
(19 rows)
n2
----
10
12
13
14
15
20
22
23
24
25
30
33
34
35
40
44
45
50
55
(19 rows)
n3
----
10
12
13
14
15
20
22
23
24
25
30
33
34
35
40
44
45
50
55
(19 rows)
n4
----
10
12
13
14
15
20
22
23
24
25
30
33
34
35
40
44
45
50
55
(19 rows)
n5
----
10
12
13
14
15
20
22
23
24
25
30
33
34
35
40
44
45
50
55
(19 rows)
N2.log
Description: Binary data
N3.log
Description: Binary data
#!/bin/bash
port_N1=7651
port_N2=7652
port_N3=7653
port_N4=7654
port_N5=7655
common_tbl="create table tbl (a int primary key);"
copy_false="subscribe_local_only=true,copy_data=false"
copy_force="subscribe_local_only=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"
psql -p $port_N5 -c "select a as N5 from tbl order by a"
}
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 *log
echo 'Set up'
initdb -D data_N1
initdb -D data_N2
initdb -D data_N3
initdb -D data_N4
initdb -D data_N5
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
cat << EOF >> data_N5/postgresql.conf
wal_level = logical
port = $port_N5
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_N5 start -w -l N5.log
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"
psql -p $port_N5 -c "$common_tbl"
# =====================================================================================================================
echo '****************************************'
echo 'Initial table data for all nodes'
echo '****************************************'
# Insert some initial data for the table at each node
psql -p $port_N1 -c "insert into tbl values (10);"
psql -p $port_N2 -c "insert into tbl values (20);"
psql -p $port_N3 -c "insert into tbl values (30);"
psql -p $port_N4 -c "insert into tbl values (40);"
psql -p $port_N5 -c "insert into tbl values (50);"
sleep 10s
show_table_on_all_nodes "Initial data on all nodes"
# =====================================================================================================================
echo '****************************************'
echo 'Share table data for nodes N1,N2'
echo '****************************************'
# setup the pub/sub to join N1 and N2
psql -p $port_N1 -c "create publication pub1 for table tbl;"
psql -p $port_N2 -c "create publication pub2 for table tbl;"
# N1 copies N2 initial data
psql -p $port_N1 -c "create subscription sub12 connection 'port=$port_N2' publication pub2 with ($copy_force);"
sleep 5s
# Truncate the N2 data but do not replicate the truncate.
# Subscribe to N1 (this will give N2 its initial data back using copy_data=force)
psql -p $port_N2 << EOF
alter publication pub2 set (publish='insert,update,delete');
truncate tbl;
create subscription sub21 connection 'port=$port_N1' publication pub1 with ($copy_force);
alter publication pub2 set (publish='insert,update,delete,truncate');
EOF
sleep 10s
show_table_on_all_nodes "Initial data of N1,N2 should be shared"
# Insert some more data at every node to see that it is replicated everywhere
psql -p $port_N1 -c "insert into tbl values (12);"
psql -p $port_N2 -c "insert into tbl values (22);"
sleep 10s
show_table_on_all_nodes "Data inserted at N1,N2 should be shared"
# =====================================================================================================================
echo '****************************************'
echo 'Share table data for nodes N1,N2,N3'
echo '****************************************'
# setup the pub/sub to join N3 to existing group of N1,N2
psql -p $port_N3 -c "create publication pub3 for table tbl;"
# N1 copies N3 initial data
# N2 copies N3 initial data
psql -p $port_N1 -c "create subscription sub13 connection 'port=$port_N3' publication pub3 with ($copy_force);"
psql -p $port_N2 -c "create subscription sub23 connection 'port=$port_N3' publication pub3 with ($copy_force);"
sleep 5s
# Truncate the N3 data but do not replicate the truncate.
# Subscribe to N1 (this will give N3 its initial data back using copy_data=force)
# Subscribe to N2 (N3 already got the N1/N2 initial data from N1 so copy_data=false here)
psql -p $port_N3 << EOF
alter publication pub3 set (publish='insert,update,delete');
truncate tbl;
create subscription sub31 connection 'port=$port_N1' publication pub1 with ($copy_force);
create subscription sub32 connection 'port=$port_N2' publication pub2 with ($copy_false);
alter publication pub3 set (publish='insert,update,delete,truncate');
EOF
sleep 10s
show_table_on_all_nodes "Initial data of N1,N2,N3 should be shared"
# Insert some more data at every node to see that it is replicated everywhere
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 "Data inserted at N1,N2,N3 should be shared"
# =====================================================================================================================
echo '****************************************'
echo 'Share table data for nodes N1,N2,N3,N4'
echo '****************************************'
# setup the pub/sub to join N4 to existing group of N1,N2,N3
psql -p $port_N4 -c "create publication pub4 for table tbl;"
# N1 copies N4 initial data
# N2 copies N4 initial data
# N3 copies N4 initial data
psql -p $port_N1 -c "create subscription sub14 connection 'port=$port_N4' publication pub4 with ($copy_force);"
psql -p $port_N2 -c "create subscription sub24 connection 'port=$port_N4' publication pub4 with ($copy_force);"
psql -p $port_N3 -c "create subscription sub34 connection 'port=$port_N4' publication pub4 with ($copy_force);"
sleep 5s
# Truncate the N4 data but do not replicate the truncate.
# Subscribe to N1 (this will give N4 its initial data back using copy_data=force)
# Subscribe to N2 (N4 already got the N1/N2/N3 initial data from N1 so copy_data=false here)
# Subscribe to N3 (N4 already got the N1/N2/N3 initial data from N1 so copy_data=false here)
psql -p $port_N4 << EOF
alter publication pub4 set (publish='insert,update,delete');
truncate tbl;
create subscription sub41 connection 'port=$port_N1' publication pub1 with ($copy_force);
create subscription sub42 connection 'port=$port_N2' publication pub2 with ($copy_false);
create subscription sub43 connection 'port=$port_N3' publication pub3 with ($copy_false);
alter publication pub4 set (publish='insert,update,delete,truncate');
EOF
sleep 10s
show_table_on_all_nodes "Initial data of N1,N2,N3,N4 should be shared"
# Insert some more data at every node to see that it is replicated everywhere
psql -p $port_N1 -c "insert into tbl values (14)"
psql -p $port_N2 -c "insert into tbl values (24);"
psql -p $port_N3 -c "insert into tbl values (34);"
psql -p $port_N4 -c "insert into tbl values (44);"
sleep 10s
show_table_on_all_nodes "Data inserted at N1,N2,N3,N4 should be shared"
# =====================================================================================================================
echo '****************************************'
echo 'Share table data for nodes N1,N2,N3,N4,N5'
echo '****************************************'
# setup the pub/sub to join N5 to existing group of N1,N2,N3,N4
psql -p $port_N5 -c "create publication pub5 for table tbl;"
# N1 copies N5 initial data
# N2 copies N5 initial data
# N3 copies N5 initial data
# N4 copies N5 initial data
psql -p $port_N1 -c "create subscription sub15 connection 'port=$port_N5' publication pub5 with ($copy_force);"
psql -p $port_N2 -c "create subscription sub25 connection 'port=$port_N5' publication pub5 with ($copy_force);"
psql -p $port_N3 -c "create subscription sub35 connection 'port=$port_N5' publication pub5 with ($copy_force);"
psql -p $port_N4 -c "create subscription sub45 connection 'port=$port_N5' publication pub5 with ($copy_force);"
sleep 10s
# Truncate the N5 data but do not replicate the truncate.
# Subscribe to N1 (this will give N5 its initial data back using copy_data=force)
# Subscribe to N2 (N5 already got the N1/N2/N3/N4 initial data from N1 so copy_data=false here)
# Subscribe to N3 (N5 already got the N1/N2/N3/N4 initial data from N1 so copy_data=false here)
# Subscribe to N4 (N5 already got the N1/N2/N3/N4 initial data from N1 so copy_data=false here)
psql -p $port_N5 << EOF
alter publication pub5 set (publish='insert,update,delete');
truncate tbl;
create subscription sub51 connection 'port=$port_N1' publication pub1 with ($copy_force);
create subscription sub52 connection 'port=$port_N2' publication pub2 with ($copy_false);
create subscription sub53 connection 'port=$port_N3' publication pub3 with ($copy_false);
create subscription sub54 connection 'port=$port_N4' publication pub4 with ($copy_false);
alter publication pub5 set (publish='insert,update,delete,truncate');
EOF
sleep 10s
show_table_on_all_nodes "Initial data of N1,N2,N3,N4,N5 should be shared"
# Insert some more data at every node to see that it is replicated everywhere
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);"
psql -p $port_N5 -c "insert into tbl values (55);"
sleep 10s
show_table_on_all_nodes "Data inserted at N1,N2,N3,N4,N5 should be shared"
N5.log
Description: Binary data
N4.log
Description: Binary data
N1.log
Description: Binary data
