HI All,

 

Just thought it might be useful.

 

SLONY-I on Windows XP using Slony 1.2.16 and postgres 8.3

1.    Create 3 databases, master, slave1 and slave2 and ensure pl/pgsql
is setup in each.
 

2.    Create a schema with tables in the master database:
 

3.    Add a primary key to each table.
 

4.    Create a schema-only dump of the master database, and load it into
slave1 and slave2: 

[This step needs to be followed only if the slave does not exists
already.]

> pg_dump -s -U postgres master > schema.sql [creates an sql file with
commands necessary to create tables and insert data in it with which we
can build the slave databases]
> psql -U postgres slave1 < schema.sql [slave database gets contructed]
> psql -U postgres slave2 < schema.sql [slave database gets contructed]
 

5.    Create Slony config files for each slon engine (daemon on *nix).
The files should contain just the following two lines:



Contents of Master.conf

cluster_name='pgbench'
conn_info='host=ip of master port=5432 user=postgres dbname=master' 

Contents of Slave.conf

cluster_name='pgbench'
conn_info='host=ip slave port=5432 user=postgres dbname=master' 

[Create a file for each database, adjusting the dbname parameter as
required and adding any other connection options that may be needed. For
the IP, it's better to use domain names to ensure that all servers are
addressed uniformly in pgadmin and slonyi]
 

6.    (Windows only) Install the Slony-I service:

> slon -regservice Slony-I 

[Slony-II, Slony-II, Slony-A, etc could also be used]
 

7.    Register each of the engines (this is only necessary on Windows -
on *nix the slon daemons may be started individually and given the path
to the config file on the command line using the -f option):

> slon -addengine Slony-I C:\slony\master.conf
> slon -addengine Slony-I C:\slony\slave1.conf
> slon -addengine Slony-I C:\slony\slave2.conf 

 

8.    In pgAdmin under the Replication node in the master database,
create a new Slony-I cluster using the following options.

Create a cluster in the master database

Join existing cluster: Unchecked

Cluster name:          pgbench (any name)

Local node:            1        Master node

Admin node:            99       Admin node

    

9.    Under the Replication node, create a Slony-I cluster in each of
the slave databases using the following options:

Join existing cluster: Checked

Server:                <Select the server containing the master
database>

Database:              master

Cluster name:          pgbench

Local node:            10       Slave node 1

Admin node:            99 - Admin node

 

Join existing cluster: Checked

Server:                <Select the server containing the master
database>

Database:              master

Cluster name:          pgbench

Local node:            20       Slave node 2

Admin node:            99 - Admin node

    

10.  Create Paths on the master to both slaves, and on each slave back
to the master. Create the paths under each node on the master, using the
connection strings specified in the slon config files. Note that future
restructuring of the cluster may require additional paths to be defined.


Example 

Slave path in Master node =>host=ip/ dns port=5432 user=postgres
dbname=nlpg

Master path in slave node => host=ip/ dns port=5432 user=postgres
dbname=nlpg
 

11.  Create a Replication Set on the master using the following
settings: 

ID:                  1

Comment:             rep set1 (any name)

 

12.  Add the tables to the replication set using the following [existing
table names are available from the drop down]: 

Table:               public.accounts

ID:                  1

Index:               accounts_pkey

 

Table:               public.branches

ID:                  2

Index:               branches_pkey

 

Table:               public.history

ID:                  3

Index:               history_pkey

 

Table:               public.tellers

ID:                  4

Index:               tellers_pkey

 

[In this approach all the tables are being added to one replication set
in the master. Alternatively, we can add n replication set for each
table.]

    

13.  On the master node, create a new subscription for each slave using
the following options:

Origin:              1

Provider:            1 - Master node

Receiver:            10 - Slave node 1

 

Origin:              1

Provider:            1 - Master node

Receiver:            20 - Slave node 2

 

With 'Call forward' checked.

 

[Need not repeat this step for slave, since once the replication is up
and running, the replication sets are automatically created on the slave
clusters.]

    

14.  Start the slon service (or daemons on *nix): 

> net start Slony-I 

[Check if the slon service is up and running using the windows taskbar]
 

15.  Open 2 command prompt windows on the path postgres/8.3/bin where
slon is installed.

In the first window, type the command to bring the master database up.

slon cluster_name "host=ip/dns  dbname=nlpg user=postgres
password=abcdef"

In the second window, type the command to bring the slave database up
and listening to the master slon.

slon cluster_name "host= ip/dns  port=5432 dbname=nlpg user=postgres
password=abcd"

[Now, we have to wait until all the tables from the replication set(s)
in the master database are synchronised with those in the slave
database.Once the synchronisation is done, the replication is ready to
be tested.]

 

 

 

Regards,

Don

donsasikumar at gmail.com



Intelligent Addressing Ltd. Registered Office: 1 Adam Street, London WC2N 6DD Reg. in England No.3863861 VAT No.GB 802258946

This message has been scanned for viruses by BlackSpider MailControl - www.blackspider.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.slony.info/pipermail/slony1-general/attachments/20091106/e41dc7ef/attachment.htm