Here you may see about bi-directional replication we have used http://2ndquadrant.com/en/resources/bdr/
We use here 64bit ubuntu 14.04 with docker to demonstrate our replication solution. First we need to install docker .
sudo su
apt-key adv --keyserver hkp://p80.pool.sks-keyservers.net:80 --recv-keys 58118E89F3A912897C070ADBF76221572C52609D
echo "deb https://apt.dockerproject.org/repo ubuntu-trusty main" > /etc/apt/sources.list.d/docker.list
apt-get update
apt-get purge lxc-docker
apt-cache policy docker-engine
apt-get install linux-image-extra-$(uname -r) # ubuntu 14.04
apt-get install docker-engine
sudo usermod -aG docker iii
exit
Use your account on ubuntu instead iii in the last command. Here we should log out and log in.
https://docs.docker.com/engine/installation/linux/ubuntulinux/
For demonstration we used docker image from here: https://hub.docker.com/r/agios/postgres-bdr/
docker pull agios/postgres-bdr
docker run --name testhost1 -d agios/postgres-bdr
docker run --name testhost2 -d agios/postgres-bdr
Connect to testhost1:
docker exec -i --user=root -t testhost1 /bin/bash
su - postgres
createdb bdrdemo
We must know IP of testhost:
ip addr
My IP for testhost1 was 172.17.0.2
psql bdrdemo
Do queryes in psql:
CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE EXTENSION IF NOT EXISTS bdr;
SELECT bdr.bdr_group_create(
local_node_name := 'node01',
node_external_dsn := 'host=172.17.0.2 port=5432 dbname=bdrdemo'
);
SELECT bdr.bdr_node_join_wait_for_ready();
Connect to the second host:
docker exec -i --user=root -t testhost2 /bin/bash
su - postgres
createdb bdrdemo
psql bdrdemo
Do queries on second host:
CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE EXTENSION IF NOT EXISTS bdr;
SELECT bdr.bdr_group_join(
local_node_name := 'node02',
node_external_dsn := 'host=172.17.0.3 port=5432 dbname=bdrdemo',
join_using_dsn := 'host=172.17.0.2 port=5432 dbname=bdrdemo'
);
SELECT bdr.bdr_node_join_wait_for_ready();
Testing 1):
from testhost1:
su - postgres
psql bdrdemo
CREATE TABLE test_table1 (column1 integer);
from testhost2: (see how test_table1 was replicated on testhost2)
su - postgres
psql bdrdemo
\d
Testing 2): from testhost1:
INSERT INTO test_table1 VALUES(33);
SELECT * FROM test_table1;
from testhost2:
SELECT * FROM test_table1;
We can create table only if two servers will be online. All operations with existing tables are available on each server independently.
Here you can see video tutorial on this instruction (5 Parts):