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=# ALTERUSERbucardoWITHPASSWORD'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)