Slony1 PostgreSQL Replication - Nodes in multiple Clusters

Author

Nodes in multiple Clusters

Tang, Jason

2005-06-14, 11:24 am

Hi

Would I be assuming correct that a node can belong to more than one
cluster. It would mean I just have to call slonik twice with commands
describing a different clustername, but can reuse the node ids?

Thanks
Jase

Darcy Buskermolen

2005-06-14, 11:24 am

On Tuesday 14 June 2005 08:28, Tang, Jason wrote:
> Hi
>
> Would I be assuming correct that a node can belong to more than one
> cluster. It would mean I just have to call slonik twice with commands
> describing a different clustername, but can reuse the node ids?

Yes this is correct

>
> Thanks
> Jase
> ____________________
____________________
_______
> Slony1-general mailing list
> Slony1-general- AuKwsB3Fm+ugFIWk8tvy
RWD2FQJk+8+b@public.gmane.org
> http://gborg.postgresql.org/mailman.../slony1-general

--
Darcy Buskermolen
Wavefire Technologies Corp.

http://www.wavefire.com
ph: 250.717.0200
fx: 250.763.1759

Christopher Browne

2005-06-14, 11:24 am

"Tang, Jason" <jason.tang- UQ1CjVvcO09tmpO5GhRA
odBPR1lH4CV8@public.gmane.org> writes:
> Would I be assuming correct that a node can belong to more than one
> cluster. It would mean I just have to call slonik twice with commands
> describing a different clustername, but can reuse the node ids?

That seems right, yes.

The cluster-specific stuff resides in the _clustername namespace, so
there's no reason why you couldn't have a particular database
participating in multiple clusters.

I don't think anyone has tried it, so you may uncover new ground, and
new problems.

What I would be concerned about is that this provides more
opportunities for locks/blocks/deadlocks as you'll have more slon
processes connecting in concurrently.

I guess I haven't thought of a case yet where it would "seem wise" to
do this.
--
"cbbrowne","@","ca.afilias.info"
<http://dev6.int.libertyrms.com/>
Christopher Browne
(416) 673-4124 (land)

Tang, Jason

2005-06-14, 11:24 am

> -----Original Message-----
> "Tang, Jason" <jason.tang- UQ1CjVvcO09tmpO5GhRA
odBPR1lH4CV8@public.gmane.org> writes:
commands[color=darkr
ed]
>=20
> That seems right, yes.

Excellent! :)

> I don't think anyone has tried it, so you may uncover new ground, and
> new problems.

I was afraid you'd say that.

> What I would be concerned about is that this provides more
> opportunities for locks/blocks/deadlocks as you'll have more slon
> processes connecting in concurrently.
>=20
> I guess I haven't thought of a case yet where it would "seem wise" to
> do this.

Hmm.. maybe I should explain why I think this is the way to do it. I'm
obviously open to comments and suggestions. I have two databases both
completely unrelated, reading the docs it kind of hinted that if they're
unrelated then they should be different clusters. Or could I put both
dbs under one cluster?

Jase

Christopher Browne

2005-06-14, 11:24 am

"Tang, Jason" <jason.tang- UQ1CjVvcO09tmpO5GhRA
odBPR1lH4CV8@public.gmane.org> writes:
> commands
>
> Excellent! :)
>
>
> I was afraid you'd say that.

It doesn't mean it won't work :-). Just that you might find new edge
cases :-(.

>
> Hmm.. maybe I should explain why I think this is the way to do
> it. I'm obviously open to comments and suggestions. I have two
> databases both completely unrelated, reading the docs it kind of
> hinted that if they're unrelated then they should be different
> clusters. Or could I put both dbs under one cluster?

Hmm... It seems as though may be running into a conflict of meanings
of the term "cluster."

- With PostgreSQL, a "cluster" is a set of databases associated with a
single backend (e.g. - associated with one postmaster process, all
accessible at one port).

- With Slony-I, a "cluster" is a set of databases participating in
replication. They will normally each be associated with a different
postmaster process, and are probably running on different hosts.

The two usages of "cluster" are distinct, but I suspect you may be
mistaking one for the other...

Could you elaborate on what databases you are thinking about
replicating? That may help us get around terminology troubles :-).
--
"cbbrowne","@","ca.afilias.info"
<http://dev6.int.libertyrms.com/>
Christopher Browne
(416) 673-4124 (land)

Alan Hodgson

2005-06-14, 1:24 pm

On Tue, Jun 14, 2005 at 12:04:13PM -0400, Christopher Browne wrote:
> The cluster-specific stuff resides in the _clustername namespace, so
> there's no reason why you couldn't have a particular database
> participating in multiple clusters.
>
> I don't think anyone has tried it, so you may uncover new ground, and
> new problems.
>

I have 3 different clusters on one database. It seems to work fine. The
various sets are distinct (by definition) so I haven't seen any locking or
deadlock problems related to running multiple clusters.

--
"We can no more blame our loss of freedom on Congressmen than we can
prostitution on pimps. Both simply provide broker services for their
customers." -- Dr. Walter Williams

Tang, Jason

2005-06-14, 1:24 pm

> -----Original Message-----
and[color=darkred]
>=20
> It doesn't mean it won't work :-). Just that you might find new edge
> cases :-(

I just want it to work :)

>=20
> Hmm... It seems as though may be running into a conflict of meanings
> of the term "cluster."
>=20
> - With PostgreSQL, a "cluster" is a set of databases associated with a
> single backend (e.g. - associated with one postmaster process, all
> accessible at one port).
>=20
> - With Slony-I, a "cluster" is a set of databases participating in
> replication. They will normally each be associated with a different
> postmaster process, and are probably running on different hosts.
>=20
> The two usages of "cluster" are distinct, but I suspect you may be
> mistaking one for the other...
>=20
> Could you elaborate on what databases you are thinking about
> replicating? That may help us get around terminology troubles :-).

It seems I'm confusing myself and everyone else around me at the moment
:)

Ok lets have a go at explaining.

I have to two databases 'provision' and 'order' there are no
relationships/keys between the two. From your definition of cluster, I
_think_ I mean that there is cluster called 'foo' and there are two
slony 'sets' - id =3D 1 (provision), and id =3D 2 (order).

Is the right approach?

Cheers
Jase

Alan Hodgson

2005-06-14, 1:24 pm

On Tue, Jun 14, 2005 at 06:13:34PM +0100, Tang, Jason wrote:
> I have to two databases 'provision' and 'order' there are no
> relationships/keys between the two. From your definition of cluster, I
> _think_ I mean that there is cluster called 'foo' and there are two
> slony 'sets' - id = 1 (provision), and id = 2 (order).

If the tables are in different PostgreSQL databases, then you need
2 completely separate Slony clusters with separate slons. Each cluster will
have a single set, which could have a set ID 1 if that doesn't confuse things
too much.

--
"Government's view of the economy could be summed up in a few short phrases:
If it moves, tax it. If it keeps moving, regulate it. And if it stops moving,
subsidise it." - Ronald Reagan

Christopher Browne

2005-06-14, 1:24 pm

"Tang, Jason" <jason.tang- UQ1CjVvcO09tmpO5GhRA
odBPR1lH4CV8@public.gmane.org> writes:
> Ok lets have a go at explaining.
>
> I have to two databases 'provision' and 'order' there are no
> relationships/keys between the two. From your definition of cluster, I
> _think_ I mean that there is cluster called 'foo' and there are two
> slony 'sets' - id = 1 (provision), and id = 2 (order).
>
> Is the right approach?

So "provision" and "order" have nothing in common, including that they
reside in separate databases, right?

If they haven't got anything in common, I'd be somewhat inclined to
have them be two separate Slony-I clusters.

It would be _possible_ to have two sets in one cluster, and that
_could_ have the merit of cutting down slightly on the number of slon
processes. The latter would happen if you decided to have a database
"provision_order" which subscribed to both sets. The two
subscriptions could be managed by one slon ==> "one slon saved."

But I'd expect that from a security perspective it would be preferable
for the respective sets of data to never need to meet, that is, that
the "provision" cluster would only do "provision" stuff, and the
"order" cluster would only do "order" stuff.

I'm also reluctant to attach unrelated things together as if any node
is down, that holds back confirmations across the whole cluster.
Which prevents cleaning stuff up. If something goes "bump" with an
orders node, the tied-together-cluster leads to that affecting
provision nodes.

There isn't a self-evident right or wrong here, but my bias would be
to have two clusters...
--
"cbbrowne","@","ca.afilias.info"
<http://dev6.int.libertyrms.com/>
Christopher Browne
(416) 673-4124 (land)

Alan Hodgson

2005-06-14, 1:24 pm

On Tue, Jun 14, 2005 at 02:28:16PM -0400, Christopher Browne wrote:
> It would be _possible_ to have two sets in one cluster, and that
> _could_ have the merit of cutting down slightly on the number of slon
> processes. The latter would happen if you decided to have a database
> "provision_order" which subscribed to both sets. The two
> subscriptions could be managed by one slon ==> "one slon saved."

Ah right, make the 2 db's each members of the cluster ... forgot
about that.

--
When you understand why you dismiss all the other possible gods, you will
understand why I dismiss yours

Hannu Krosing

2005-06-15, 3:25 am

On T, 2005-06-14 at 12:04 -0400, Christopher Browne wrote:
> "Tang, Jason" <jason.tang- UQ1CjVvcO09tmpO5GhRA
odBPR1lH4CV8@public.gmane.org> writes:
>
> That seems right, yes.
>
> The cluster-specific stuff resides in the _clustername namespace, so
> there's no reason why you couldn't have a particular database
> participating in multiple clusters.
>
> I don't think anyone has tried it, so you may uncover new ground, and
> new problems.

I'm doing it and , yes, I do uncover new ground, and new problems. ;)

the main problem is (in slony 1.0.5):

1) xxtid datatype has problems when conversion operations are installed
in 2nd cluster schema, requires manual tweaking (I did actual adding of
2,d cluster via pg_dump/reaload, so it may be connected to that.

OTOH, having many sets replicating between dissimilar databases has its
own problems:

1) EXECUTE SCRIPT often fails on the nodes where some of the
tables/sequences/whatever is not present, requiring manual deleting of
the event form sl_log_1 after it has applied where it can be.

This is due to fact that EXECUTE SCRIPT is tied to a master node rather
than set of tables, and thus it tries to EXECUTE the SCRIPT on _all_
other nodes, even on the ones where it fails due to missing objects.

2) switching over master node for several sets has sync problems, which
also need manual tweaking of sl_log_1 and other sl_* tables to solve.
This is on a 4-node cluster with 10 sets, each with different set of
master and subscribers.

Sorry for not reporting it, but this has been in high-load/(and high-
stress :) ) situations, and not easy to replicate in a way of providing
a failing test case.

So my recommendation is rather to use one slon daemon
_per_replication_set
_, if you see a need to swith over some node which
is master for several dissimilar sets and possibly also a slave for some
other set. I'm not yet doing it myself, so it may have some other set of
problems.

> What I would be concerned about is that this provides more
> opportunities for locks/blocks/deadlocks as you'll have more slon
> processes connecting in concurrently.

This should not be a problem, as each daemon must have its own schema
and tables.

This might be a problem for a db schema extraction scripts.

--
Hannu Krosing <hannu-7C/ iILuz2RdeoWH0uzbU5w@
public.gmane.org>

Sponsored Links