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.

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

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?

White Paper
Linux Management with Red Hat Satellite: Measuring Business Impact and ROI

Linux has become a key foundation for supporting today's rapidly growing IT environments. Linux is being used to deploy business applications and databases, trading on its reputation as a low-cost operating environment. For many IT organizations, Linux is a mainstay for deploying Web servers and has evolved from handling basic file, print, and utility workloads to running mission-critical applications and databases, physically, virtually, and in the cloud. As Linux grows in importance in terms of value to the business, managing Linux environments to high standards of service quality — availability, security, and performance — becomes an essential requirement for business success.

Learn More

Sponsored by Red Hat

White Paper
Private PaaS for the Agile Enterprise

If you already use virtualized infrastructure, you are well on your way to leveraging the power of the cloud. Virtualization offers the promise of limitless resources, but how do you manage that scalability when your DevOps team doesn’t scale? In today’s hypercompetitive markets, fast results can make a difference between leading the pack vs. obsolescence. Organizations need more benefits from cloud computing than just raw resources. They need agility, flexibility, convenience, ROI, and control.

Stackato private Platform-as-a-Service technology from ActiveState extends your private cloud infrastructure by creating a private PaaS to provide on-demand availability, flexibility, control, and ultimately, faster time-to-market for your enterprise.

Learn More

Sponsored by ActiveState