Skip to content

PostgreSQL replication using Bucardo

Srijan Choudhary
2 min read
PostgreSQL replication using Bucardo

There are many different ways to use replication in PostgreSQL, whether for high
availability (using a failover), or load balancing (for scaling), or just for
keeping a backup. Among the various tools I found online, I though bucardo is
the best for my use case - keeping a live backup of a few important tables.

I've assumed the following databases:

  • Primary: Hostname = host_a, Database = btest
  • Backup: Hostname = host_b, Database = btest

We will install bucardo in the primary database (it required it's own database
to keep track of things).

  1. Install postgresql

    sudo apt-get install postgresql-9.4
  2. Install dependencies on host_a

    sudo apt-get install libdbix-safe-perl libdbd-pg-perl libboolean-perl build-essential postgresql-plperl-9.4
  3. On host_a, Download and extract bucardo source

    tar xvfz 5.4.0.tar.gz
  4. On host_a, Build and Install

    perl Makefile.PL
    sudo make install
    sudo mkdir /var/run/bucardo
    sudo mkdir /var/log/bucardo
  5. Create bucardo user on all hosts

    CREATE USER bucardo SUPERUSER PASSWORD 'random_password';
    CREATE DATABASE bucardo;
    GRANT ALL ON DATABASE bucardo TO bucardo;

    Note: All commands from now on are to be run on host_a only.

  6. On host_a, set a password for the postgres user:

    ALTER USER postgres PASSWORD 'random_password';
  7. On host_a, add this to the installation user's ~/.pgpass file:


    Also add entries for the other hosts for which users were created in step 5.

    Note: It is also a good idea to chmod the ~/.pgpass file to 0600.

  8. Run the bucardo install command:

    bucardo -h host_a install
  9. Copy schema from A to B:

    psql -h host_b -U bucardo template1 -c "drop database if exists btest;"
    psql -h host_b -U bucardo template1 -c "create database btest;"
    pg_dump -U bucardo --schema-only -h host_a btest | psql -U bucardo -h host_b btest
  10. Add databases to bucardo config

    bucardo -h host_a -U bucardo add db main db=btest user=bucardo pass=host_a_pass host=host_a
    bucardo -h host_a -U bucardo add db bak1 db=btest user=bucardo pass=host_b_pass host=host_b

    This will save database details (host, port, user, password) to bucardo

  11. Add tables to be synced

    To add all tables:

    bucardo -h host_a -U bucardo add all tables db=main relgroup=btest_relgroup

    To add one table:

    bucardo -h host_a -U bucardo add table table_name db=main relgroup=btest_relgroup

    Note: Only table which have a primary key can be added here. This is a
    limitation of bucardo.

  12. Add db group

    bucardo -h host_a -U bucardo add dbgroup btest_dbgroup main:source bak1:target
  13. Create sync

    bucardo -h host_a -U bucardo add sync btest_sync dbgroup=btest_dbgroup relgroup=btest_relgroup conflict_strategy=bucardo_source onetimecopy=2 autokick=0
  14. Start the bucardo service

    sudo bucardo -h host_a -U bucardo -P random_password start

    Note that this command requires passing the password because it uses sudo,
    and root user's .pgpass file does not have the credentials saved for bucardo

  15. Run sync once

    bucardo -h host_a -U bucardo kick btest_sync 0
  16. Set auto-kick on any changes

    bucardo -h host_a -U bucardo update sync btest_sync autokick=1
    bucardo -h host_a -U bucardo reload config

That's it. Now, the tables specified in step 11 will be replicated from host_a
to host_b.

I also plan to write about other alternatives I've tried soon.

devops postgresql

Related Posts

Members Public

Download a file securely from GCS on an untrusted system

Download files from google cloud storage using temporary credentials or time-limited access URLs

Members Public

Advanced PostgreSQL monitoring using Telegraf, InfluxDB, Grafana

Introduction This post will go through my experience with setting up some advanced monitoring for PostgreSQL database using Telegraf, InfluxDB, and Grafana (also known as the TIG stack), the problems I faced, and what I ended up doing at the end. What do I mean by advanced? I liked this

Advanced PostgreSQL monitoring using Telegraf, InfluxDB, Grafana
Members Public

Running docker jobs inside Jenkins running on docker

Jenkins [] is a free and open source automation server, which is used to automate software building, testing, deployment, etc. I wanted to have a quick and easy way to run Jenkins inside docker, but also use docker containers to run jobs on the dockerized Jenkins. Using

Running docker jobs inside Jenkins running on docker