Postgres replication with bucardo

base on our experience when use bucardo, you must copy the schema that you will mirroring before you start setup, DON'T TRY WITH PRODUCTION DATABASE OR YOU WILL LOSE THE DATA

Preparation for main database

1. backup database

# backup schema only
pg_dump "host=127.0.0.1 port=5432 dbname=maindb user=mainuser" -Fc -s -v -f schema.dump

# Dump contents of database:
pg_dump "host=127.0.0.1 port=5432 dbname=maindb user=mainuser" -Fc -v -f full.dump

2. Create bucardo Role and DB

sudo su - postgres # change users to postgres

createuser --interactive # role name 'bucardo', superusers 'yes'
createdb -O bucardo bucardo

psql # enter to psql
ALTER USER bucardo WITH PASSWORD 'passwordStrong'; # example password
\q # exit from psql

# add credential to pgfile
echo "127.0.0.1:5432:bucardo:bucardo:passwordStrong" > $HOME/.pgpass

sudo chmod 0600 $HOME/.pgpass

3. Setup connection postgres

# edit pg_hba.conf
vi /etc/postgresql/13/main/pg_hba.conf

# copy line bellow

# "local" is for Unix domain socket connections only
local   all             bucardo                                 trust
local   all             all                                     peer
# IPv4 local connections:
host    all             bucardo         127.0.0.1/32            trust
host    all             all             127.0.0.1/32            trust
host    all             all             0.0.0.0/0               md5

# restart postgres
service postgresql restart

4. Test config and install plugin

apt-get update

apt-get install postgresql-plperl-13 # depend on postgres version yu use
apt-get install -y libdbd-pg-perl libcgi-session-perl libencode-locale-perl
apt-get install libdbi-perl

psql -U postgres -h 127.0.0.1 -p 5432 # if yu enter without password mean config works

# add extension and language
CREATE EXTENSION plperl;
CREATE LANGUAGE plperlu;
SELECT * FROM pg_language; # for checking

Setup mirror db with docker

1. Pull image

docker pull postgres:13 # the version must be the same as the main one

2. Start container

# Create pgdata dir for persistent data
mkdir pgdata
chmod 747 pgdata

# run container postgres
docker run -d --name mirrordb --restart unless-stopped -v ${HOME}/pgdata:/var/lib/postgresql/data -e POSTGRES_PASSWORD=mirrorpass -e POSTGRES_USER=mirroruser -e POSTGRES_DB=dbmirror -p 5431:5432 postgres:13

4. Copy main db schema

# Using pg_restore
pg_restore -v --host=127.0.0.1 --port=5431 --dbname=dbmirror --user=mirroruser --no-owner --role=digimirror_prod schema.dump

Config Bucardo

1. install

bucardo install

bucardo status

2. add database

# source or main db
bucardo add database db_source dbname=maindb dbhost=127.0.0.1 dbport=5432
# targets or second db
bucardo add database db_target dbname=dbmirror dbhost=127.0.0.1 dbport=5431 dbuser=mirroruser dbpass=mirrorpass

# to check
bucardo list dbs

3. add tables

bucardo add all tables db=db_source --relgroup=mirrortest --verbose

# to checl
bucardo list tables
bucardo list relgroups

4. add sequncy

bucardo add all sequence db=db_source --relgroup=mirrortest --verbose

5. crate grup and add role db

bucardo add dbgoup mirroring

# for master to master (both db have read&write permission)
bucardo add dbgroup mirroring db_source:source
bucardo add dbgroup mirroring db_target:source

# master to slave (only master have read&write permission)
# source mean master
# target mean slave
bucardo add dbgroup mirroring db_source:source
bucardo add dbgroup mirroring db_target:target

3. add replication event grup

bucardo add sync masters relgroup=mirrortest dbs=mirroring status=inactive
bucardo add sync masterslave relgroup=mirrortest dbs=mirroring onetimecopy=1 status=inactive conflict_strategy=bucardo_source



# tocheck yu config
bucardo list dbgroups
bucardo list syncs

# if all config correct run this command
bucardo update sync masterslave status=active

4. start bucard

bucardo start
bucardo status

# check event
bucardo status digidata
bucardo deactivate digidata

# check demon is run
ps -Afw | grep Bucardo

# to stop
bucardo stop

Bucardo handling

1. update column and table

stop buardo
bucardo update sync masterslave status=inactive
bucardo add all tables db=db_source --relgroup=test --verbose
bucardo update sync masterslave status=active onetimecopy=1
start bucardo