PostgreSQL

Configure Bucardo replication on CentOS

• bucardo , centos , postgresql , replication

configure bucardo replication centos

I was working with a migration process to migrate the Postgresql Database from Ec2(centos) to AWS RDS. Since I did many migrations to RDS but this is my first migration with Postgresql. The challenge was the source database in Postgresql 9.2 and I wanted to 9.6 on RDS. So decided to configure bucardo replication centos.

Pre-Requirements: #

  • EC2 instance with CentOs 6.5 (you can use this for centos 6.9, 7.0+)
  • Postgresql 9.2 on CentOs
  • RDS with Postgresql 9.6
  • Connectivity between Ec2 to RDS.
  • Source Database bhuvi.

Now lets kick start the process.

Install Postgresql 9.2: #

rpm -Uvh https://download.postgresql.org/pub/repos/yum/9.2/redhat/rhel-7-x86_64/pgdg-centos92-9.2-3.noarch.rpm
yum install postgresql92 postgresql92-server postgresql92-contrib postgresql-devel postgresql92-plperl

Initialize the Postgresql Cluster and start the service: #

/usr/pgsql-9.2/bin/postgresql92-setup initdb
systemctl enable postgresql-9.2.service
systemctl enable postgresql-9.2.service
systemctl start postgresql-9.2.service

Prepare the Source Database: #

create database bhuvi;
\c bhuvi;
create table source (id int);
insert into source values (1);
insert into source values (2);
insert into source values (3);
insert into source values (4);
insert into source values (5);

Install dependencies: #

sudo yum install perl-DBI perl-DBD-Pg perl-DBIx-Safe postgresql92-plperl perl-version perl-ExtUtils-MakeMaker perl-DBD-Pg perl-Encode-Locale perl-Sys-Syslog perl-Test-Simple perl-Pod-Parser perl-Time-HiRes perl-Readonly

rpm -Uvh http://dl.fedoraproject.org/pub/epel/7/x86_64/Packages/p/perl-boolean-0.30-1.el7.noarch.rpm

Prepare a Database for Bucardo: #

CREATE USER bucardo WITH LOGIN SUPERUSER ENCRYPTED PASSWORD 'bucardo-runner';
CREATE DATABASE bucardo;
\c bhuvi;
CREATE EXTENSION plperl;

Add directories for Bucardo service: #

sudo mkdir -p /var/log/bucardo /var/run/bucardo
sudo chown -R postgres:postgres /var/log/bucardo /var/run/bucardo

Install Bucardo Package: #

cd /tmp
wget http://bucardo.org/downloads/Bucardo-5.4.1.tar.gz
tar xvfz Bucardo-5.4.1.tar.gz
cd Bucardo-5.4.1
perl Makefile.PL
make
sudo make install

Source file for Bucardo: #

vi $HOME/.bucardorc
dbhost=127.0.0.1
dbname=bucardo
dbport=5432
dbuser=bucardo

Setup pgpass file: #

echo "127.0.0.1:5432:bucardo:bucardo:bucardo-runner" > $HOME/.pgpass
chmod 0600 $HOME/.pgpass

Install bucardo on its own database: #

./tmp/Bucardo-5.4.1/bucardo install --quiet

This will ask to verify the configurations, f you see the username or DB name get changes press the num ber you want to change.

Export the Source and Target Database details: #

export SOURCE_HOST=127.0.0.1
export SOURCE_PORT=5432
export SOURCE_DATABASE=bhuvi
export SOURCE_USERNAME=bucardo
export SOURCE_PASSWORD=bucardo

export DEST_HOST=bhuvi.us-east-1.rds.amazonaws.com
export DEST_PORT=5432
export DEST_DATABASE=bhuvi
export DEST_USERNAME=bhuvi
export DEST_PASSWORD=bhu12345

cat >> $HOME/.pgpass <<EOL
$DEST_HOST:$DEST_PORT:$DEST_DATABASE:$DEST_USERNAME:$DEST_PASSWORD
$SOURCE_HOST:$SOURCE_PORT:$SOURCE_DATABASE:$SOURCE_USERNAME:$SOURCE_PASSWORD
EOL

Export the table that we want to replicate: #

export TABLES_WITH_SPACES="public.source"

You can use space to add multiple tables.

Take a dump without data: #

pg_dump "host=$SOURCE_HOST port=$SOURCE_PORT dbname=$SOURCE_DATABASE user=$SOURCE_USERNAME" $TABLES --schema-only | grep -v 'CREATE TRIGGER' | grep -v '^--' | grep -v '^$' | grep -v '^SET' | grep -v 'OWNER TO' > schema.sql

Restore the dump on the target database: #

psql "host=$DEST_HOST port=$DEST_PORT dbname=$DEST_DATABASE user=$DEST_USERNAME" -f schema.sql

Add source and destination database to Bucardo: #

./bucardo add db source_db dbhost=$SOURCE_HOST dbport=$SOURCE_PORT dbname=$SOURCE_DATABASE dbuser=$SOURCE_USERNAME dbpass=$SOURCE_PASSWORD
./bucardo add db dest_db dbhost=$DEST_HOST dbport=$DEST_PORT dbname=$DEST_DATABASE dbuser=$DEST_USERNAME dbpass=$DEST_PASSWORD

Add tables to the Bucardo: #

./bucardo add tables $TABLES_WITH_SPACES db=source_db
./bucardo add herd copying_herd $TABLES_WITH_SPACES

Start Sync the tables: #

./bucardo add sync the_sync relgroup=copying_herd dbs=source_db:source,dest_db:target onetimecopy=2
./bucardo start

Yeah!!! we are done. Query the Target table and you can see the data there.