PostgreSQL replication using Bucardo

Srijan Choudhary 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

     wget https://github.com/bucardo/bucardo/archive/5.4.0.tar.gz
     tar xvfz 5.4.0.tar.gz
  4. On host_a, Build and Install

     perl Makefile.PL
     make
     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:

     host_a:5432:*:postgres:random_password
     host_a:5432:*:bucardo:random_password

    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
    database.

  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
    user.

  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.

Interactions

  • Anonymous
    Anonymous

    Haven't tried these yet, but its very well formated and to the point, Thanks Srijan. I'll again thank you when I am done with these steps. Please keep up this kind of good, clean work. Thanks.

    Reply
  • Dave
    Dave

    Very easy to follow, got a much better idea than from other blogs I was reading from the same - plus, I've ended up with something that actually works!!! Thanks :)

    Reply
  • Derdus
    Derdus

    Hello Srijan
    How do you create the hosts? Can the hosts be in the same machine?

    Best
    Derdus.

    Reply
  • Jeet Parekh
    Jeet Parekh
    [root@machine1 Bucardo-5.5.0]# ./bucardo start
    Checking for existing processes
    Can't locate Sys/Syslog.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at Bucardo.pm line 32. BEGIN failed
    --compilation aborted at Bucardo.pm line 32.

    Getting above error. please help.

    Reply