Last updated: 2018-11-05

This page describes the steps needed to replicate a sample database, created by the pgbench utility, with Bucardo. This will demonstrate simply master to slave behavior, using the pushdelta and fullcopy sync types.

Install Bucardo

The first step is to install Bucardo. Detailed instructions can be found on the installation page, but the quick steps are:

Install Perl modules

Bucardo requires the following Perl modules to be installed:

  • DBD::Pg
  • DBIx::Safe

Download and unpack Bucardo

The latest version of Bucardo can be found at the download page. Alternatively, you can pull the development version from git by doing:

git clone git://github.com/bucardo/bucardo.git

Either way, you should end up in a bucardo directory, and ready for the next step.

make and install

Run the following commands:

perl Makefile.PL

The following step is optional but recommended:

make test

Finally, install as a user with appropriate rights. One way to do this is:

sudo make install

You should now have a global bucardo file available. Test that you can run it and that you are using the correct version:

bucardo --version

Create and populate the database

Bucardo needs a central database. The install option of bucardo will create and install this database for you. All you need to provide is the location of a Postgres instance you want to use, and a valid PID directory. For this example, we’ll use the default values of no host, port 5432, and a user named ‘Postgres’. We’ll use the /tmp/bucardo directory as our piddir value.

mkdir /tmp/bucardo
bucardo install --piddir=/tmp/bucardo

You will need to enter a “P” to tell it to proceed. If all goes well, you should see a message like this:

$ bucardo install --piddir=/tmp/bucardo
This will install the bucardo database into an existing Postgres cluster.
Postgres must have been compiled with Perl support,
and you must connect as a superuser
We will create a new superuser named 'bucardo',
and make it the owner of a new database named 'bucardo'
Current connection settings:
1. Host:          <none>
2. Port:          5432
3. User:          postgres
4. PID directory: /tmp/bucardo
Enter a number to change it, P to proceed, or Q to quit: p
Postgres version is: 8.4
Attempting to create and populate the bucardo database and schema
Database creation is complete
Connecting to database 'bucardo' as user 'bucardo'
Updated configuration setting "piddir"
Installation is now complete.
If you see any unexpected errors above, please report them to bucardo-general@bucardo.org
You should probably check over the configuration variables next, by running:
bucardo show all
Change any setting by using: bucardo set foo=bar

That’s it! Time to setup our test databases. NOTE: In this example the source/master and target/slave databases reside within a single instance of he Postgres server, and this installation step is normally only required for the source/master node. In the real world where source/master and target/slave are hosted in separate Postgres servers, each slave node will need to have the role ‘bucardo’ manually created before proceeding to the next step.

Setup the pgbench databases

The pgbench utility that comes with Postgres can be used to create some simple test tables in an existing database. Let’s create two databases, test1 (the master), and test2 (the slave).

createdb test1
createdb test2

Next, we’ll install the pgbench files on each.

pgbench -i test1
pgbench -i test2

Now that we have some data, let’s get Bucardo to replicate it.

Add the databases

Bucardo needs to know about each database it needs to talk to. The bucardo program does this with the add db option.

bucardo add db test1
bucardo add db test2

We’ve kept it simple for this example, but you generally will end up replicating databases with the same name, and thus should add an extra internal database name. Since we did not provide one, they default to the actual database names.

Add the tables

Bucardo also needs to know about any tables that it may be called on to replicate. Adding tables by the add table command does not actually start replicating them. In this case, we’re going to use the handy add all tables feature. Tables are grouped together inside of Bucardo into herds, so we’ll also place the newly added tables into a named herd. Finally, the history table has no primary key or unique index, so we cannot replicate it by using the pushdelta method, so we’re going to exclude it from the alpha herd, using the -T switch, and add it in the next setup with the -t switch.

$ bucardo add all tables db=test1 -T history --herd=alpha --verbose
New tables:
New tables added: 3
Already added: 0
$ bucardo add all tables db=test1 -t history --herd=beta --verbose
New tables:
New tables added: 1
Already added: 0

Add the syncs

A sync is a named replication event. Each sync has a source herd; because we created two herds above, we’ll go ahead and create two syncs as well. One will be a pushdelta sync, the other will be a fullcopy sync.

$ bucardo add sync benchdelta source=alpha targetdb=test2 type=pushdelta
Added sync "benchdelta"

$ bucardo add sync benchcopy source=beta targetdb=test2 type=fullcopy
Added sync "benchcopy"

We are ready to kick off Bucardo at this point. Before we do, let’s use the list options to bucardo to check everything out.

$ bucardo list herds
Herd: alpha Members: public.branches, public.tellers, public.accounts
  Used in syncs: benchdelta
Herd: beta  Members: public.history
  Used in syncs: benchcopy
$ bucardo list syncs
Sync: benchcopy   (fullcopy )  beta  =>  test2  (Active)
Sync: benchdelta  (pushdelta)  alpha =>  test2  (Active)

$ bucardo list dbs
Database: test1  Status: active  Conn: psql -p 5432 -U bucardo -d test1
Database: test2  Status: active  Conn: psql -p 5432 -U bucardo -d test2
$ bucardo list tables
Table: public.accounts  DB: test1  PK: aid (int4)
Table: public.branches  DB: test1  PK: bid (int4)
Table: public.history   DB: test1  PK: none
Table: public.tellers   DB: test1  PK: tid (int4)

Start Bucardo

The final step is to fire it up:

bucardo start

After a few seconds, the prompt will return. There will be a log file in the current directory called log.bucardo that you can look through. To disable the logfile and just rely on syslog use the –debugfile=0 argument. You can also verify that the Bucardo daemons are running by doing a:

ps -Afw | grep -i Bucardo

Test Replication

To verify that things are working properly, let’s get some baseline counts:

$ psql -d test1 -At -c 'select count(*) from tellers'

$ psql -d test2 -At -c 'select count(*) from tellers'

$ psql -d test1 -c 'select * from tellers where tid = 1'
 tid | bid | tbalance | filler 
   1 |   1 |        0 |
(1 row)
$ psql -d test2 -c 'select * from tellers where tid = 1'
 tid | bid | tbalance | filler
   1 |   1 |        0 |
(1 row)

Now let’s make changes to that record, and verify that it gets propagated to the slave (test2)

$ psql -d test1 -c 'update tellers set bid=999 where tid = 1'
$ psql -d test2 -c 'select * from tellers where tid = 1'
 tid | bid | tbalance | filler
   1 | 999 |        0 |

How about the history table, which has not primary key? We cannot track row by row changes, and don’t want to copy the whole thing every time the table changes, so we’ve got to kick that sync manually when we want to change it:

$ psql -d -At test1 -c 'select count(*) from history'
$ psql -d -At test2 -c 'select count(*) from history'
$ pgbench -t3 test1
$ psql -At -d test1 -c 'select count(*) from history'
$ psql -At -d test2 -c 'select count(*) from history'
$ bucardo kick benchcopy
$ psql -At -d test1 -c 'select count(*) from history'
$ psql -At -d test2 -c 'select count(*) from history'

This ends the demonstration. Feel free to play around more. To stop Bucardo when done, just issue:

bucardo stop

As you experiment, you might also want to look at the syncs in more detail with:

bucardo status
bucardo status benchdelta
bucardo status benchcopy

Bucardo.org is hosted and supported by End Point Corporation

Need assistance with Bucardo?

End Point Corporation offers professional support for Bucardo, as well as specializing in developing, designing, and marketing effective websites. Since 1995, our diverse team of developers has an excellent record of client satisfaction and proven that End Point can handle your organization’s greatest web challenges.