Database Replication with Slony-I

Whether you need multiple instances of your database for high availability, backup or for a no-downtime migration to a new version, this versatile tool will keep all of them in sync.

Database management systems have been a crucial component of infrastructures for many years now. PostgreSQL is an advanced, object-relational database management system that is frequently used to provide such services. Although this database management system has proven to be stable for many years, the two available open-source replication solutions, rserv and ERServer, had serious limitations and needed replacement.

Fortunately, such a replacement recently became available. Slony-I is a trigger-based master to multiple slaves replication system for PostgreSQL being developed by Jan Wieck. This enterprise-level replication solution works asynchronously and offers all key features required by data centers. Among the key Slony-I usage scenarios are:

  • Database replication from the head office to various branches to reduce bandwidth usage or speed up database requests.

  • Database replication to offer load balancing in all instances. This can be particularly useful for report generators or dynamic Web sites.

  • Database replication to offer high availability of database services.

  • Hot backup using a standby server or upgrades to a new release of PostgreSQL.

This article walks you through the steps required to install Slony-I and replicate a simple database located on the same machine. It also describes how Slony-I can be combined with high-availability solutions to provide automatic failover.

Installing Slony-I

To install Slony-I and replicate a simple database, first install PostgreSQL from source. Slony-I supports PostgreSQL 7.3.2 or higher; 7.4.x and 8.0 need the location of the PostgreSQL source tree when being compiled. If you prefer using PostgreSQL packages from your favorite distribution, simply rebuild them from the package sources and keep the package build location intact so it can be used when compiling Slony-I. That said, obtain the latest Slony-I release, which is 1.0.5, compile and install it. To do so, proceed with the following commands:


% tar -zxvf slony1-1.0.5.tar.gz
% cd slony1-1.0.5
% ./configure \
--with-pgsourcetree=/usr/src/redhat/BUILD/postgresql-7.4.5
% make install

In this example, we tell the Slony-I's configure script to look in /usr/src/redhat/BUILD/postgresql-7.4.5/ for the location of the PostgreSQL sources, the directory used when building the PostgreSQL 7.4.5 RPMs on Red Hat Enterprise Linux. The last command compiles Slony-I and installs the following files:

  • $postgresql_bindir/slonik: the administration and configuration script utility of Slony-I. slonik is a simple tool, usually embedded in shell scripts, used to modify Slony-I replication systems. It supports its own format-free command language described in detail in the Slonik Command Summary document.

  • $postgresql_bindir/slon: the main replication engine. This multithreaded engine makes use of information from the replication schema to communicate with other engines, creating the distributed replication system.

  • $postgresql_libdir/slony1_funcs.so: the C functions and triggers.

  • $postgresql_libdir/xxid.so: additional datatype to store transaction IDs safely.

  • $postgresql_datadir/slony1_base.sql: replication schema.

  • $postgresql_datadir/slony1_base.v73.sql.

  • $postgresql_datadir/slony1_base.v74.sql.

  • $postgresql_datadir/slony1_funcs.sql: replication functions.

  • $postgresql_datadir/slony1_funcs.v73.sql.

  • $postgresql_datadir/slony1_funcs.v74.sql.

  • $postgresql_datadir/xxid.v73.sql: a script used to load the additional datatype previously defined.

Generally, $postgresql_bindir points to /usr/bin/, $postgresql_libdir to /usr/lib/pgsql/ and $postgresql_datadir to /usr/share/pgsql/. Use the pg_config --configure command to display the parameters used when PostgreSQL was built to find the various locations for your own installation. Those files are all that is needed to offer a complete replication engine for PostgreSQL.

Figure 1. How the Slony-I replication engines work for a master with a slave database.

As you can see in Figure 1, Slony-I's main replication engine, slon, makes use of many threads. The synchronization thread verifies at a configurable interval if there has been replicable database activity, generating SYNC events if such activity happens. The local listen thread listens for new configuration events and modifies the cluster configuration and the in-memory configuration of the slon process accordingly.

As its name implies, the cleanup thread performs maintenance on the Slony-I schema, like removing old events or vacuuming the tables. The remote listen thread connects to the remote node's database to receive events from its event provider. When it receives events or confirmations, it selects the corresponding information and feeds the internal message queue of the remote workers thread. The replication data is combined into groups of transactions. The remote workers thread, one per remote node, does the actual data replication, events storing and generation of confirmations. At any moment, the slave knows exactly what groups of transactions it has consumed.

______________________

Comments

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

I was running 'subscribe.sh'

Anonymous's picture

I was running 'subscribe.sh' I got the error message below:
[root@ny0138 script]# ./subscribe.sh
:7: NOTICE: subscribe set: omit_copy=f
:7: NOTICE: subscribe set: omit_copy=f
CONTEXT: SQL statement "SELECT "_sql_cluster".subscribeSet_int( $1 , $2 , $3 , $4 , $5 )"
PL/pgSQL function "subscribeset" line 68 at PERFORM

Can you help me ?

Thanks

M

Postgres 8.4 changes

Jarrod Neven's picture

Postgres 8.4
1)you need to change cluster_setup.sh script to include the parameter 'event note' as below.

store node (id = 2, comment = 'Node 2', event node = 1);

2) supply the parameter password= for 'node' and 'store path' lines

Hi everyone would like to

Anonymous's picture

Hi everyone would like to know how to make Failback. I do the failover, I would like to add the node failure but get an error.
After running the failover script, run on the master (node 2) the following script to add the failed node (node 1) as a slave:

where to keep cluster_setup.sh and run?

Santhosh's picture

Hi all,
I have tried the above steps and created the db's but i got struck in creating the cluster_setup.sh and running. When i run this shell script, it says: ./cluster_setup.sh: line 10: slonik: command not found. i installed slony.
Can anyone plz help me. i'm in need of this help urget!!!! Plz!plz!!!
Regards,
Santhosh Goud

whatever locaion...just make sure the script has proper permissi

Anonymous's picture

whatever location...just make sure the script has proper permissions.
cd to the location where the script is and issue the slonik command.
If slonik is fine then there is no problem with running the script from there.
Do so as the "postgres" user.

Are you sure the slonik command is in the same bin directory

Aziz Sharief's picture

Hi Santosh,

Make sure the slony command is in the same directory as your postgres binaries like
psql, pg_dump..etc.

Are you running the cluster_setup.sh as a postgres user or rootuser?

Thanks & Regards,
Aziz Sharief

Detect DB synchronize

werner's picture

hi,

i have been looking for answer to question of how do i know the db are synchronize without dumping the db data to do a comparison? Is there anyway other means which can be done?

If not for the "diff" solution then you may have to compare each

Anonymous's picture

If not for the "diff" solution then you may have to compare each
table in masters DB with the corresponding table in slave db.

One hint is use EXISTS to do the comparison.

select * from mastertable as a where not exists ( select 1 from slavetable as b where
a.columnname = b.columnname and .....so on);

If you get zero results that indicates that the two tables are similar.
One disadvantage is you somehow have to get the tables you are comparing
into the same DB which ivolves some work to be done(like rename one table, its constarinst ..indexes) etc.

slony-I relication

venu madhav's picture

Dear Sir,
We are using PostgreSQL and Slony-I from last 4 years.
Slony-I 1.1.5 is implemented with 1 master + 4 slaves on PostgreSQL 8.1 databases.
This is working fine from last one year.
Now we want to increase Slaves from 4 to 10.
Is it possible with Slony-I 1.1.5 to replicate One Master with 10 slaves.

Thanks & Regards
Ch Venu Madhav
STO/CR&D/ECIL

Problems with Failing Over

Carlos Hernán's picture

Hi, i have problems with Failing Over script in heart beat, someone can tell me how implement promote.sh in heartbeat.

Thanks...

Excellent Article

Anonymous's picture

Good job at breaking things down and keeping it simple. This review of Slony-I should be link of their main website for people looking for a quick yet structured walk-through.

Keep up the good work ;)

SERVERS ON DIFFERENT MACHINE

dilsha's picture

HI....

nice tutorial

In my case i have both master and slave on different machine so is it necessary to execute the script files and to start the slon engine on both the systems?

plz help me
Thanks

In case of two database server

Mahendra's picture

In case of two database server. Do I need to run both cluster_setup.sh and subscribe.sh tobe executed on both database?
Also artical specifies that in node defination host=FQDN. Can we give IP address in this case?

Thanks

Hi guys!

Giacomo Orizzonte's picture

exelent tutorial Ludovic!

i try follow all steps, and i thnik all is ok but dont see in my replicating base any rows from master, i see two process slon (well is two diferent pc's) i run cluster.sh and suscribe.sh scripts in two pc's, whit out errors. and i see the mensajes for slon

( i open in console see this --------------------
2007-03-29 12:02:18 CDT CONFIG storeListen: li_origin=2 li_receiver=1 li_provider=2
2007-03-29 12:07:01 CDT DEBUG1 cleanupThread: 0.001 seconds for cleanupEvent()
2007-03-29 12:07:01 CDT DEBUG1 cleanupThread: 0.004 seconds for delete logs
-----------------------------
)

but dont start the sync process.

do you have any idea?

thanks very much.

thanx

Somu's picture

The article is well descriptive, and helped a lot for me to setup the secondary server for postgres

THANX A LOT

Do i Have to install SLONY in the Master and in the Slave server

Icebergdelphi's picture

Hi, i am from Chiapas Mexico, actually i have a Postgre Database installed in a CENTOS Linux Distro(In my Principal Office), but i need to stablish a 7 Servers with the same database and with CENTOS in every Branch of my company, my big question is: Do i have to Install SLONY in every Server in every branch of my company with the same configuration of the Master database?

I readed Database Replication with Slony-I, but i never saw something about the slaves configurations.
Tanx
and sorry 4 My English
Hiber Tadeo Moreno (IcebergDelphi)

timestamp problem

umar's picture

I've tried cluster_setup.sh above and get this error:
ERROR: invalid input syntax for type timestamp: "Tue Jun 27 14:06:04.735468 2006 WIT"

I've tried suggestion from http://gborg.postgresql.org/project/slony1/bugs/bugupdate.php?1300 but still didn't work. Any help will be appreciated.

Notes:
I change the U variable from postgres to my account with superuser privilege and add P variable for password. The default postgres user keep being asked for password. I'm using postgres and slony1 from debian package.

reply timestamp problem

irawan's picture

find this line in postgresql.conf and change
timezone=UTC
or
timezone=GMT

Automated failover Considered Harmful

Andrew Hammond's picture

The decision to failover (including accepting the potential loss of committed transactions) is not usually one you want software making for you. Typically in the event of a failure, it would make more sense to attempt to get the failed master up and stumbling along enough to perform a move set (the controlled, non-lossy alternative to failover). Failover is a last resort with async replication systems.

Also, it's worth at least mentioning the _excellent_ slony mailing list:
http://gborg.postgresql.org/mailman/listinfo/slony1

I followed the exact same ste

Steven's picture

I followed the exact same steps but when I was running 'subscribe.sh' I got the error message below:

:4: NOTICE: subscribeSet:: set 1 has no tables - risk of problems - see bug 1226
:4: NOTICE: http://gborg.postgresql.org/project/slony1/bugs/bugupdate.php?1226

Please help me solve this. Thanks.

It's normal. it's a NOTICE me

Anonymous's picture

It's normal. it's a NOTICE message, not ERROR message. Everything work like a charm (like async charm ;D)

U rock!!

dhenz's picture

I followed your stuff and I got it to work just one time!! u rock thanx!!

idle process

toone's picture

We are running slony to check for changes every 10 seconds but we observe that process are overlapping and tends to become idle thus using up all of 4GB of memory what can we do?

Webinar
One Click, Universal Protection: Implementing Centralized Security Policies on Linux Systems

As Linux continues to play an ever increasing role in corporate data centers and institutions, ensuring the integrity and protection of these systems must be a priority. With 60% of the world's websites and an increasing share of organization's mission-critical workloads running on Linux, failing to stop malware and other advanced threats on Linux can increasingly impact an organization's reputation and bottom line.

Learn More

Sponsored by Bit9

Webinar
Linux Backup and Recovery Webinar

Most companies incorporate backup procedures for critical data, which can be restored quickly if a loss occurs. However, fewer companies are prepared for catastrophic system failures, in which they lose all data, the entire operating system, applications, settings, patches and more, reducing their system(s) to “bare metal.” After all, before data can be restored to a system, there must be a system to restore it to.

In this one hour webinar, learn how to enhance your existing backup strategies for better disaster recovery preparedness using Storix System Backup Administrator (SBAdmin), a highly flexible bare-metal recovery solution for UNIX and Linux systems.

Learn More

Sponsored by Storix