Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

7 Steps to configure BDR replication in postgresql

  •  The BDR (Bi-Directional Replication) project adds multi-master replication to PostgreSQL 9.4. 
Postgres-BDR has a lower impact on the masters(s) than trigger-based replication solutions. There is no write-amplification, as it does not require triggers to write to queue tables in order to replicate writes.
 
Here We are using  postgres version 9.4.12 and bdr version 1.0.2. for configuring  multi master replication . Simply Following 7 steps you can configure the multi master replication in postgresql.
To download the bdr in below link..
 https://github.com/2ndQuadrant/bdr/archive/bdr-pg/REL9_4_12-1.tar.gz

$ tar -xzvf REL9_4_12-1.tar.gz
$ wget https://github.com/2ndQuadrant/bdr/archive/bdr-plugin/1.0.2.tar.gz
$ tar -xzvf 1.0.2.tar.gz

1. To install BDR.
$ cd ~/bdr-bdr-pg-REL9_4_12-1
$ ./configure --prefix=/usr/lib/postgresql/9.4 --enable-debug --with-openssl
$ make -j4 -s install-world
$ cd ~/bdr-bdr-plugin-1.0.2
$ PATH=/usr/lib/postgresql/9.4/bin:"$PATH" ./configure
$ make -j4 -s all
$ make -s install
2. Setup for replication:-First, we will create a new database with bdr turned on.
$ createuser postgres
$ mkdir -p /var/lib/postgresql
$ chown postgres:postgres /var/lib/postgresql
$ sudo usermod -d /var/lib/postgresql postgres
$ su -l postgres
$ export PATH=/usr/lib/postgresql/9.4/bin:$PATH
$ mkdir ~/9.4-bdr
$ initdb -D ~/9.4-bdr -A trust
3.Put a Entry in Postgresql.conf File
listen_addresses = '*'
shared_preload_libraries = 'bdr'
wal_level = 'logical'
track_commit_timestamp = on
max_connections = 100
max_wal_senders = 10
max_replication_slots = 10
max_worker_processes = 10
Restart the server,because the server knows changed parameters.
4. Edit file ~/9.4-bdr/pg_hba.conf , add those lines for enable communication between two server:
host replication postgres 10.0.0.1/32 trust
host replication postgres 10.0.0.2/32 trust
 
host replication bdrsync 10.0.0.1/32 password
host replication bdrsync 10.0.0.2/32 password 
Then Reload The file..
Ok, now we have a new database at ~/9.4-bdr . Let’s repeat all step from part 1 and 2 all two server. Then we need to run the new PostgreSQL server:
(in terminal of postgres user)
$ export PATH=/usr/lib/postgresql/9.4/bin:$PATH
$ pg_ctl -l ~/log -D ~/9.4-bdr start
$ psql -c "CREATE USER bdrsync superuser;"
$ psql -c "ALTER USER bdrsync WITH PASSWORD '12345#';"
5. Create new database
Let’s create a new user test_user with a new database test_db for demo
(in terminal of postgres user)

$ createuser test_user
$ createdb -O test_user test_db
$ psql test_db -c 'CREATE EXTENSION btree_gist;'
$ psql test_db -c 'CREATE EXTENSION bdr;
6. Create a master node in server 1(in terminal of postgres user)
psql
\c test_db
SELECT bdr.bdr_group_create(
    local_node_name := 'node1',
    node_external_dsn := 'host=10.0.0.1 user=bdrsync dbname=test_db password=12345#'
);
7. Join master node in server 2(in terminal of postgres user)
psql
\c test_db
SELECT bdr.bdr_group_join(
    local_node_name := 'node2',
    node_external_dsn := 'host=10.0.0.2 user=bdrsync dbname=test_db password=12345#',
    join_using_dsn := 'host=10.0.0.1 user=bdrsync dbname=test_db password=12345#'
);
View bdr nodes and connections:(in psql terminal of the database)
select * from bdr.bdr_nodes;
select * from bdr.bdr_connections;
To drop replication from a node, this will remove the node from replication with other servers
(in psql terminal of the database)

select bdr.remove_bdr_from_local_node(true)

For more reference to configure multimaster replication:
https://www.tutorialdba.com/2017/09/how-to-setup-bi-directional-replication.html

https://www.tutorialdba.com/2017/09/bdr-bi-directional-replication.html

Comments

Popular posts from this blog

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

PostgreSQL pgBadger

PostgreSQL Pgbadger Installation On Linux

How to configure Replication Manager (repmgr) ?

How To Schedule PostgreSQL Jobs using pgAgent on Linux plateform