Bucardo/pgbench example

From Bucardo

Jump to: navigation, search

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://bucardo.org/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_ctl file available. Test that you can run it and that you are using the correct version:

 bucardo_ctl --version

Create and populate the database

Bucardo needs a central database. The install option of bucardo_ctl 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_ctl 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_ctl 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_ctl show all
Change any setting by using: bucardo_ctl 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_ctl program does this with the add db option.

 bucardo_ctl add db test1
 bucardo_ctl 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_ctl add all tables db=test1 -T history --herd=alpha --verbose
 New tables:
 New tables added: 3
 Already added: 0

 $ bucardo_ctl 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_ctl add sync benchdelta source=alpha targetdb=test2 type=pushdelta
 Added sync "benchdelta"
 $ bucardo_ctl 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_ctl to check everything out.

 $ bucardo_ctl 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_ctl list syncs
 Sync: benchcopy   (fullcopy )  beta  =>  test2  (Active)
 Sync: benchdelta  (pushdelta)  alpha =>  test2  (Active)
 $ bucardo_ctl 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_ctl 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_ctl 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_ctl 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_ctl stop

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

 bucardo_ctl status
 bucardo_ctl status benchdelta
 bucardo_ctl status benchcopy
Personal tools