Database Replication with Slony-I

From Issue #134
June 2005

Apr 28, 2005 By Ludovic Marcotte

Linux Journal

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.

Listing 4. promote.sh


#!/bin/bash

CLUSTER=sql_cluster
H1=master.example.com
H2=slave.example.com
U=postgres

DB1=contactdb
DB2=contactdb

su - postgres -c slonik <<_EOF_
cluster name = $CLUSTER;

node 1 admin conninfo = 'dbname=$DB1 host=$H1 user=$U';
node 2 admin conninfo = 'dbname=$DB2 host=$H2 user=$U';

failover (id = 1, backup node = 2);
drop node (id = 1, event node = 2);

From Listing 4, the failover Slonik command is used to indicate that the node with id = 1, the node running on master.example.com, has failed, and that the node with id = 2 will take over all sets from the failed node. The second command, drop node, is used to remove the node with id = 1 from the replication system completely. Eventually, you might want to bring back the failed node in the cluster. To do this, you must configure it as a slave and let Slony-I replicate any missing information. Eventually, you can proceed with a switchback to the initial master node by locking the set (lock set), waiting for all events to complete (wait for event), moving the set to a new origin (move set) and waiting for a confirmation that the last command has completed. Please refer to the Slonik Command Summary for more information on those commands.

Conclusion

Replicating databases using Slony-I is relatively simple. Combined with the Linux-HA Heartbeat, this allows you to offer high availability of your database services. Although the combination of Slony-I and Linux HA-Heartbeat is an attractive solution, it is important to note that this is not a substitute for good hardware for your database servers.

Even with its small limitations, like not being able to propagate schema changes or replicate large objects, Slony-I is a great alternative to both rserv and ERServer and is now, in fact, the preferred solution for replicating PostgreSQL databases. Slony-II even supports synchronous multimaster replication and is already on the design table.

To conclude, I would like to thank Jan Wieck, the author of Slony-I, for reviewing this article.

Resources for this article: /article/8202.

Ludovic Marcotte (ludovic@sophos.ca) holds a Bachelor's degree in Computer Science from the University of Montréal. He is currently a software architect for Inverse, Inc., an IT consulting company located in downtown Montréal.

______________________

Comments

I was running 'subscribe.sh'

Submitted by Anonymous on Wed, 05/12/2010 - 09:24.

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

Postgres 8.4 changes

Submitted by Jarrod Neven (not verified) on Thu, 10/29/2009 - 05:08.

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

Submitted by Anonymous (not verified) on Tue, 06/02/2009 - 14:07.

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?

Submitted by Santhosh (not verified) on Mon, 06/01/2009 - 05:10.

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

Submitted by Anonymous (not verified) on Mon, 06/01/2009 - 07:11.

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

Submitted by Aziz Sharief (not verified) on Mon, 06/01/2009 - 07:05.

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

Submitted by werner (not verified) on Tue, 04/21/2009 - 09:04.

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

Submitted by Anonymous (not verified) on Mon, 06/01/2009 - 07:17.

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

Submitted by venu madhav (not verified) on Wed, 03/18/2009 - 03:30.

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

Submitted by Carlos Hernán (not verified) on Wed, 11/21/2007 - 19:48.

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

Thanks...

Excellent Article

Submitted by Anonymous (not verified) on Mon, 10/29/2007 - 22:21.

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

Submitted by dilsha (not verified) on Wed, 05/30/2007 - 04:53.

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

Submitted by Mahendra (not verified) on Mon, 04/23/2007 - 08:30.

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!

Submitted by Giacomo Orizzonte (not verified) on Thu, 03/29/2007 - 12:37.

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

Submitted by Somu (not verified) on Thu, 02/08/2007 - 08:01.

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

Submitted by Icebergdelphi (not verified) on Mon, 01/15/2007 - 10:08.

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

Submitted by umar (not verified) on Tue, 06/27/2006 - 02:26.

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

Submitted by irawan (not verified) on Thu, 07/13/2006 - 22:03.

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

Automated failover Considered Harmful

Submitted by Andrew Hammond (not verified) on Fri, 08/05/2005 - 09:20.

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

Submitted by Steven (not verified) on Thu, 08/04/2005 - 01:47.

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

Submitted by Anonymous (not verified) on Wed, 08/17/2005 - 05:50.

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

U rock!!

Submitted by dhenz (not verified) on Thu, 12/14/2006 - 22:13.

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

idle process

Submitted by toone (not verified) on Tue, 06/12/2007 - 07:55.

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?

Webcast

More Resources

How to Build an Optimal Hadoop Cluster to Store and Maintain Unlimited Amounts of Data Using Microservers

Realizing the promise of Apache® Hadoop® requires the effective deployment of compute, memory, storage and networking to achieve optimal results. With its flexibility and multitude of options, it is easy to over or under provision the server infrastructure, resulting in poor performance and high TCO. Join us for an in depth, technical discussion with industry experts from leading Hadoop and server companies who will provide insights into the key considerations for designing and deploying an optimal Hadoop cluster.

Learn More


Designing Electronics with Linux	May 22, 2013
Dynamic DNS—an Object Lesson in Problem Solving	May 21, 2013
Using Salt Stack and Vagrant for Drupal Development	May 20, 2013
Making Linux and Android Get Along (It's Not as Hard as It Sounds)	May 16, 2013
Drupal Is a Framework: Why Everyone Needs to Understand This	May 15, 2013
Home, My Backup Data Center	May 13, 2013

Enter to Win an Adafruit Pi Cobbler Breakout Kit for Raspberry Pi

It's Raspberry Pi month at Linux Journal. Each week in May, Adafruit will be giving away a Pi-related prize to a lucky, randomly drawn LJ reader. Winners will be announced weekly.

Fill out the fields below to enter to win this week's prize-- a Pi Cobbler Breakout Kit for Raspberry Pi.

Congratulations to our winners so far:

5-8-13, Pi Starter Pack: Jack Davis
5-15-13, Pi Model B 512MB RAM: Patrick Dunn
5-21-13, Prototyping Pi Plate Kit: Philip Kirby
Next winner announced on 5-27-13!

Full Name: *

Email: *

Job Title: *

Country: *

Free Webinar: Hadoop

How to Build an Optimal Hadoop Cluster to Store and Maintain Unlimited Amounts of Data Using Microservers

Some of key questions to be discussed are:

What is the “typical” Hadoop cluster and what should be installed on the different machine types?
Why should you consider the typical workload patterns when making your hardware decisions?
Are all microservers created equal for Hadoop deployments?
How do I plan for expansion if I require more compute, memory, storage or networking?

Register now for this free webinar.

Developer Poll

Already a subscriber? Click here for subscriber services.

First Name	Last Name

Address 1	Address 2

City	State	Zip

Email