Bucardo Installation

Multi-Master replication:

I’ve assumed the following hosts and databases

Primary : Hostname = primarynode, Database = mydatabase, IP = 192.168.1.102

Backup : Hostname = replicanode, Database = mydatabase, IP = 172.26.103

Need to add following entries in hosts file (/etc/hosts) both nodes

192.168.1.102 primarynode

192.168.1.103 replicanode

Installation Procedure on primarynode
root@primarynode:~# apt-get install libdbix-safe-perl libdbi-perl
Install Bucardo Deb:
root@primarynode:~# apt-get install postgresql-plperl-12
Create bucardo directory in /var/run to store pid file
root@primarynode:~# sudo mkdir /var/run/bucardo
root@primarynode:~# sudo chmod 777 /var/run/bucardo
Create bucardo directory in /var/log to store bucardo logs
root@primarynode:~# sudo mkdir /var/log/bucardo
root@primarynode:~# sudo chmod 777 /var/log/bucardo
Create bucardo database role and database
root@primarynode:~# sudo su – postgres
postgres@primarynode:~$ psql
postgres=#  create user bucardo superuser;
CREATE ROLE
Alter user passwd:
postgres=#  ALTER USER bucardo  WITH PASSWORD 'mypasswd';
postgres=# create database bucardo;
CREATE DATABASE
postgres=#  ALTER DATABASE bucardo OWNER TO bucardo;
ALTER DATABASE

postgres=# \list

                                 List of databases

   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges  

———–+———-+———-+————-+————-+———————–

 bucardo   | bucardo  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +

           |          |          |             |             | postgres=CTc/postgres

 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +

           |          |          |             |             | postgres=CTc/postgres

(4 rows)           | 

Change postgres configurations in /etc/postgresql/12/main/postgresql.conf

Change listen_addresses value localhost to *

root@primarynode:~# vi /etc/postgresql/12/main/pg_hba.conf

# “local” is for Unix domain socket connections only

Athe end of the file below two lines:

local   all             postgres                                trust

local   all             bucardo                                 trust

# IPv4 local connections:

host    all             all             127.0.0.1/32            md5

host    all                     all                     192.168.1.102/32            trust

after that restart postgres service

root@primarynode:~# systemctl restart postgresql

Check 5432 port listening or not

root@primarynode:~# ss -anp |grep 5432

u_str   LISTEN   0        234                 /var/run/postgresql/.s.PGSQL.5432 80772                                                   * 0                      users:((“postgres”,pid=2565,fd=5))                                            

tcp     LISTEN   0        234                                           0.0.0.0:5432                                              0.0.0.0:*                      users:((“postgres”,pid=2565,fd=3))                                            

tcp     LISTEN   0        234                                              [::]:5432                                                 [::]:*                      users:((“postgres”,pid=2565,fd=4))           

 
Install Bucardo

Now we will install bucardo using following command

apt install bucardo         

root@primarynode:~# bucardo install

bucardo install

This will install the bucardo database into an existing Postgres cluster.

Postgres must have been compiled with Perl support,

and you must connect as a superuser

Current connection settings:

1. Host:           localhost

2. Port:           5432

3. User:           bucardo

4. Database:       bucardo

5. PID directory:  /var/run/bucardo

Enter a number to change it, P to proceed, or Q to quit: p     

Password for user bucardo:

Password for user bucardo:

Attempting to create and populate the bucardo database and schema

Password for user bucardo:

Database creation is complete

postgres=# create database mydatabase;

CREATE DATABASE

postgres=# \c mydatabase

You are now connected to database “mydatabase” as user “postgres”.

mydatabase=# create table table1(id integer PRIMARY KEY, num integer);

CREATE TABLE

mydatabase=# create table table2(id integer PRIMARY KEY, num integer);

CREATE TABLE

mydatabase=# CREATE TABLE

mydatabase=# create table table3(id integer PRIMARY KEY, num integer);

CREATE TABLE

mydatabase=# \dt

         List of relations

 Schema |  Name  | Type  |  Owner  

——–+——–+——-+———-

 public | table1 | table | postgres

 public | table2 | table | postgres

 public | table3 | table | postgres

(3 rows)

 
Installation Procedure on replicanode
Install Postgres
apt-get install postgresql-plperl-12 bucardo libdbi-perl
Change postgres configurations in /etc/postgresql/12/main/postgresql.conf
Change listen_addresses value localhost to *

Add bucardo database and primarynode IP address in trusted list in pg_hba.conf

root@replicanode:~# vi /etc/postgresql/12/main/pg_hba.conf

# Database administrative login by Unix domain socket

local   all             postgres                                trust

local   all             bucardo                                 trust

# IPv4 local connections:

host    all             all             127.0.0.1/32            md5

host    all                     all                     192.168.1.102/32            trust

After that restart postgres service

root@replicanode:~# systemctl restart postgresql

Check 5432 port listening or not

root@replicanode:~# ss -anp|grep 5432

sudo su – postgres

psql

 Create your own databases and tables

postgres=# create database mydatabase;

CREATE DATABASE

postgres=# \c mydatabase

You are now connected to database “mydatabase” as user “postgres”.

mydatabase=# create table table1(id integer PRIMARY KEY, num integer);

CREATE TABLE

mydatabase=# create table table2(id integer PRIMARY KEY, num integer);

CREATE TABLE

mydatabase=# CREATE TABLE

mydatabase=# create table table3(id integer PRIMARY KEY, num integer);

CREATE TABLE

mydatabase=# \dt

Configuring Replication
These steps run on PrimaryNode
Add databases

root@primarynode:~# bucardo add database serv1 dbname=mydatabase host=primarynode

Added database “serv1”

root@primarynode:~# bucardo add database serv2 dbname=mydatabase host=replicanode

Added database “serv2”

Add tables

root@primarynode:~# bucardo add table % db=serv1

Added the following tables or sequences:

  public.table1

  public.table2

  public.table3

root@primarynode:~# bucardo add table % db=serv2

Added the following tables or sequences:

  public.table1

  public.table2

  public.table3

Add herd

root@primarynode:~# bucardo add all tables –herd=one_two db=serv1

Creating relgroup: one_two

Added table public.table1 to relgroup one_two

Added table public.table2 to relgroup one_two

Added table public.table3 to relgroup one_two

New tables added: 0

Already added: 3

root@primarynode:~# bucardo add all tables –herd=two_one db=serv2

Added table public.table1 to relgroup two_one

Added table public.table2 to relgroup two_one

Added table public.table3 to relgroup two_one

New tables added: 0

Already added: 3

Add sync

root@primarynode:~# bucardo add sync sync_oneToTwo relgroup=one_two db=serv1,serv2

Added sync “sync_oneToTwo”

Created a new dbgroup named “sync_oneToTwo”

root@primarynode:~# bucardo add sync sync_TwoToOne relgroup=two_one db=serv2,serv1

WARNING:  Relgroup has no members: two_one

Added sync “sync_TwoToOne”

Created a new relgroup named “two_one”

Created a new dbgroup named “sync_TwoToOne”

List databases

bucardo list database

Database: serv1    Status: active  Conn: psql -U bucardo -d mydatabase -h primarynode

Database: serv2    Status: active  Conn: psql -U bucardo -d mydatabase -h replicanode

List syncs

root@primarynode:~# bucardo list sync

Sync “sync_TwoToOne”  Relgroup “two_one”  DB group “sync_TwoToOne” serv1:target serv2:source  [Active]

Sync “sync_oneToTwo”  Relgroup “one_two”  DB group “sync_oneToTwo” serv1:source serv2:target  [Active]

Start bucardo

root@primarynode:~# bucardo start

Checking for existing processes

Starting Bucardo

Check bucardo status

root@primarynode:~# bucardo status

PID of Bucardo MCP: 5150

 Name            State    Last good    Time      Last I/D    Last bad    Time    

===============+========+============+=========+===========+===========+==========

 sync_TwoToOne | Good   | 08:14:18   | 14m 37s | 0/1       | 04:25:50  | 4h 3m 5s

 sync_oneToTwo | Good   | 08:21:49   | 7m 6s   | 0/1       | none      |         

How to test Database replication:

In this scenario we can add values on the either side. We need to check the same database and tables on the opposite side.

su  – postgres

psql

postgres=# \c mydatabase;

INSERT INTO table2(id,num) VALUES(5,105);  [can be run on either node and check the  same value on the other node]

select * from table2;     

   id   | num

——–+—–

   5 | 105

(1 rows)

Leave a Reply

Your email address will not be published. Required fields are marked *