Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

How to setup Bi-Directional Replication (BDR)

Logical decoding is a feature added to PostgreSQL 9.4. It’s the process of extracting all persistent changes to a database from the write-ahead log and translating it into an application-specifc form such as stream of tuples or SQL commands.

Bi-Directional Replication (BDR) is built on top of logical decoding and provides asynchronous multi-master logical replication.

It lets you create a BDR group and write to any server in it, sending the changes  the changes row-by-row to all other servers in the group AFTER they’ve been committed.

Many of the features it relies on are already on PostgreSQL, but some of them weren’t added to 9.4 or 9.5 releases, so to enable BDR you’ll need a patched version of the binaries.

(Uni-Directional Replication, or UDR, works on the official unpatched PostrgreSQL distribution, but it has the downside of not replicating DDL statements)

It also lets you replicate databases across major versions, for zero downtime database upgrade. Since it’s logical, it lets you replicate across differente architectures as well.

Letting you write to any node is a major advantage over any of the more traditional PostgreSQL replication setups, but there are a few things to worry about first.

As the docs say, BDR is a “loosely coupled shared-nothing multi-master design”. So there are a few things you need to beware of:

 Some anomalies may be introduced by asynchronous multi-master replication;
 Apps are free to write to any node, but it must be carefully designed to not introduce conflicts or to be able to deal with them
(for instance, the app may write a parent record on Node 1 and then try to write its child record on Node 2, just to find out that the parent hasn’t been replicated yet);
You may enable synchronous_commit, but it may slow down your database to a halt, depending on network environment and how far apart are your nodes. Even then, you may still have conflicts to resolve.
Although it has some quirks, BDR enables you to set geographically distributed application, enables you to do rolling upgrades and is particularly good when the data is clustered by user group or location. Also, there’s nothing stopping you from setting up a BDR and only writing to one node, having the other as an easy to configure hot-standby.

Here’s a quick guide on how to install BDR-enabled PostgreSQL on a two node configuration:

Proposed architecture: two nodes running CentOS 7
Node 1:
Name: VINPG1301
IP Address:

Node 2:
Name: VINPG2301
IP Address:

First thing, remove any PostgreSQL installation you might have:
yum remove postgresql94\*
Install the patched PostgreSQL 9.4 BDR
sudo yum install http://packages.2ndquadrant.com/postgresql-bdr94-2ndquadrant/yum-repo-rpms/postgresql-bdr94-2ndquadrant-redhat-1.0-2.noarch.rpm
sudo yum check-update
sudo yum install postgresql-bdr94-bdr
On both nodes as postgres user, add the following line to your .bash_profile :
export PGDATA
export PATH=/usr/pgsql-9.4/bin:$PATH
This will make your test database go to the newly created /var/lib/pgsql/9.4-bdr/data folder, but you may change that to any foler you like.

On your data folder, add the following lines to your postgresql.conf:
wal_level = logical 
shared_preload_libraries = 'bdr'
track_commit_timestamp = on
#the following configuration may change depending on the number of nodes you have
max_connections = 100
max_wal_senders = 10
max_replication_slots = 10
max_worker_processes = 10
port = 5598 # change to whatever port you need
# synchronous_commit = on # uncomment this line if you want synchronous commit
# bdr.synchronous_commit = on # uncomment this line if you want synchronous commit
On your data folder, add the following lines to your pg_hba.conf:
host replication postgres trust
host all postgres trust
As postgres user, execute the following commands on both nodes:
pg_ctl -o "-p 5598" -w start
createdb -p 5598 -U postgres bdrdemo
Again, on both nodes, create the BDR extensions:
 psql -p 5598 -U postgres bdrdemo
On Node 1, execute the following commands:
 psql -p 5598 -U postgres bdrdemo
 SELECT bdr.bdr_group_create(
 local_node_name := 'node1',
 node_external_dsn := 'host= port=5598 dbname=bdrdemo'
 SELECT bdr.bdr_node_join_wait_for_ready();
On Node 2, run the following command to join the BDR group:
 psql -p 5598 -U postgres bdrdemo
 SELECT bdr.bdr_group_join(
 local_node_name := 'node2',
 node_external_dsn := 'host= port=5598 dbname=bdrdemo',
 join_using_dsn := 'host= port=5598 dbname=bdrdemo'
 SELECT bdr.bdr_node_join_wait_for_ready();
To test your BDR system, go to Node 1:
 psql -p 5598 -U postgres bdrdemo
 -- you will see two rows
 SELECT * FROM t1bdr;
Then go to Node 2:
 psql -p 5598 -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;
And back to Node 1:
psql -p 5598 -U postgres bdrdemo
 -- you will see one row
 SELECT * FROM t1bdr;
An alternate way to do that would be using the bdr_init_copy command:
bdr_init_copy -D /var/lib/pgsql/9.4-bdr/data -n node2 -d 'host= port=5598 dbname=bdrdemo' -U postgres --local-dbname='host= port=5598 dbname=bdrdemo'
Anyway you do it, BDR will make an initial copy of the node you’ve selected to join and synchronize all changes after it’s done… For the duration of the initial copy, all pre-existing nodes in the group will store their WAL and it may fill the FS, crashing the databases. One shoud be aware of the quality of the network connection between the new node and the one it’s joining, choosing the closest node to new one to perform the copy.


Popular posts from this blog

rman interview and scenario based questions and answer and also rman(backup and recovery) discussions panel

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

7 Steps to configure BDR replication in postgresql

PostgreSQL Introduction