Slony-I Baisc Installation and Replication Setup Steps.

Source Installation:

Download the Slony-I source from http://slony.info/downloads/2.2/source/

#slony1-2.2.4.tar.bz2

#tar -xvf slony1-2.2.4.tar.bz2

#cd slony1-2.2.4

#./configure –prefix=/usr/local/pgsql/bin –with-pgconfigdir=/usr/local/pgsql/bin

#make

#make install

Step-1:

======

master=# select version();

version

--------------------------------------------------------------------------------------------------------------

PostgreSQL 9.1.9 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.6 20110731 (Red Hat 4.4.6-3), 64-bit

(1 row)

masterdb=# \c postgres

You are now connected to database "postgres" as user "postgres".

postgres=# create database master;

CREATE DATABASE

postgres=# \c master

You are now connected to database "master" as user "postgres".

master=# create table s1(id int primary key,name varchar(10));

NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "s1_pkey" for table "s1"

CREATE TABLE

master=# insert into s1 values(generate_series(1,10),('sample'));

INSERT 0 10

master=# select * from s1;

id | name

----+--------

1 | sample

2 | sample

3 | sample

4 | sample

5 | sample

6 | sample

7 | sample

8 | sample

9 | sample

10 | sample

(10 rows)

Step-2:

=====

slave=# select version();

version

--------------------------------------------------------------------------------------------------------------

PostgreSQL 9.4.6 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.6 20110731 (Red Hat 4.4.6-3), 64-bit

(1 row)

postgres=# create database slave;

CREATE DATABASE

postgres=# \c slave

You are now connected to database "slave" as user "postgres".

slave=# create table s1(id int primary key,name varchar(10));

CREATE TABLE

Step-3:

=====

Setup replication, we need to create below scripts :

[postgres@localhost slon_test1]$ more initalize_BASICBLD.slonik

## Script for Initialization.

cluster name = basicbld;

node 1 admin conninfo = 'dbname=master host=192.168.205.157 user=postgres port=5432 password=adminedb';

node 2 admin conninfo = 'dbname=slave host=192.168.205.162 user=postgres port=5432 password=adminedb';

init cluster (id = 1, comment = 'Primary node for slave');

#### Setting Store Nodes ###

store node (id = 2, event node = 1, comment = 'Slave Node For The Primary Node 1');

#### Storing all nodes in the Slony-I catalogs in schema _basicbld

store path(server = 1, client = 2, conninfo = 'dbname=master host=192.168.205.157 user=postgres port=5432 password=adminedb');

store path(server = 2, client = 1, conninfo = 'dbname=slave host=192.168.205.162 user=postgres port=5432 password=adminedb');

[postgres@localhost slon_test1]$ more create_set_BASICBLD.slonik

## Script for Create_set

cluster name = basicbld;

node 1 admin conninfo = 'dbname=master host=192.168.205.157 user=postgres port=5432 password=adminedb';

node 2 admin conninfo = 'dbname=slave host=192.168.205.162 user=postgres port=5432 password=adminedb';

#-----------------------------

# Creating sets for Tables

#-----------------------------

#---- Set 1 --------#

try { create set (id = 1, origin = 1, comment = 'Set 1 for shadow'); } on error { echo 'Failed...set 1'; exit 1;}

echo 'Set 1 ...created';

set add table (set id = 1, origin = 1, id = 1, full qualified name = 'public.s1', comment = 'Table public.s1 with primary key');

echo 'PKey table *** public.s1 *** added.';

[postgres@localhost slon_test1]$ more subscribe_set_BASICBLD.slonik

## Script for subscribe_set

cluster name = basicbld;

node 1 admin conninfo = 'dbname=master host=192.168.205.157 user=postgres port=5432 password=adminedb';

node 2 admin conninfo = 'dbname=slave host=192.168.205.162 user=postgres port=5432 password=adminedb';

try { subscribe set (id = 1, provider = 1 , receiver = 2, forward = yes, omit copy = false); } on error { exit 1;}

echo 'Set 1 subscribed to nodes 1';

[postgres@localhost slon_test1]$ more primary_start.sh

#!/bin/bash

# starting slon on Origin

# Change the binary location as per Origin Node

# Slony binaries/ip/clustername/conninfo according to node

DATE=$(date +%G%m%d)

SLONLOG="/tmp/primary_$DATE.log"

/usr/local/pgsql/bin/slon -s 1000 basicbld 'host=192.168.205.157 dbname=master user=postgres port=5432' >$SLONLOG 2>&1 &

exit

[postgres@localhost slon_test1]$ more slave_start.sh

#!/bin/bash

# starting slon on Subscriber

# Change Slony binaries/ip/clustername/conninfo according to

# Subscriber node.

DATE=$(date +%G%m%d)

SLONLOG="/tmp/slave_$DATE.log"

/usr/local/pgsql/bin/slon -s 1000 basicbld 'host=192.168.205.162 dbname=slave user=postgres port=5432' >$SLONLOG 2>&1 &

exit

 

Step-4

=====

Execute scripts in the below order:

[postgres@localhost slon_test1]$ /usr/local/pgsql/bin/slonik /tmp/slon_test1/initalize_BASICBLD.slonik

[postgres@localhost slon_test1]$ /usr/local/pgsql/bin/slonik /tmp/slon_test1/create_set_BASICBLD.slonik

/tmp/slon_test1/create_set_BASICBLD.slonik:13: Set 1 ...created

/tmp/slon_test1/create_set_BASICBLD.slonik:16: PKey table *** public.s1 *** added.

[postgres@localhost slon_test1]$ sh prymary_start.sh

[postgres@localhost slon_test1]$ sh slave_start.sh

[postgres@localhost slon_test1]$ /usr/local/pgsql/bin/slonik /tmp/slon_test1/subscribe_set_NEWSLN.slonik

/tmp/slon_test1/subscribe_set_NEWSLN.slonik:8: Set 2 subscribed to nodes 3

slave=# select * from s1;

id | name

----+--------

1 | sample

2 | sample

3 | sample

4 | sample

5 | sample

6 | sample

7 | sample

8 | sample

9 | sample

10 | sample

(10 rows)

Step-5

=====

master=# insert into s1 values(generate_series(11,15),('sample'));

INSERT 0 5

slave=# select * from s1;

id | name

----+--------

1 | sample

2 | sample

3 | sample

4 | sample

5 | sample

6 | sample

7 | sample

8 | sample

9 | sample

10 | sample

11 | sample

12 | sample

13 | sample

14 | sample

15 | sample

(15 rows)

For more information,Slony-I documentation is the best.

http://www.slony.info/adminguide/2.2/doc/adminguide/slony.pdf

And also you can go through the below, one of our senior Raghav blogspot will help you in short .

http://raghavt.blogspot.in/search?q=slony+installation