- Slony-I – introduction:
Slony-I
is a "master to multiple slaves" replication system
for PostgreSQL
supporting cascading (e.g. - a node
can feed another node which feeds another node) and failover.
The big picture
for the development of Slony-I is that it is a master-slave
replication system that includes all features and capabilities needed
to replicate large databases to a reasonably limited number of slave
systems. Slony-I is a system designed for use at data centers and
backup sites, where the normal mode of operation is that all nodes
are available.
- Tools:
Postgres-9.2, PgAdmin 1.16, slon version 2.0.7 on UBUNTU 12.04
- Database setupThe data will be replicated from database source ,public schema with table emp_details to to target databse,public schema and emp_details table.User is postgres, host=localhost both the database are on the same machine (same host and port)
- Replication Process with PgAdminCreate to database “source” and “target” .Create a schema-only dump of the source database, and load it into target.Everything has to be done into the Replication items inside the two newly created databases. The two replication objects need references to each other .
1- Create base replication objects
For the Master :
- Right click on source > Replication and choose "New Slony-I Cluster"
- Join existing cluster : Do not check this !
- Cluster name : choose a name, here it is : REP_CLUSTER
- Local Node Left : 1
- Local Node Right : master_node
- Admin Node Left : 99
- Admin Node Right : admin_node
- Press OK.
For the Slave :
- Right click on target > Replication and choose "New Slony-I Cluster"
- Join existing cluster : Has to be checked
- Server : localhost
- Database : source
- Cluster name : REP_CLUSTER (should be automatically recognized)
- Local Node Left : 10
- Local Node Right : slave_node
- Admin Node : 99 - admin_node
- Press OK.
2 – Create path setup
For the Master :
- Right click on master_node Path and choose New path
- Server : 10 - Slave Node
- Connect Info : host=localhost port=5434 user=postgres password= “” dbname=target
- Conn retry : 10
- Press OK.
For the Slave :
- Right click on slave_node > Path and choose New path on target
- Server : 1 - master_node
- Connect Info : host=localhost port=5434 user=postgres password=”” dbname=source
- Conn retry : 10
- Press OK.
Similarly create path for each node (slave on source and master on
target)
3 – Define replication set on source
- In the Db source > REP_CLUSTER create a Replication Sets and choose new Replication Set
- ID : 1
- Comment : Replication Set 1
Now we choose the table we want to be
replicated from source to target .
- In the Replication Set 1 choose New tables
- Table : Any table of your choice e.g public.details (You will have to select table from the list that you want to replicate.)
- ID : 1
- Index : Auto filled when choosing table. That’s important to have a unique constraint on the table you want to replicate (primary key for instance).
- Comment : a nice comment
- Press OK.
4 – Create a new Subscription (screen caps below)
- Source >Replication Set > Subscription and choose New subscription.
- Origin : write “1”
- Provider : Choose “1 - Master Node”
- Receiver : Choose “10 - Slave Node”
- Can forward : Keep it unchecked
- Press OK.
Now check path and listen are same in source and target of admin
node .If not add master_node (slave_node), slave_node(slave_node) in
the target as well .
Start the replication
Go to slon path
/usr/lib/postgresql/9.2/bin bin (or any other location depending
on your linux flavour and / or Postgresql installation path). Check
that slon is
there in the path. This is the slony daemon you will
start twice : one for the MASTER(source), the other for the SLAVE
(target).
slon syntax :
slon
[Options] [ClusterName] [ConnexionInfos]
For the Master :
- ./slon -d 1 REP_CLUSTER "dbname=source user=postgres
host=localhost port=5434"
For the Slave :
- ./slon -d 1 REP_CLUSTER "dbname=target user=postgres
host=localhost port=5434"
Refer to the sample output of source and target .
Test the Replication
Add some data in the details table of
source database and check whether it is replicated at the target.
Reference:
