· postgresql replication bucardo backup

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:

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.