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).
The script starts off with just 2-way sharing (nodes N1, N2), then expands to 3-way sharing (nodes N1, N2, N3), then 4-way sharing (nodes N1, N2, N3, N4), then 5-way sharing (nodes N1, N2, N3, N4, N5). As an extra complication, for this test, all 5 nodes have different initial table data, which gets replicated to the others whenever each new node joins the existing share group. PSA. ------ 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 NOTICE: created replication slot "sub21" on publisher CREATE SUBSCRIPTION 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)
#!/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 # N2 copies N1 initial data 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);" 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"
N2.log
Description: Binary data
N1.log
Description: Binary data
N5.log
Description: Binary data
N4.log
Description: Binary data
N3.log
Description: Binary data