Slony replication using perl tools

I have been working on Slony for a couple of years now and have started to feel very comfortable with this replication solution. For beginners it might not be so user friendly but as long as you understand the working of this replication module things will start becoming easy. I am going to list out easy ways to setup, configure and maintain this replication module.

Environment:

I have 2 CentOS boxes that I am going to setup replication on. You can have as many as you want its as simple as adding another node and subscribing the data to it. PostgreSQL version I am using is 8.4.9. Same steps would follow in any other version of postgres. Slony version I have is 2.1.0. It is always a good idea to read the release notes of Slony version to check the compatibility with the postgres server version.

There are two ways to use Slony. Shell or Perl, I prefer perl its easier and reliable option. Although I do not see much information on the web about using perl tools.

Installation:

I assume you have postgres installed on both the servers and and the Master node has the database setup and ready to be replicated. I also assume that the tables you are going to replicate have primary keys on them as this is a requirement for setting up replication.

1. Download slony 2.1.0 source from the following link:

http://slony.info/downloads/2.1/source/slony1-2.1.0.tar.bz2

2. This is a source install and we would need to configure with the current install location in this case it is ( /opt/PostgreSQL/8.4 ) use prefix option for it and then make and make install. Eg:

./configure '--prefix=/opt/slony/slony2.1'
'--with-perltools=/opt/slony/slony2.1'
'--with-pgconfigdir=/opt/PostgreSQL/8.4/bin'

make
make install

The above step needs to be performed on all nodes (master + slaves)

NOTE: This would be a good time to add a schema only copy of the database to all the slony slaves. If you already have some data in the slave tables it will be deleted when we start the slony subscribe process.

3. Create slony configuration file. This is the file that will list information on all the master and slave nodes and tables and sequences that will be added to replication. Sample copy of the config file is given below for reference.

if ($ENV{SLONYNODES}) {
require $ENV{SLONYNODES};
} else {
$CLUSTER_NAME = 'slony_test';
$LOGDIR = '/postgres/tracelogs/slony_log';
$MASTERNODE = 1;
add_node(node => 1,
host => '127.0.0.1',
dbname => 'test',
port => 6543,
user => 'postgres',
password => );
add_node(node => 2,
host => '127.0.0.1',
dbname => 'test',
port => 6543,
user => 'postgres',
password => );

#(you can add more nodes here by copying the above node)

}

$SLONY_SETS = {
"set1" => {
"set_id" => 1 ,
"table_id" => 1 ,
"sequence_id" => 1 ,
"pkeyedtables" => [
"public.test",
"public.test1",
"public.test2",
"public.test3",
],
"keyedtables" => {},
"serialtables" => [],
"sequences" => [

#sequences go in this block for the 1st set
"public.test_id_seq",
"public.test1_id_seq",
],
},
};
if ($ENV{"SLONYSET"}) {
require $ENV{"SLONYSET"};
}
1;

4. Initialize the slony cluster using the config file we created above:

./slonik_init_cluster -c slon_test.conf |slonik

5. Now we start slon process as:

./slon_start -c slon_test.conf 1

./slon_start -c slon_test.conf 2

What we are doing above is starting slon processes on node 1 and node 2. A good practice here would be start these processes on the slave box so that they use memory on the slony slave.

6. Then we need to create the set:

./slonik_create_set -c slon_test.conf 1|slonik

7. Last step is to subscribe the set for one node at a time :

./slonik_subscribe_set -c slon_test.conf 1 2|slonik

Make no mistake here or your master node will be empty by the time subscribe set completes.

slon_test.conf 1 2 above me that subscribe set 1 to node 2 if you make a mistake by doing slon_test.conf 2 1 slony will truncate tables on the master node and you will loose data.

8. Now its waiting time. let the initial sync complete. Its a good idea here to look at the log files for any errors and also the sl_status view in the slony schema in your database to check how things are moving along.

Maintaining slony replication:

The easiest part about maintaining slony replication is to remember never ever do a DDL on slony nodes without slonik_execute or taking the table out of replication. Reason is that slony does not replicate DDL statements to nodes it only does DML.

slonik_execute_script is a utility that will let you alter a table without breaking replication

if you have multiple DDL statements just put them in a script and call it using slonik_execute_script

execute_script [options] set# full_path_to_sql_script_file
execute_script [options] -c SCRIPT set#

Executes the contents of a SQL script file on the specified set.
The script only needs to exist on the machine running the slon
daemon.

set# The set to which this script applies.

-c SCRIPT Pass the SQL to be executed via the command line instead
of as a file.

-n NUM
--node=NUM Override the set origin specified in the configuration
file.

If you need to add a node or drop a node or a table or do much more about managing slony. There are a bunch of scripts available in the slony install directory. I am not going to explain them as they are pretty straight forward. Refer the below document for further details on it and if you need help with slony just leave a comment and I will be sure to write back.

http://slony.info/adminguide/2.1/doc/adminguide/slony.pdf

Advertisements

, , ,

  1. #1 by Database Technologies on May 26, 2012 - 3:11 pm

    Nice one! This is good blog.

  2. #2 by Vinod T Veettil on April 7, 2015 - 7:44 pm

    Found it very helpful to begin slony. Thanks.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: