BOND Replicate

Postgresql Master to Master Replication

Bond replicate is a simple easy to use solution for doing replication of postgresql database across multiple sites. Database changes are transmitted between master database sites quickly as changes they happen.

Download Bond Replicate

Source code is released under the GPL licence. Scons is required to compile the source code.
https://gitlab.gymmasteronline.com/publicaccess/bondreplicate

Master to Master Replication

This is a built upon the old master to slave replication code but is Master to Master. Set up and installation is similar to the old system.

It does not do real time lock checking across all databases. The reason for this is the lag issue involved in making sure a record is locked at all sites before allowing a write is significant. So we deal with these errors after they happen instead of before. If you are in a situation where all sites are not regularally modifying the same record at the same time it isn’t going to be an issue. These issues do get logged to replicate_error table when they happen, and all sites have foreign key integrity maintained.

Requirements

postgresql version 8.3
libpq
glib

How it works

Each client keeps a log of records that have been changed. These logs of changes are stored within the database in the replicate_log table.

These log entries are sent in real time to all other databases that are to be replicated to. If the destination database server is down, the log entry is stored till it comes back online.

Once the log entry has been sent to all other servers it will remove the log entry from the table.

By default all tables are replicated, but you can disable certain tables from replication. This is useful for configuration and summary tables by modifying replicate_tables table.

When log entries are sent to the other master database, user triggers are disabled. This is so that if you have triggers on tables they are not fired multiple times as the data is sent around your wide area network.
Foreign key constraints are enabled to force integrity, along with rules and other constraints.

To avoid two sites issuing the same record ID to the same record at the same time, all sequences are updated to not overlap. The last digit of new id’s issued at the site will be the site the id was generated on. Ie, a id sequence thats nextval was to be 6432 would become 64321 instead. This only effects new id’s issued, not old record ids.

Setting up Replication

The credatedb.sql script creates the necessary tables needed for replication to start. The procedure is to set up one server first, and then copy it to all the clients.

psql -f createdb.sql databasename

Edit replicate_hosts and replicate_tables to contain all the other master sites to replicate to.

Edit bondreplicate.conf to have your database connection settings. The maxsites is used to avoid id sequence overlaps. Ideally set it to 10 or 100 depending on the number of sites you have.

Run bondreplicate once, then press ctrl+C once it has started (It should say main loop). It would of create a number of triggers on each table on your database so they start replicating.

Make a copy of your database, and send it to all sites. All clients access should be temporally disabled while you do this to avoid people writing to the database while you update it.

pg_dump databasename > file.sql
psql -f file.sql -h remotehost databasename
<pre>
<h2>Upgrading databases</h2>
<p>Replication can be taken down by running this sql query</p>
<pre>
select replicate_down();