Postgresql BDR Setup

yum install http://packages.2ndquadrant.com/postgresql-bdr94-2ndquadrant/yum-repo-rpms/postgresql-bdr94-2ndquadrant-redhat-1.0-2.noarch.rpm

yum install https://dl.fedoraproject.org/pub/epel/epel-release-latest-6.noarch.rpm

yum update

yum install postgresql-bdr94-bdr

Initialize Node1 and 2 database

/usr/pgsql-9.4/bin/postgresql94-setup initdb

Setup Postgresql.conf and hba node 1

/var/lib/pgsql/9.4-bdr/data/postgresql.conf

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

    # These aren't required, but are useful for diagnosing problems

    #log_error_verbosity = verbose

    #log_min_messages = debug1

    #log_line_prefix = 'd=%d p=%p a=%a%q '

    # Useful options for playing with conflicts

    #bdr.default_apply_delay=2000   # milliseconds

    #bdr.log_conflicts_to_table=on

/var/lib/pgsql/9.4-bdr/data/pg_hba.conf

host    replication     postgres        10.211.55.2/24          trust

Setup Postgresql.conf and hba node 2

/var/lib/pgsql/9.4-bdr/data/postgresql.conf

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

    # These aren't required, but are useful for diagnosing problems

    #log_error_verbosity = verbose

    #log_min_messages = debug1

    #log_line_prefix = 'd=%d p=%p a=%a%q '

    # Useful options for playing with conflicts

    #bdr.default_apply_delay=2000   # milliseconds

    #bdr.log_conflicts_to_table=on

/var/lib/pgsql/9.4-bdr/data/pg_hba.conf

host    replication     postgres        10.211.55.1/24          trust

Start the Postgresql Server in both servers nodes

#service postgresql-94 start

#chkconfig postgresql-94 on

Create the database in node 1 and 2

#sudo su - postgres

For node 1

#createdb -p 5598 -U postgres bdrdemo

For node 2

createdb -p 5599 -U postgres bdrdemo

On the first node/instance in database "bdrdemo" as postgreSQL superuser, create the extensions necessary for BDR:

    psql -p 5598 -U postgres bdrdemo

       CREATE EXTENSION btree_gist;

       CREATE EXTENSION bdr;

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 5598) from the same SQL session as above on port 5598:

    SELECT bdr.bdr_group_create(

      local_node_name := 'node1',

      node_external_dsn := 'port=5598 dbname=bdrdemo host=10.211.55.1’

);

    

To ensure that the node is ready to replicate, run this function from the same SQL session as above on port 5598:

    SELECT bdr.bdr_node_join_wait_for_ready();

    

On the second node/instance on port 5599 in database "bdrdemo" as postgreSQL superuser, create the extensions necessary for BDR:

    psql -p 5599 -U postgres bdrdemo

       CREATE EXTENSION btree_gist;

       CREATE EXTENSION bdr;

    

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:

    SELECT bdr.bdr_group_join(

      local_node_name := 'node2',

      node_external_dsn := 'port=5599 dbname=bdrdemo host=10.211.55.2’,

      join_using_dsn := 'port=5598 dbname=bdrdemo host=10.211.55.1’

);

    

To ensure that the node/instance is ready to replicate, run this function from the same SQL session as above on port 5599:

    SELECT bdr.bdr_node_join_wait_for_ready();

    

Create a table and insert rows from your first server node/instance:

    psql -p 5598 -U postgres bdrdemo

      CREATE TABLE t1bdr (c1 INT, PRIMARY KEY (c1));

      INSERT INTO t1bdr VALUES (1);

      INSERT INTO t1bdr VALUES (2);

      -- you will see two rows

      SELECT * FROM t1bdr;

    

Check that the rows are there on your second node/instance. Now, delete a row:

    psql -p 5599 -U postgres bdrdemo

      -- you will see two rows

      SELECT * FROM t1bdr;

      DELETE FROM t1bdr WHERE c1 = 2;

      -- you will see one row

      SELECT * FROM t1bdr;

    

Check that one row has been deleted from the first node/instance on node 2:

    psql -p 5598 -U postgres bdrdemo

      -- you will see one row

      SELECT * FROM t1bdr;

For Monitoring nodes on the Master server

SELECT * FROM bdr.bdr_nodes;

node_sysid	node_timeline	node_dboid	node_status	node_name	node_local_dsn	node_init_from_dsn
6.16133E+18	1	16385	r	node1	port=5598 dbname=bdrdemo host=10.211.55.1
6.16132E+18	1	17160	r	node2	port=5599 dbname=bdrdemo host=10.211.55.2	port=5598 dbname=bdrdemo host=10.211.55.1

Here is an example of a SELECT from bdr.bdr_nodes that indicates that one node is ready (r), one node has been removed/killed (k), and one node is initializing (i):

    SELECT * FROM bdr.bdr_nodes;

         node_sysid      | node_timeline | node_dboid | node_status | node_name |      node_local_dsn      |    node_init_from_dsn

    ---------------------+---------------+------------+-------------+-----------+--------------------------+--------------------------

     6125823754033780536 |             1 |      16385 | r           | node1     | port=5598 dbname=bdrdemo |

     6125823714403985168 |             1 |      16386 | k           | node2     | port=5599 dbname=bdrdemo | port=5598 dbname=bdrdemo

     6125847382076805699 |             1 |      16386 | i           | node3     | port=6000 dbname=bdrdemo | port=5598 dbname=bdrdemo

    (3 rows)