PostgreSQL Bi-Directional Replication

A while ago I wanted to explore the options i had for multi master replication in postgres, I have previously worked with Oracle MMR and to be honest i don't like the idea of mmr, mostly because of the all conflicts that you sometime get, most of the times these conflicts are easy to resolve though and mmr, or in our case BDR can be a very nice way to scale up your database writes.
So, while searching i came across a project called postgresql BDR (bi-directional replication) developed by 2ndQuadrant, it allows users to create a geographically distributed asynchronous multi-master database using Logical Log Streaming Replication based on the changeset extraction feature introduced in PostgreSQL 9.4.
This post is about showing how to set this up.
For this setup i used 2 debian VMs (debian-7.5.0-amd64-netinst.iso)
the extra packages i installed were :
git, mc, joe,sudo, curl, libreadline-dev, zlib1g-dev, bison, flex
(by the way, comments about joe wont be tolerated !!)

With the vms ready and a user postgres1 created :

git clone git://git.postgresql.org/git/2ndquadrant_bdr.git

cd 2ndquadrant_bdr

git checkout bdr/0.6

./configure --prefix=$HOME/bdr

make

make install

cd ~/2ndquadrant_bdr/contrib/btree_gist && make && make install && cd ../../contrib/bdr && make && make install

add to user's profile:
export PATH=/home/postgres1/bdr/bin:$PATH

check version :
psql (PostgreSQL) 9.4beta1_bdr0601

sudo mkdir /opt/pgdata1
sudo mkdir /opt/pgdata2
sudo chown postgres1:postgres1 /opt/pgdata1/
sudo chown postgres1:postgres1 /opt/pgdata2/

as postgres1
initdb -D /opt/pgdata1/ -U postgres1 --auth-host=md5 --auth-local=peer


edit /opt/pgdata1/pg_hba.conf and add :
host    replication     postgres1        192.168.0.10/32          trust
edit /opt/pgdata1/postgresql.conf and change :
listen_addresses = '*'

### ADDED FOR BDR
wal_level = 'logical'
max_replication_slots = 3
max_wal_senders = 4
shared_preload_libraries = 'bdr'
bdr.connections = 'postgres1'
bdr.postgres2_dsn = 'dbname=postgres host=192.168.0.10 user=postgres port=5433'
track_commit_timestamp = on

Made a copy with bdr_init_copy to a second $PGDATA directory, made the corrections on the connection strings in postgresql.conf so the 2 clusters could connect with each other and started both postgres servers.

Everything worked, both databases were in read/write mode and i could perform all kinds of operations on both of them.

A few things that have to be noted are:
1. you cannot use normal sequences , you have to use global sequences or you will see all kinds of problems.
2. I noticed the following issues that i'm not sure why they happen :

master1 =# create table test_tr (id int primary key, name text);
CREATE TABLE
master1 =# insert into test_tr values (1,'Vasilis');
INSERT 0 1
master1 =# begin ; update test_tr set name = 'Vasilis2' where id =1;
BEGIN
UPDATE 1
Before i commit on master1 i do the following on master 2
master2 =# begin ; update test_tr set name = 'Vasilis3' where id =1;
BEGIN
UPDATE 1
master2 =# commit;
COMMIT
master1 =# commit;
COMMIT
master1 =# select * from test_tr;
id | name

----+----------
1 | Vasilis3
(1 row)
In my understanding the first transaction would lock the row and the second would wait the first to commit.
#CASE 2.#
master1 =# create table tr_test (name text);
CREATE TABLE
master1 =# insert into tr_test values ('Vasilis');
INSERT 0 1
master1 =# begin;
BEGIN
master1 =# update tr_test set name = 'koko';
UPDATE 1
master1 =# select name from tr_test ;
koko
(1 row)
master1 =# commit;
COMMIT
master1 =# select name from tr_test ;
koko
(1 row)
AT THE SAME TIME on MASTER2
master2 =# begin ; update tr_test set name ='llalla';
BEGIN
UPDATE 1
master2 =# select name from tr_test ;
llalla
(1 row)
master2 =# commit;
COMMIT
master2 =# select name from tr_test ;
llalla
(1 row)
AT THIS POINT if you drop the table :
master1 =# drop table tr_test ;
... never ending...
master2 =# drop table tr_test ;
... never ending...
RESTART both masters doesn't make any difference
From postgres logs :
LOG: starting background worker process "bdr (6036347025262511738,1,12155,): postgres2: apply"
LOG: connection received: host=192.168.0.10 port=34641
LOG: replication connection authorized: user=postgres1
LOG: starting logical decoding for slot bdr_12155_6036347078061848225_1_12155__
DETAIL: streaming transactions committing after 0/1E87AC8, reading WAL from 0/1E87A90
LOG: logical decoding found consistent point at 0/1E87A90
DETAIL: running xacts with xcnt == 0
LOG: unexpected EOF on standby connection
LOG: disconnection: session time: 0:00:00.163 user=postgres1 database=postgres host=192.168.0.10 port=34641
ERROR: could not find primary key for table with oid 25281
LOG: worker process: bdr (6036347025262511738,1,12155,): postgres2: apply (PID 3649) exited with exit code 1

logs will keep increasing, same message repeats..



Conclusion is that BDR is not ready for production , something that of course is absolutely normal considering that its based on 9.4 which is still beta2, but it sure is promising, and if it gets to its final stage it will boost PostgreSQL replication flexibility tremendously!



official user guide can be found here

update: After Craigs comment that 0.7 release is out, i plan making a new post about 0.7 version, trying to reproduce the problems i already found.

Thanks for Reading
-- Vasilis