Replication with slony-I

Started by Abdul Rahmanover 17 years ago13 messagesgeneral

Jump to latest

Abdul Rahman

abr_ora@yahoo.com

over 17 years ago

May any one support step by step procedure for the replication with slony-I in windows xp.

Emanuel Calvo Franco

postgres.arg@gmail.com

over 17 years ago

In reply to: Abdul Rahman (#1)

Re: Replication with slony-I

http://slony.blogspot.com/

sorry is in spanish, but is an exelent resource,

i hope it will be useful

reggards
2008/10/27 Abdul Rahman <abr_ora@yahoo.com>:

May any one support step by step procedure for the replication with slony-I
in windows xp.

--
Emanuel Calvo Franco
Syscope Postgresql DBA
BaPUG Member

Raymond O'Donnell

rod@iol.ie

over 17 years ago

In reply to: Abdul Rahman (#1)

Re: Replication with slony-I

On 27/10/2008 11:17, Abdul Rahman wrote:

May any one support step by step procedure for the replication with
slony-I in windows xp.

There's a pretty good step-by-step guide in the Slony documentation - I
haven't it to hand, but it's entitled "Replicating your first database"
or something like that. As I recall, it's based on a *nix setup, but
Windows is mentioned too.

Off the top of my head, the only major difference is that instead of
running a separate slon daemon for each node, on Windows you run a
single slon as a service, and then invoke it with "/addengine" for each
node...it's all in the docs.

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

Abdul Rahman

abr_ora@yahoo.com

over 17 years ago

In reply to: Raymond O'Donnell (#3)

Re: Replication with slony-I

Thanks a lot Ray!

I have found the document and going to follow this. If I face any difficulty then will prompt to archive.

Regards,

Abdul.

--- On Mon, 10/27/08, Raymond O'Donnell <rod@iol.ie> wrote:
From: Raymond O'Donnell <rod@iol.ie>
Subject: Re: [GENERAL] Replication with slony-I
To: abr_ora@yahoo.com
Cc: pgsql-general@postgresql.org
Date: Monday, October 27, 2008, 7:17 PM

On 27/10/2008 11:17, Abdul Rahman wrote:

May any one support step by step procedure for the replication with
slony-I in windows xp.

There's a pretty good step-by-step guide in the Slony documentation - I
haven't it to hand, but it's entitled "Replicating your first
database"
or something like that. As I recall, it's based on a *nix setup, but
Windows is mentioned too.

Ray.

Ivano Luberti

luberti@archicoop.it

over 17 years ago

In reply to: Abdul Rahman (#4)

Re: Replication with slony-I

Can you please publicly share a link to this document ?

Abdul Rahman ha scritto:

Thanks a lot Ray!

I have found the document and going to follow this. If I face any
difficulty then will prompt to archive.

Regards,

Abdul.
--- On *Mon, 10/27/08, Raymond O'Donnell /<rod@iol.ie>/* wrote:
From: Raymond O'Donnell <rod@iol.ie>
Subject: Re: [GENERAL] Replication with slony-I
To: abr_ora@yahoo.com
Cc: pgsql-general@postgresql.org
Date: Monday, October 27, 2008, 7:17 PM

On 27/10/2008 11:17, Abdul Rahman wrote:

May any one support step by step procedure for the replication with
slony-I in windows xp.

There's a pretty good step-by-step guide in the Slony documentation - I
haven't it to hand, but it's entitled "Replicating your
first
database"
or something like that. As I recall, it's based on a *nix setup, but
Windows is mentioned too.

Off the top of my head, the only major difference is that instead of
running a separate slon daemon for each node, on Windows you run a
single slon as a service, and then invoke it with "/addengine" for
each
node...it's all in the docs.

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

--
==================================================
Archimede Informatica NEWS!
==================================================

Realizzato il Sistema Integrato per la biglietteria della Torre di Pisa:
prenotazione, vendita, pre-vendita ed emissione dei biglietti di ingresso
alla Torre sia online che presso le biglietterie dislocate sulla piazza:

_http://www.opapisa.it/boxoffice

_Partner del Progetto Ci-Tel "Front office Telematico per il cittadino"
Ente Coordinatore Comune di Pisa

_http://www.comune.pisa.it/doc/e-government.htm

_==================================================
dott. Ivano Mario Luberti
Archimede Informatica societa' cooperativa a r. l.
Sede Operativa
Via Gereschi 36 - 56126- Pisa
tel.: +39-050- 580959
tel/fax: +39-050-9711344
e-mail: archimede@archicoop.it
web: _http://www.archicoop.it

_ <http://www.archicoop.it/>__ <http://www.archicoop.it/>

Raymond O'Donnell

rod@iol.ie

over 17 years ago

In reply to: Ivano Luberti (#5)

Re: Replication with slony-I

On 28/10/2008 07:52, Ivano Luberti wrote:

Can you please publicly share a link to this document ?

Surely - the docs are here:

http://www.slony.info/documentation/

and the one you want ought to be listed in the table of contents.

Ray.

Abdul Rahman

abr_ora@yahoo.com

over 17 years ago

In reply to: Raymond O'Donnell (#6)

Re: Replication with slony-I

I followed the document entitled entitled "Replicating your first database" and got SUCCESS
In implementing the given example.

But, the implementation on existing database is not working for the tables already exists in the database. The replication is being only among four tables created via pgbench tool (accounts, history, branches, tellers).

whats going on!

--- On Tue, 10/28/08, Abdul Rahman <abr_ora@yahoo.com> wrote:
From: Abdul Rahman <abr_ora@yahoo.com>
Subject: Re: [GENERAL] Replication with slony-I
To: rod@iol.ie
Cc: pgsql-general@postgresql.org
Date: Tuesday, October 28, 2008, 9:05 AM

Thanks a lot Ray!

I have found the document and going to follow this. If I face any difficulty then will prompt to archive.

Regards,

Abdul.

--- On Mon, 10/27/08, Raymond O'Donnell <rod@iol.ie> wrote:
From: Raymond O'Donnell <rod@iol.ie>
Subject: Re: [GENERAL] Replication with slony-I
To: abr_ora@yahoo.com
Cc: pgsql-general@postgresql.org
Date: Monday, October 27, 2008, 7:17 PM

On 27/10/2008 11:17, Abdul Rahman wrote:

May any one support step by step procedure for the replication with
slony-I in windows xp.

There's a pretty good step-by-step guide in the Slony documentation - I
haven't it to hand, but it's entitled "Replicating your
first
database"
or something like that. As I recall, it's based on a *nix setup, but
Windows is mentioned too.

Ray.

Import Notes

Resolved by subject fallback

Raymond O'Donnell

rod@iol.ie

over 17 years ago

In reply to: Abdul Rahman (#7)

Re: Replication with slony-I

On 29/10/2008 11:00, Abdul Rahman wrote:

But, the implementation on existing database is not working for the
tables already exists in the database. The replication is being only
among four tables created via pgbench tool (accounts, history, branches,
tellers).

whats going on!

You'll need to tell us a *lot* more about what you've done, show us your
slonik scripts, etc etc - otherwise we're just guessing.

Also, it may be worth your while subscribing to the slony1-general
mailing list - details on www.slony.info.

Ray.

Abdul Rahman

abr_ora@yahoo.com

over 17 years ago

In reply to: Raymond O'Donnell (#8)

Re: Replication with slony-I

The link of the document, entitled "Replicating Your First Database" is as under:

http://slony1.projects.postgresql.org/slony1-1.2.6/doc/adminguide/firstdb.html

Import Notes

Resolved by subject fallback

#10

Abdul Rahman

abr_ora@yahoo.com

over 17 years ago

In reply to: Abdul Rahman (#9)

Re: Replication with slony-I

Ok Ray!

I will send the detail of my work soon.

Thanks to All participants.

Import Notes

Resolved by subject fallback

#11

Abdul Rahman

abr_ora@yahoo.com

over 17 years ago

In reply to: Abdul Rahman (#10)

Re: Replication with slony-I

The
detail of my work is as under:

As I
mentioned earlier that platform is windows xp and using postgres 8.2 in which
Slony- I is included. And all databases are at localhost.

After
reading the document entitled “Replicating Your First Database” which is for
Linux I optimized the instructions for windows and performed the following
tasks:

I didn’t find any line like tcpip_socket=true in my
postgresql.conf. Because there is another option and i.e. listen_addresses = '*'. I
changed it to listen_addresses = 'localhost'.

In pg_hba.conf, I changed the
line

host all all 127.0.0.1/32 md5

host all all 127.0.0.1/32 trust

Then created 2 databases
named: master and slave Executed the command

pgbench -i -s 1 -U $PGBENCHUSER -h $MASTERHOST $MASTERDBNAME After optimizing it i.e.: pgbench -i -s 1 -U postgres -h localhost master

Slony-I does not automatically copy
table definitions from a master when a slave subscribes to it, so we need
to import this data. I did this with pg_dump.

pg_dump -s -U postgres -h localhost master > schema.sql psql -U postgres -h localhost slave < schema.sql

Executed the command:

pgbench -s 1 -c 5 -t 1000 -U postgres -h localhost master

Optimized the given script (and used slonik to
run this) as follows:

cluster name =
pgbench;

node 1 admin
conninfo = 'dbname=master host=localhost user=postgres';

node 2 admin
conninfo = 'dbname=slave host=localhost user=postgres';

init cluster (
id=1, comment = 'Master Node');

table add key
(node id = 1, fully qualified name = 'public.history');

create set
(id=1, origin=1, comment='All pgbench tables');

set add
table (set id=1, origin=1, id=1, fully qualified name = 'public.accounts', comment='accounts
table');

set add table (set id=1, origin=1, id=2, fully qualified
name = 'public.branches', comment='branches table');

set add table (set id=1, origin=1, id=3, fully qualified
name = 'public.tellers', comment='tellers table');

set add table (set id=1, origin=1, id=4, fully qualified
name = 'public.history', comment='history table', key = serial);

store node
(id=2, comment = 'Slave node');

store path
(server = 1, client = 2, conninfo='dbname=master host=localhost
user=postgres');

store path
(server = 2, client = 1, conninfo='dbname=slave host=localhost user=postgres');

store listen
(origin=1, provider = 1, receiver =2);

store listen
(origin=2, provider = 2, receiver =1);

On MASTERHOST (localhost) the
command to start the replication executed the command:

slon pgbench "dbname=master user=postgres host=localhost"

On SLAVEHOST (localhost) the
command to start the replication executed the command:

slon pgbench "dbname=slave user=postgres host=localhost"

To start replicating the 4
pgbench tables (set 1) from the master (node id 1) the the slave (node id
2), executed the following script:

cluster name = pgbench;

node 1 admin conninfo = 'dbname=master
host=localhost user=postgres';

node 2 admin conninfo = 'dbname=slave host=localhost
user=postgres';

subscribe set ( id = 1, provider = 1, receiver
= 2, forward = no);

Got SUCCESS up to this point. Now I think
this is pgbench which is responsible for replicating tables.

Now my question is that how to include the tables which
are already present in an existing database

Import Notes

Resolved by subject fallback

#12

Raymond O'Donnell

rod@iol.ie

over 17 years ago

In reply to: Abdul Rahman (#11)

Re: Replication with slony-I

On 29/10/2008 12:55, Abdul Rahman wrote:

11. Got SUCCESS up to this point. Now I think this is *pgbench *which
is responsible for replicating tables.

No - it's Slony which is responsible for the replication. pgbench is
just executing lots of SQL commands - SELECTs, UPDATEs, INSERTs, etc -
while Slony is replicating the changes from one set to the other.

Now my question is that how to include the tables which are already
present in an existing database

Just add more SET ADD TABLE commands to your slonik script. Use the
existing lines as a model, so you'll get something like:

set add table (set id=1, origin=1, id=4, fully qualified name =
'public.new_table', comment='Another table');

Make sure that the id is different for each table.

HTH,

Ray.

#13

Abdul Rahman

abr_ora@yahoo.com

over 17 years ago

In reply to: Raymond O'Donnell (#12)

Re: Replication with slony-I

TOO MANY THANKS RAY!

I have got success in doing replication to an existing database. I did test on a dummy databases. But I am sure that I can implement to live project.

Regards,
Abdul Rehman.

Import Notes

Resolved by subject fallback