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.

Much like Listing 1, subscribe.sh starts by defining the cluster namespace and the connection information for the two nodes. Once completed, the subscribe set command causes the first node to start replicating the set containing a single table and sequence to the second node using the slon processes.

Once the subscribe.sh script has been executed, connect to the contactdb_slave database and examine the content of the contact table. At any moment, you should see that the information was replicated correctly:

% psql -U contactuser contactdb_slave
contactdb_slave=> select * from contact;
 cid |  name  |   address    |  phonenumber
-----+--------+--------------+----------------
   1 | Joe    | 1 Foo Street | (592) 471-8271
   2 | Robert | 4 Bar Roard  | (515) 821-3831

Now, connect to the /contactdb/ database and insert a row:

% psql -U contact contactdb
contactdb=> begin; insert into contact (cid, name,
 address, phonenumber) values
 ((select nextval('contact_seq')), 'William',
 '81 Zot Street', '(918) 817-6381'); commit;

If you examine the content of the contact table of the contactdb_slave database once more, you will notice that the row was replicated. Now, delete a row from the /contactdb/ database:

contactdb=> begin; delete from contact
 where cid = 2; commit;

Again, by examining the content of the contact table of the contactdb_slave database, you will notice that the row was removed from the slave node correctly.

Instead of comparing the information for contactdb and contactdb_slave manually, we easily can automate this process with a simple script, as shown in Listing 3. Such a script could be executed regularly to ensure that all nodes are in sync, notifying the administrator if that is no longer the case.

Although replicating a database on the same system isn't of much use, this example shows how easy it is to do. If you want to experiment with a replication system on nodes located on separate computers, you simply would modify the DB2, H1 and H2 environment variables from Listing 1 to 3. Normally, DB2 would be set to the same value as DB1, so an application always refers to the same database name. The host environment variables would need to be set to the fully qualified domain name of the two nodes. You also would need to make sure that the slon processes are running on both computers. Finally, it is good practice to synchronize the clocks of all nodes using ntpd or something similar.

Later, if you want to add more tables or sequences to the initial replication set, you can create a new set and use the merge set slonik command. Alternatively, you can use the set move table and set move sequence commands to split the set. Refer to the Slonik Command summary for more information on this.

Failing Over

In case of a failure from the master node, due to an operating system crash or hardware problem, for example, Slony-I does not provide any automatic capability to promote a slave node to become a master. This is problematic because human intervention is required to promote a node, and applications demanding highly available database services should not depend on this. Luckily, plenty of solutions are available that can be combined with Slony-I to offer automatic failover capabilities. The Linux-HA Heartbeat program is one of them.

Figure 2. Heartbeat switches the IP alias to the slave node in case the master fails.

Consider Figure 2, which shows a master and slave node connected together using an Ethernet and serial link. In this configuration, the Heartbeat is used to monitor the node's availability through those two links. The application makes use of the database services by connecting to PostgreSQL through an IP alias, which is activated on the master node by the Heartbeat. If the Heartbeat detects that the master node has failed, it brings the IP alias up on the slave node and executes the slonik script to promote the slave as the new master.

The script is relatively simple. Listing 4 shows the content of the script that would be used to promote a slave node, running on slave.example.com, so it starts offering all the database services that master.example.com offered.

______________________

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