Testing World: 2017-06-04

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

----------------------------------------------------------------------------------------

Testing World

Tuesday 6 June 2017

Bi-Directional Replication for PostgreSQL 9.4 on Centos 64bit using 2ndQuadrant

Select Language