Bi-Directional Replication for PostgreSQL 9.4 on Centos 64bit using 2ndQuadrant
Node 1: 192.168.1.10
Node 2: 192.168.1.11
Step 2: Installing BDR Node1 and 2 database
yum install http://packages.2ndquadrant.com/postgresql-bdr94-2ndquadrant/yum-repo-rpms/postgresql-bdr94-2ndquadrant-redhat-latest.noarch.rpm
Step 3: Install the BDR packages
yum install postgresql-bdr94-bdr
Step 4: Initialize Node1 and 2 database
/usr/pgsql-9.4/bin/postgresql94-setup initdb
Step 5: Edit the postgresql.conf (/var/lib/pgsql/9.4-bdr/data/postgresql.conf) file for both nodes/instances:
Node 1
listen_addresses = '*'
port = 5598
shared_preload_libraries = 'bdr'
wal_level = 'logical'
track_commit_timestamp = on
max_connections = 100
max_wal_senders = 10
max_replication_slots = 10
# Make sure there are enough background worker slots for BDR to run
max_worker_processes = 10
Node 2
listen_addresses = '*'
port = 5599
shared_preload_libraries = 'bdr'
wal_level = 'logical'
track_commit_timestamp = on
max_connections = 100
max_wal_senders = 10
max_replication_slots = 10
# Make sure there are enough background worker slots for BDR to run
max_worker_processes = 10
Step 6: Set the IP addresse in file (/var/lib/pgsql/9.4-bdr/data/pg_hba.conf)
Node 1:
host replication postgres 123.12.1.10/32 trust
host replication postgres 123.12.1.11/32 trust
host all postgres 123.12.1.10/32 trust
host all postgres 123.12.1.11/32 trust
Node 2:
host replication postgres 123.12.1.11/32 trust
host replication postgres 123.12.1.10/32 trust
host all postgres 123.12.1.11/32 trust
host all postgres 123.12.1.10/32 trust
Step 7: Reboot the server and Restart the Postgres and disable IPTables algorithm:
Disable IPtable using following command to get access to outside world.
'iptables -F'
/etc/init.d/postgresql-9.4 status
/etc/init.d/postgresql-9.4 stop
/etc/init.d/postgresql-9.4 start
/etc/init.d/postgresql-9.4 restart
Login to postgres `su - postgres` and then enter the command.
Step 8: Create DB on Node 1
createdb -p 5432 -U postgres dbDuiIVR
Note: One can use different port on two different nodes. i.e. here you may want to use 5598
Step 9: Create DB on Node 2
createdb -p 5432 -U postgres dbDuiIVR
Note: One can use different port on two different nodes. i.e. here you may want to use 5599
Step 10: Enabling BDR in SQL sessions for both of your nodes/instances
Node 1:
psql -p 5432 -U postgres dbDuiIVR
CREATE EXTENSION btree_gist;
CREATE EXTENSION bdr;
Node -2:
psql -p 5432 -U postgres dbDuiIVR
CREATE EXTENSION btree_gist;
CREATE EXTENSION bdr;
Step 11: Then you run a function that identifies a BDR group that delineates a connection string for other nodes to communicate
with (for the first node, we will use port 5432) from the same SQL session as above on port 5432:
Node 1:
SELECT bdr.bdr_group_create(
local_node_name := 'node1',
node_external_dsn := 'port=5432 dbname=dbDuiIVR host=192.168.1.10'
);
Note: To make sure, issue following query
SELECT * FROM bdr.bdr_nodes;
Step 12: To ensure that the node is ready to replicate, run this function from the same SQL session as above on port 5432:
Node 1:
SELECT bdr.bdr_node_join_wait_for_ready();
Step 13: Then run a function that joins this node/instance to your BDR group you created above (for the second node, we will use port 5599)
from the same SQL session as above on port 5599:
Node 2:
SELECT bdr.bdr_group_join(
local_node_name := 'node2',
node_external_dsn := 'port=5432 dbname=dbDuiIVR host=192.168.1.11',
join_using_dsn := 'port=5432 dbname=dbDuiIVR host=192.168.1.10'
);
Note: To make sure, issue following query
SELECT * FROM bdr.bdr_nodes;
Step 14: To ensure that the node/instance is ready to replicate, run this function from the same SQL session as above on port 5599:
Node 2:
SELECT bdr.bdr_node_join_wait_for_ready();
Step 15: Create a table and insert rows from your first node/instance:
Node 1:
CREATE TABLE t2bdr (c1 INT, PRIMARY KEY (c1));
INSERT INTO t1bdr VALUES (1);
INSERT INTO t1bdr VALUES (2);
-- you will see two rows
SELECT * FROM t1bdr;
Step 16: Check that the rows are there on your second node/instance. Now, delete a row:
Node -2:
-- you will see two rows
SELECT * FROM t1bdr;
DELETE FROM t1bdr WHERE c1 = 2;
-- you will see one row
SELECT * FROM t1bdr;
Step 18: Check that one row has been deleted from the first node/instance::
-- you will see one row
SELECT * FROM t1bdr;
----------------------------------------------------------------------------------------
How to remove BDR postgreSQL from the same server. (This has been done on 62 & 63)
1) First check what rpm are installed on the server (192.168.1.10)
[root@linsys1b ~]# rpm -qa | grep postgres
postgresql-bdr94-bdr-1.0.2-1_2ndQuadrant.el6.x86_64
postgresql-bdr94-2ndquadrant-redhat-1.0-3.noarch
postgresql-bdr94-contrib-9.4.12_bdr1-1_2ndQuadrant.el6.x86_64
postgresql-bdr94-9.4.12_bdr1-1_2ndQuadrant.el6.x86_64
postgresql-bdr94-libs-9.4.12_bdr1-1_2ndQuadrant.el6.x86_64
postgresql-bdr94-server-9.4.12_bdr1-1_2ndQuadrant.el6.x86_64
2) To remove above rpm, use following command,
rpm -e postgresql-bdr94-bdr-1.0.2-1_2ndQuadrant.el6.x86_64
rpm -e postgresql-bdr94-contrib-9.4.12_bdr1-1_2ndQuadrant.el6.x86_64
rpm -e postgresql-bdr94-server-9.4.12_bdr1-1_2ndQuadrant.el6.x86_64
rpm -e postgresql-bdr94-2ndquadrant-redhat-1.0-3.noarch
yum remove postgresql
rpm -e postgresql-bdr94-libs-9.4.12_bdr1-1_2ndQuadrant.el6.x86_64
----------------------------------------------------------------------------------------
