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.
Replicating a Small Database

We first create the database we will replicate. This database contains a single table and sequence. Let's create a user contactuser, the contactdb database and activate the plpgsql programming language to this newly created PostgreSQL database by proceeding with the following commands:

% su - postgres
% createuser --pwprompt contactuser
Enter password for user "contactuser": (specify a
password)
Enter it again:
Shall the new user be allowed to create databases?
(y/ n) y
Shall the new user be allowed to create more new
users? (y/ n) n

% createdb -O contactuser contactdb
% createlang -U postgres -h localhost plpgsql \
contactdb

Then, we create the sequence and the table in the database we will replicate and insert some information in the table:


% psql -U contactuser contactdb

contactdb=> create sequence contact_seq start with 1;

contactdb=> create table contact (
 cid         int4 primary key,
 name        varchar(50),
 address     varchar(255),
 phonenumber varchar(15)
);

contactdb=> insert into contact (cid, name, address,
phonenumber) values ((select nextval('contact_seq')),
'Joe', '1 Foo Street', '(592) 471-8271');
contactdb=> insert into contact (cid, name, address,
phonenumber) values ((select nextval('contact_seq')),
'Robert', '4 Bar Roard', '(515) 821-3831');
contactdb=> \q

For the sake of simplicity, let's create a second database on the same system in which we will replicate the information from the contactdb database. Proceed with the following commands to create the database, add plpgsql programming language support and import the schema without any data from the contactdb database:


% su - postgres
% createdb -O contactuser contactdb_slave
% createlang -U postgres -h localhost plpgsql \
contactdb_slave
% pg_dump -s -U postgres -h localhost contactdb | \
psql -U postgres -h localhost contactdb_slave

Once the databases are created, we are ready to create our database cluster containing a master and a single slave. Create the Slonik cluster_setup.sh script and execute it. Listing 1 shows the content of the cluster_setup.sh script.

The first slonik command (cluster name) of Listing 1 defines the namespace where all Slony-I-specific functions, procedures, tables and sequences are defined. In Slony-I, a node is a collection of a database and a slon process, and a cluster is a collection of nodes, connected using paths between each other. Then, the connection information for node 1 and 2 is specified, and the first node is initialized (init cluster). Once completed, the script creates a new set to replicate, which is essentially a collection containing the public.contact table and the public.contact_seq sequence. After the creation of the set, the script adds the contact table to it and the contact_seq sequence. The store node command is used to initialize the second node (id = 2) and add it to the cluster (sql_cluster). Once completed, the scripts define how the replication system of node 2 connects to node 1 and how node 1 connects to node 2. Finally, the script tells both nodes to listen for events (store listen) for every other node in the system.

Once the script has been executed, start the slon replication processes. A slon process is needed on the master and slave nodes. For our example, we start the two required processes on the same system. The slon processes must always be running in order for the replication to take place. If for some reason they must be stopped, simply restarting allows them to continue where they left off. To start the replication engines, proceed with the following commands:


% slon sql_cluster "dbname=contactdb user=postgres" &
% slon sql_cluster "dbname=contactdb_slave user=postgres" &

Next, we need to subscribe to the newly created set. Subscribing to the set causes the second node, the subscriber, to start replicating the information of the contact table and contact_seq sequence from the first node. Listing 2 shows the content of the subscription script.

______________________

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