Bucardo Installation

In this tutorial i will show you how to install bucardo for PostgreSQL multi-master or master-master replication in Ubuntu server. Let’s see what is bucardo and how we can we install it.

I’ve assumed the following hosts and databases

Primary : Hostname = ashok, Database = mydatabase, IP = 172.26.111.118
Backup : Hostname = ashok, Database = mydatabase, IP = 172.26.111.208

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

172.26.111.118  node1
172.26.111.208  node2

Installation Procedure on Node1

Install DBIx::Safe

Bucardo requires the Perl module DBIx::Safe to be installed.

DBIx::Safe is a Perl module that allows for safe and controlled access to a DBI database handle. It is similar in spirit to the standard Perl module “Safe”.

Some distributions have it available as a package, in which case the installation is as simple as

yum install perl-DBIx-Safe

# or

apt-get install libdbix-safe-perl

To install it manually, grab the latest DBIx::Safe tarball, then unpack the tarball and install as a normal Perl module

tar xvfz dbix_safe.tar.gz
cd DBIx-Safe-1.2.5
perl Makefile.PL
make
make test
sudo make install

Note

make is typically used to build executable programs and libraries from source code. If make command is not working in your ubuntu server and you need to install make command using following command.

sudo apt-get install make

In the above step perl Makefile.PL output looks like as follows

root@ashok:/home/ashok/DBIx-Safe-1.2.5# perl Makefile.PL
Checking if your kit is complete...
Looks good
Warning: prerequisite DBD::Pg 1.49 not found.
Warning: prerequisite DBI 1.49 not found.
Generating a Unix-style Makefile
Writing Makefile for DBIx::Safe
Writing MYMETA.yml and MYMETA.json

If you face following error while executing make test command

Further testing stopped:  Could not load the DBIx::Safe module: Can't locate DBI.pm in @INC

then you need to run following command to install DBI.pm

root@ashok:/home/ashok/DBIx-Safe-1.2.5# sudo apt-get install libdbi-perl

make sure your output of make test command looks like

root@ashok:/home/ashok/DBIx-Safe-1.2.5# make test
PERL_DL_NONLAZY=1 "/usr/bin/perl" "-MExtUtils::Command::MM" "-MTest::Harness" "-e" "undef *Test::Harness::Switches; test_harness(0, 'blib/lib', 'blib/arch')" t/*.t
t/01safe.t ........ ok   
t/02perlcritic.t .. skipped: Must set $ENV{TEST_AUTHOR} to run Perl::Critic tests
t/03db.t .......... skipped: Cannot test without a valid database connection: make sure DBI_DSN and DBI_USER are set.Error was Can't connect to data source '' because I can't work out what driver to use (it doesn't seem to contain a 'dbi:driver:' prefix and the DBI_DRIVER env var is not set) at t/03db.t line 24.
All tests successful.
Files=3, Tests=1,  1 wallclock secs ( 0.04 usr  0.03 sys +  0.53 cusr  0.06 csys =  0.66 CPU)
Result: PASS

Download and unpack the Bucardo tarball

The latest version of Bucardo can be found on the Bucardo download page. Untar it and switch to the directory

ashok@ashok:~$ tar -xvzf Bucardo-5.5.0.tar.gz 

ashok@ashok:~$ cd Bucardo-5.5.0/

Next run following command

ashok@ashok:~/Bucardo-5.5.0$ perl Makefile.PL

output of above command looks like as follows

ashok@ashok:~/Bucardo-5.5.0$ perl Makefile.PL
Checking if your kit is complete...
Looks good
Warning: prerequisite CGI 0 not found.
Warning: prerequisite DBD::Pg 2.0 not found.
Warning: prerequisite DBIx::Safe v1.2.4 not found.
Warning: prerequisite Encode::Locale 0 not found.
Generating a Unix-style Makefile
Writing Makefile for Bucardo
Writing MYMETA.yml and MYMETA.json

Next run following command to build bucardo execution program

root@ashok:/home/ashok/Bucardo-5.5.0# sudo make install

Create the Bucardo database

Bucardo needs to be installed into a database. This database must have the pl/perlu language available. For systems installed via packaging, installing Pl/PerlU may be as simple as

root@ashok:/home/ashok/Bucardo-5.5.0# apt-get install postgresql-plperl-10

Create bucardo directory in /var/run to store pid file

root@ashok:/home/ashok/Bucardo-5.5.0# sudo mkdir /var/run/bucardo

root@ashok:/home/ashok/Bucardo-5.5.0# sudo chmod 777 /var/run/bucardo

Create bucardo directory in /var/log to store bucardo logs

root@ashok:/home/ashok/Bucardo-5.5.0# sudo mkdir /var/log/bucardo

root@ashok:/home/ashok/Bucardo-5.5.0# sudo chmod 777 /var/log/bucardo

Create bucardo database role and database

root@ashok:/home/ashok/Bucardo-5.5.0# sudo su postgres

postgres@ashok:/home/ashok/Bucardo-5.5.0$ psql
psql (10.10 (Ubuntu 10.10-0ubuntu0.18.04.1))
Type "help" for help.

postgres=#  create user bucardo superuser;
CREATE ROLE
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

Change listen_addresses value localhost to *

root@ashok:/home/ashok/Bucardo-5.5.0# vi /etc/postgresql/10/main/postgresql.conf
#
#
listen_addresses = '*'          # what IP address(es) to listen on;
#
#
port = 5432

Add bucardo database in trusted list in pg_hba.conf

root@ashok:/home/ashok/Bucardo-5.5.0# vi /etc/postgresql/10/main/pg_hba.conf
# Database administrative login by Unix domain socket
local   all             postgres                                trust
local   all             bucardo                                 trust

after that restart postgres service

root@ashok:/home/ashok/Bucardo-5.5.0# systemctl restart postgresql

Check 5432 port listening or not

root@ashok:/home/ashok/Bucardo-5.5.0# netstat -anp | grep 5432
tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN      5510/postgres       
tcp        0      0 172.26.111.118:47658    172.26.111.208:5432     ESTABLISHED 10435/Bucardo VAC.

Install Bucardo

Now we will install bucardo using following command

root@ashok:/home/ashok/Bucardo-5.5.0# sudo bucardo install

Output of above command looks like as follows

root@ashok:/home/ashok/Bucardo-5.5.0# sudo 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:           <none>
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

Attempting to create and populate the bucardo database and schema
Database creation is complete

root@ashok:/home/ashok/Bucardo-5.5.0#

Create your own databases and tables

root@ashok:/home/ashok/Bucardo-5.5.0# sudo su postgres

postgres@ashok:/home/ashok/Bucardo-5.5.0$ psql
psql (10.10 (Ubuntu 10.10-0ubuntu0.18.04.1))
Type "help" for help.

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 table3(id integer PRIMARY KEY, num integer);
CREATE TABLE

mydatabase=#

mydatabase=# \dt;
         List of relations
 Schema |  Name  | Type  |  Owner   
--------+--------+-------+----------
 public | table1 | table | postgres
 public | table2 | table | postgres
 public | table3 | table | postgres
(3 rows)

mydatabase=#

Installation Procedure on Node2

Install Postgres

root@ashok:/home/ashok/Bucardo-5.5.0# apt-get install postgresql-plperl-10

Change postgres configurations

Change listen_addresses value localhost to *

root@ashok:/home/ashok/Bucardo-5.5.0# vi /etc/postgresql/10/main/postgresql.conf
#
#
listen_addresses = '*'          # what IP address(es) to listen on;
#
#
port = 5432

Add bucardo database and node1 ip address in trusted list in pg_hba.conf

root@ashok:/home/ashok/Bucardo-5.5.0# vi /etc/postgresql/10/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		        172.26.111.118/32	    trust

after that restart postgres service

root@ashok:/home/ashok/Bucardo-5.5.0# systemctl restart postgresql

Check 5432 port listening or not

root@ashok:/home/ashok/Bucardo-5.5.0# netstat -anp | grep 5432
tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN      3921/postgres       
tcp        0      0 172.26.111.208:5432     172.26.111.118:47692    ESTABLISHED 4672/postgres: 10/m

Create your own databases and tables

root@ashok:/home/ashok/Bucardo-5.5.0# sudo su postgres

postgres@ashok:/home/ashok/Bucardo-5.5.0$ psql
psql (10.10 (Ubuntu 10.10-0ubuntu0.18.04.1))
Type "help" for help.

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 table3(id integer PRIMARY KEY, num integer);
CREATE TABLE

mydatabase=#

mydatabase=# \dt;
         List of relations
 Schema |  Name  | Type  |  Owner   
--------+--------+-------+----------
 public | table1 | table | postgres
 public | table2 | table | postgres
 public | table3 | table | postgres
(3 rows)

mydatabase=#

Configuring Replication

Note

These steps run on node 1

Add databases

root@ashok:/home/ashok/Bucardo-5.5.0#bucardo add database serv1 dbname=mydatabase

root@ashok:/home/ashok/Bucardo-5.5.0#bucardo add database serv2 dbname=mydatabase host=node2

Add tables

root@ashok:/home/ashok/Bucardo-5.5.0# bucardo add table % db=serv1

root@ashok:/home/ashok/Bucardo-5.5.0# bucardo add table % db=serv2

Add herd

root@ashok:/home/ashok/Bucardo-5.5.0# bucardo add all tables --herd=one_two db=serv1

root@ashok:/home/ashok/Bucardo-5.5.0# bucardo add all tables --herd=one_two db=serv1

Add sync

root@ashok:/home/ashok/Bucardo-5.5.0# bucardo add sync sync_oneToTwo relgroup=one_two db=serv1,serv2

root@ashok:/home/ashok/Bucardo-5.5.0# bucardo add sync sync_TwoToOne relgroup=two_one db=serv2,serv1

List databases

root@ashok:/home/ashok/Bucardo-5.5.0# bucardo list database
Database: serv1  Status: active  Conn: psql -U bucardo -d mydatabase
Database: serv2  Status: active  Conn: psql -U bucardo -d mydatabase -h node2

List syncs

root@ashok:/home/ashok/Bucardo-5.5.0# 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@ashok:/home/ashok/Bucardo-5.5.0# bucardo start
Checking for existing processes
Removing file "/var/run/bucardo/fullstopbucardo"
Starting Bucardo

Check bucardo status

root@ashok:/home/ashok/Bucardo-5.5.0# bucardo status
PID of Bucardo MCP: 12262
 Name            State    Last good    Time        Last I/D    Last bad    Time  
===============+========+============+===========+===========+===========+=======
 sync_TwoToOne | Good   | 01:11:17   | 2h 8m 32s | 0/0       | none      |       
 sync_oneToTwo | Good   | 01:11:17   | 2h 8m 32s | 0/0       | none      |

Now you can insert data in node 1 in existing tables and check in node 2 tables. Similarly you can insert data in node 2 in existing tables and check in node 1. Also you can perform delete records in one node and check results in another one.

That’s it guys. This is all about Bucardo. Let me know your comments and suggestions about this tutorial. Thank you.

Bucardo Installation