Piyush dot Me

rubyrep Database replication that doesn’t hurt.

Unlike Oracle & MySQL : PostgreSQL doesn’t’ have built in replication solutions but there are many other replication solutions available for PostgreSQL liked listed here :-

http://wiki.postgresql.org/wiki/Replication%2C_Clustering%2C_and_Connection_Pooling

and some additional proprietary solutions for the custom needs by different companies.

Mostly people use Slony : http://www.slony.info/ for master – slave replication solution – Slony is a “master to multiple slaves” replication system supporting cascading (e.g. – a node can feed another node which feeds another node…) and failover.

but Slony has limitations as mentioned here :

• Replicated tables must have a unique or primary key
• It does not support replication of large objects
• Schema changes are not propagated (though they can be coordinated)
• It does not support synchronizing databases outside of replication
• There are limitations on version compatibility; you can not replicate from PostgreSQL 8.2 to PostgreSQL 8.4 for example
• It is more difficult to set up than many other replication solutions

There are many new replication and clustering solutions being there but most of them in development phases only.

To provide mater-master replication in PostgreSQL – mostly used solutions are :-

- Bucardo

- RubyRep

and RubyRep is most easy to setup and configure.

RubyRep Mission:-

Development of an open-source solution for asynchronous, master-master replication of relational databases that is

• ridiculously easy to use
• database independent

It currently supports PostgreSQL and MySQL and is currently developed by Arndt Lehmann,. He also provides great support to the RubyRep mailing list, especially for adding new features or fixing bugs.

RubyRep always operates on two databases. To make it simple to understand, the databases are referred to as “left” and “right” database respectively.

RubyRep’s key features includes:

• Simple configuration, complete setup can be done via single configuration file.
• Simple Installation, if you have a JVM installed, then you just have to download and extract the files.
• Platform Independent, it runs on Unix and Windows platform.
• Table Design Independent, meaning that all commands work on tables no matter if they have a simple primary key (all data types acceptable), a combined primary key, or no primary key at all. It successfully processes multi-byte texts and “big” data types
• It replicates tsvector datatype

In addition to the above, RubyRep actually provides three tools in one; a Compare, Sync, and Replication tools.

Compare

This tool scans corresponding tables of left and right database, looking for diverging data. Key features of the comparison tool are:

• Different output modes, from a count of differences to full row dumps.
• Low bandwidth mode available, reducing the number of round-trips so only actual differences go through the network.
• A progress bar with estimated remaining amount of work.
• Server load is targeted toward only the “right” database server.

Sync

The sync tool is used to synchronize data in corresponding tables of a left and right pair of databases. Key features of the sync tool are:

• All features of the Compare tool also apply to syncs
• Automatically orders table syncs to avoid foreign key conflicts.
• You can configure the Sync policy to ignore deletes in left database, or to ignore creating records in right database, and other such combinations
• Provides two prebuilt conflict resolution methods, either left db wins or right db wins
• Custom conflict resolution methods specifiable via ruby code snippets
• Merge decisions can optionally be logged in the rubyrep event log table.

Replicate

Of course RubyRep also provides a replication tool. Some of the key features of the replication tool include:

• Automatically sets up all necessary triggers, log tables, etc.
• Automatically discovers newly added tables and synchronizes the table content
• Automatically reconfigures sequences to avoid duplicate key conflicts
• Tracks changes to primary key columns
• Can implement either master-slave or master-master replication
• Prebuilt conflict resolution methods available include left or right wins, or earlier, later change wins
• Custom conflict resolution specifiable via ruby code snippets
• Replication decisions can optionally be logged in the rubyrep event log table

One of the problems common to replication solutions is that of setting up new nodes. With Slony, there are always some headaches caused by high load on master database server, as a result of the TRUNCATE/COPY cycle Slony goes through. In the case of RubyRep, most of the CPU load is on the slave server, and you can use the Sync command in advance before you start replicating database. RubyRep also provides some flexibility to ignore the Sync commands if you don’t want to sync the database again.

For installation refer to http://www.rubyrep.org/installation.html

Help

# rubyrep --help
Usage: /usr/local/bin/rubyrep [general options] command [parameters, ...]

Asynchronous master-master replication of relational databases.

Available options:
 --verbose                    Show errors with full stack trace
 -v, --version                    Show version information.
 --help                       Show this message

Available commands:
 generate        Generates a configuration file template
 help            Shows detailed help for the specified command
 proxy           Proxies connections from rubyrep commands to the database
 replicate       Starts a replication process
 scan            Scans for differing records between databases
 sync            Syncs records between databases
 uninstall       Removes all rubyrep tables, triggers, etc. from "left" and "right" database

• Generate configuration file

  #rubyrep generate myrubyrep.conf

Checkout http://www.rubyrep.org/tutorial.html for scanning & sync & replication configs

Ref: Denish Patel Blog Post