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.