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)