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

Oracle DBMS SCHEDULER Examples

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

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

vacuumlo - removing large objects orphans from a database PostgreSQL