Bucardo: the replication system for PostgreSQL

A few posts back, we talked about Bucardo and its usefulness for replicating PostgreSQL Databases. We looked at how to install Bucardo and took the first steps to synchronize our Databases; specifically, we created a simple master-master relationship between two Databases belonging to different hosts.

Now I bring you a small detail that must be taken into account when synchronizing Databases, and it is the synchronization of sequences:

Sequences and Bucardo

Sequences are used to generate unique integer values for assignment in associated tables; in other words, they are used to generate primary keys (Primary Key -PK-) for subsequent storage in the tables. By default, Bucardo does not synchronize the sequences for these identifiers when inserting the replicated records, resulting in an inconsistency between the databases to be synchronized (as far as sequences are concerned). However, we have several ways to solve this; first, the easiest:

1.0 Modify the counter sequence

Imagine we have two Databases, in different networks, that require an Internet connection to synchronize:

However, in at least one of the networks, a permanent Internet connection is not possible (except for a few hours a day), resulting in the synchronization not being able to be carried out regularly.

To make matters worse, both Databases are updated by different users all the time.

What could we do if we want to synchronize these two Databases?

1.1 Avoiding record collision

We can manage different sequences so that records are never overlapped, and when synchronizing the Databases, the identifiers of the Databases will never collide (or indicate that the PK of the record to be inserted already exists):

Secuencia BD1 = START WITH 1 INCREMENT BY 2. Secuencia BD2 = START WITH 2 INCREMENT BY 2.

We can also use different intervals; that is, start the counter in one of the Databases at 1 and the other at 100000000:

Secuencia BD1 =  START WITH 1. Secuencia BD2 =  START WITH 100000000.

You can see both scenarios in the following link Pushdelta syncs and sequences.

1.2 Synchronizing sequences with Bucardo

The other scenario is to indicate that - in addition to the tables - the sequences should also be synchronized; for this we use the following command:

$ bucardo add <sequences> db=dbname=<dbname>  relgroup=<relgroupname>

Where <sequences> are the sequences to synchronize; if we want to synchronize all sequences in our Database:

$ bucardo add all sequences db=dbname=<dbname>  relgroup=<relgroupname>

Following our example we saw in the previous Bucardo post, the following commands are necessary to synchronize both the tables and their sequences:

$ bucardo add database oo2local dbname=oo2 user=postgres pass=postgres $ bucardo add database ooremoto dbname=oo user=postgres pass=XXXX host=XXXXX $ bucardo add dbgroup mydbgroup midblocal:source midbremoto:source $ bucardo add all tablerelgroup=myrelgroup db=midblocal $ bucardo add all sequences db=midblocal relgroup=myrelgroup $ bucardo add sync mysync relgroup=myrelgroup dbs=mydbgroup autokick=0 $ bucardo update sync mysync autokick=1

The very good news is that if you were using the scheme proposed in the previous post (all tables without the schemas), you can execute this simple line:

$ bucardo add all tablerelgroup=myrelgroup db=midblocal

To tell Bucardo to also synchronize the sequences, and that's it; there is no need to restart or update anything else, Bucardo will automatically take the new configurations. Still, if you want to know that the changes you applied are really being taken by Bucardo, just run the following command:

linux1@linux1-Lenovo-3000:~$ bucardo status mysync ====================================================================== Last good                : Aug 13, 2026 12:04:39 (time to run: 8s) Rows deleted/inserted    : 1 / 1 Sync name                : mysync Current state            : Good Source relgroup/database : myrelgroup / midblocal Tables in sync           : 47 Status                   : Active Check time               : None Overdue time             : 00:00:00 Expired time             : 00:00:00 Stayalive/Kidsalive      : Yes / Yes Rebuild index            : No Autokick                 : Yes Onetimecopy              : No Post-copy analyze        : Yes Last error:              : ====================================================================== 

Bucardo adds the total of tables and sequences to synchronize in Tables in sync; since my Database has 29 tables and 18 sequences, which total 47 tables and sequences.

Another way to synchronize both tables and sequences is to add them explicitly to their relgroup:

# 1. Create the databases (midblocal and midbremoto) 
bucardo add database midblocal dbname=oo2 user=postgres pass=postgres bucardo add database midbremoto dbname=oo user=postgres pass=XXXX host=XXXXX # 
2. Create the database group 
bucardo add dbgroup mydbgroup midblocal:source midbremoto:source # 
3. Create the relationship group (myrelgroup)

Add Tables to the Relationship Group

$ bucardo add all tables db=midblocal relgroup=myrelgroup

(This adds all tables from midblocal to the myrelgroup group.)

Add Sequences to the Relationship Group

This command is vital to ensure that the primary key counters remain synchronized:

$ bucardo add all sequences db=midblocal relgroup=myrelgroup

Create and Activate Synchronization

$ bucardo add sync mysync relgroup=myrelgroup dbs=mydbgroup autokick=0 $ bucardo update sync mysync autokick=1 $ bucardo start

With these steps, you ensure that all tables and all sequences are being monitored and synchronized by Bucardo.

Bucardo: the replication system for PostgreSQL

As the title for this post specifies, Bucardo is a replication system that, in other words, allows "cloning" or "duplicating" records within Databases (sources-targets), and also has support for managing multiple Databases with master-slave relationships.

Although the source must be a PostgreSQL Database, the target can be PostgreSQL, MySQL, Redis, Oracle, MariaDB, SQLite, or MongoDB.

The steps to install Bucardo on Linux are very simple (as we will see in the next section), although Bucardo can be a bit difficult to install due to various configurations and necessary dependencies at the Operating System level, but in this post, you will find a section where we talk about Errors that occurred while installing Bucardo.

This post offers a quick but complete view of Bucardo, starting with its installation in a Linux environment, its configuration, some errors that may appear during its installation process, and concluding the post by carrying out a small experiment with a simple Master-Master relationship with PostgreSQL Databases.

Installing Bucardo on Ubuntu -part 1-

In this section, we will give some tips on how to install Bucardo version 5.6.0 on Ubuntu, although the procedure is the same for other Linux distributions; first, we download the `Bucardo.tar.gz` from the downloads section.

We unpack the package and locate ourselves in the folder from the terminal; at the time this post was written, the version is 5.6.0:

$ cd Bucardo-5.6.0/

We generate the Makefile:

$ perl Makefile.PL Generating a Unix-style Makefile Writing Makefile for Bucardo Writing MYMETA.yml and MYMETA.json

The most recommended is to obtain your copy of Bucardo from its GitHub repository at: git clone git://bucardo.org/bucardo.git

Finally, we install Bucardo:

$ make make install

In Fedora, there is another method to get a copy of Bucardo, which consists of installing it through the repositories:

$ rpm -Uvh http://download.fedora.redhat.com/pub/epel/5/i386/epel-release-5-6.noarch.rpm yum -y install bucardo

Modern way

These are the most current steps you can follow to install Bucardo.

You must have the following packages installed:

$ sudo apt update $ sudo apt install git postgresql-server-dev-XX libdbi-perl libdbd-pg-perl libclone-perl make

Download:

$ git clone https://github.com/bucardo/bucardo.git $ cd bucardo/

Generate and install:

perl Makefile.PL make sudo make install

Execute the Bucardo installation:

$ bucardo install

Configuring the PostgreSQL environment for Bucardo

Once we install the first phase of Bucardo through the Makefiles, we go to PGAdmin or any other Administrator and create a Database and Role (with SuperUser) called "bucardo":

Configuring Bucardo -part 2-

Now we will continue with the Bucardo installation according to the configurations made in PostgreSQL; for this, we must execute in a terminal:

$ bucardo install

or -depending on the installation source-

$ bucardo_ctl install

We will see the following information:

Current connection settings: 1. Host:           <none> 2. Port:           5432 3. User:           postgres 4. Database:       bucardo 5. PID directory:  /var/run/bucardo

If the directory /var/run/bucardo does not exist, create it via: mkdir /var/run/bucardo

The Host, user, and database created above are specified:

Current connection settings: 1. Host:           localhost 2. Port:           5432 3. User:           bucardo 4. Database:       bucardo 5. PID directory:  /var/run/bucardo

Errors that occurred while installing Bucardo:

• ERROR: language "plperlu" does not exist: We install the package with sudo yum install postgresql-plperl, after installation in the Bucardo Database we run:         `CREATE EXTENSION plperl;`        `CREATE LANGUAGE plperlu;`
• ERROR: permission denied for language plperlu Set the user as SuperUser from the pgAdmin interface
• For any authentication error; remember to place the following in the pg_hba.conf located in /etc/postgresql/X.X/main/p:
• local   bucardo         bucardo                                 md5
• If the following error appears when executing a Bucardo command: `DBI connect('dbname=bucardo','bucardo',...) failed: fe_sendauth: no password supplied at /usr/local/bin/bucardo line 308.`        You can solve the error by modifying the file indicated in the error (`/usr/local/bin/bucardo`), look for the indicated line of code:

• And add the Bucardo password:

If everything goes well and no other error occurred, CONGRATULATIONS, you have just completed the installation of Bucardo and can now create all the relationships between Databases you want.

Creating a Master-Master relationship with Bucardo

In this last section, we will see how to create a simple master-slave relationship with Bucardo commands, which I will briefly explain below:

• First, we create the Databases; if they are local, it is not necessary to specify the host: `bucardo add database <name> dbname=<dbname> user=<dbuser> pass=<dbpassword>`
• to add the tables and sequences to synchronize: `bucardo add table <table1> <table2> … relgroup=<relgroupname>`
• We create a Database group to easily synchronize them later: `bucardo add dbgroup <dbgroupname> <dbname>:source <dbname>:source`
• Now we create the synchronization with: `bucardo add <syncname> mysync relgroup=<relgroupname> dbs=<dbgroupname> autokick=0`
• And we activate the synchronization: `bucardo update sync <syncname> autokick=1`

You can find the official and complete documentation in the links at the end of this post.

Once the basic commands for creating a replication system are understood, now we are going to create a Master-Master relationship where one of the Databases is local and the other is remote; for this, we use the following commands:

bucardo add database midblocal dbname=oo user=bucardo pass=bucardo bucardo add database midbremoto dbname=oo user=postgres pass=postgres host=<RemoteIP> bucardo add table public.estatus db=midblocal relgroup=myrels bucardo add dbgroup mydbgroup midblocal:source midbremoto:source bucardo add sync mysync relgroup=myrels dbs=mydbgroup autokick=0 bucardo update sync mysync autokick=1 bucardo reload config

Finally, we start Bucardo with:

$ bucardo start

And we check its status with:

$ bucardo status

Now try inserting records into any of the synchronized Database tables, and they should replicate to stay synchronized.

Some links of interest:

• Bucardo Wiki
• Bootstrapping Bucardo Master/Master Replication
• Install Bucardo