Anyone using pgreplicator?

[Alan Graham]

Apologies if this is off topic, but I've tried the pgreplicator forums,
and they appear to be dormant.

I'm trying to get a multi master replication app running, I thought I
was doing well, I've got past all of the problems mentioned on the
pgreplicator forums and (checking the archives) on this list, ie, tcl
installed ok, script changes for postgresql 7.3.4 in, communications
using scp as user replicator working... Everything in fact works.
Except that it doesn't actually replicate. When I synchronise. I can
see the traffic going between the sites. I can see the record being
replicated appearing on the flow table, I can see it on the history
file on both the source and the destination, so it's reaching the
destination. There is no sign of any error in any of the pgreplicator
logs or the system logs. I've even started postgresql in debug log mode
and tailed the log while started the replicator on the remote site. I
see the communications come in, and see the commands being entered. But
the data isn't being changed on the destination.

Any pgreplicator users out there seen this sort of thing before?

I thought it might be a permissions issue, but all users have full
access. The replicator user can make the changes by submitting an SQL
update. pgreplicator has a 'rejected sql' log file, but that's empty.
I'm at my wits end...

Regards

--
Alan Graham <al*********@infonetsystems.com.au>
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Alan Graham]

Replying to my own post, tch, tch... :-)

Thanks to those of you who have contacted me off list. It seems that
there's a fair bit of interest in replication in general at the moment.

I fixed my problem with pgReplicator, so the test system is merrily
replicating between Perth and Brisbane as I type. Bandwidth usage is
low at the moment. The problem was embarrassingly simple once I checked
the actual SQL being received on the remote server. (Thanks Stef of .za
for the suggestion).

When pgReplicator creates its replication changes to a data base it adds
its own unique ID. I did this independently when I initially set this
up. This meant that the id for records that I considered to be
identical were actually different according to pgreplicator and
therefore updates couldn't find the corresponding record to update in
the remote system. As I said, embarrassingly simple!

The trick when setting up a replicated multi master db is to first of
all create the combined db, create the pgr replication schema on this
combined db, and then distribute this db around to all sites. Only then
do you make the site specific changes (eg, to pgr_lsite, and any locally
independent tables). Note that this is the opposite approach to that on
the website. Alternatively, (I haven't tried this) one could distribute
a schema only db, put the site specific data into it and let replication
handle the filling of the common data, by dumping the data as user
replicator, then loading it again as a real user.

Hope this helps someone else avoid my mistake.

Alan Graham

On Thu, 2004-04-15 at 22:11, Alan Graham wrote:

Apologies if this is off topic, but I've tried the pgreplicator forums,
and they appear to be dormant.

I'm trying to get a multi master replication app running, I thought I
was doing well, I've got past all of the problems mentioned on the
pgreplicator forums and (checking the archives) on this list, ie, tcl
installed ok, script changes for postgresql 7.3.4 in, communications
using scp as user replicator working... Everything in fact works.
Except that it doesn't actually replicate. When I synchronise. I can
see the traffic going between the sites. I can see the record being
replicated appearing on the flow table, I can see it on the history
file on both the source and the destination, so it's reaching the
destination. There is no sign of any error in any of the pgreplicator
logs or the system logs. I've even started postgresql in debug log mode
and tailed the log while started the replicator on the remote site. I
see the communications come in, and see the commands being entered. But
the data isn't being changed on the destination.

Any pgreplicator users out there seen this sort of thing before?

I thought it might be a permissions issue, but all users have full
access. The replicator user can make the changes by submitting an SQL
update. pgreplicator has a 'rejected sql' log file, but that's empty.
I'm at my wits end...

Regards

--
Alan Graham <al*********@infonetsystems.com.au>

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)

iD8DBQBAhUYNoD2Tjq9atDkRAsljAJ0RnmYgCNFy/3LbkmRSmxKPi5UltACeKvD6
bSXOqrCSRkfo1o3iV1WQqvQ=
=op66
-----END PGP SIGNATURE-----

[Alan Graham]

Replying to my own post, tch, tch... :-)

Thanks to those of you who have contacted me off list. It seems that
there's a fair bit of interest in replication in general at the moment.

I fixed my problem with pgReplicator, so the test system is merrily
replicating between Perth and Brisbane as I type. Bandwidth usage is
low at the moment. The problem was embarrassingly simple once I checked
the actual SQL being received on the remote server. (Thanks Stef of .za
for the suggestion).

When pgReplicator creates its replication changes to a data base it adds
its own unique ID. I did this independently when I initially set this
up. This meant that the id for records that I considered to be
identical were actually different according to pgreplicator and
therefore updates couldn't find the corresponding record to update in
the remote system. As I said, embarrassingly simple!

The trick when setting up a replicated multi master db is to first of
all create the combined db, create the pgr replication schema on this
combined db, and then distribute this db around to all sites. Only then
do you make the site specific changes (eg, to pgr_lsite, and any locally
independent tables). Note that this is the opposite approach to that on
the website. Alternatively, (I haven't tried this) one could distribute
a schema only db, put the site specific data into it and let replication
handle the filling of the common data, by dumping the data as user
replicator, then loading it again as a real user.

Hope this helps someone else avoid my mistake.

Alan Graham

On Thu, 2004-04-15 at 22:11, Alan Graham wrote:

Apologies if this is off topic, but I've tried the pgreplicator forums,
and they appear to be dormant.

I'm trying to get a multi master replication app running, I thought I
was doing well, I've got past all of the problems mentioned on the
pgreplicator forums and (checking the archives) on this list, ie, tcl
installed ok, script changes for postgresql 7.3.4 in, communications
using scp as user replicator working... Everything in fact works.
Except that it doesn't actually replicate. When I synchronise. I can
see the traffic going between the sites. I can see the record being
replicated appearing on the flow table, I can see it on the history
file on both the source and the destination, so it's reaching the
destination. There is no sign of any error in any of the pgreplicator
logs or the system logs. I've even started postgresql in debug log mode
and tailed the log while started the replicator on the remote site. I
see the communications come in, and see the commands being entered. But
the data isn't being changed on the destination.

Any pgreplicator users out there seen this sort of thing before?

I thought it might be a permissions issue, but all users have full
access. The replicator user can make the changes by submitting an SQL
update. pgreplicator has a 'rejected sql' log file, but that's empty.
I'm at my wits end...

Regards

--
Alan Graham <al*********@infonetsystems.com.au>

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)

iD8DBQBAhUYNoD2Tjq9atDkRAsljAJ0RnmYgCNFy/3LbkmRSmxKPi5UltACeKvD6
bSXOqrCSRkfo1o3iV1WQqvQ=
=op66
-----END PGP SIGNATURE-----

[Richard Welty]

On Tue, 20 Apr 2004 23:47:26 +0800 Alan Graham <al*********@infonetsystems.com.au> wrote:

I fixed my problem with pgReplicator, so the test system is merrily
replicating between Perth and Brisbane as I type. Bandwidth usage is
low at the moment. The problem was embarrassingly simple once I checked
the actual SQL being received on the remote server. (Thanks Stef of .za
for the suggestion).

any chance of your publishing notes on how you got pgreplicator going?
i've taken two shots at it, and gotten stuck on getting tcl-dp working properly
both times. from a functionality point of view, it's closer to what i want than
any of the others, but it's been frustrating as hell to try and get going.

richard
--
Richard Welty rw****@averillpark.net
Averill Park Networking 518-573-7592
Java, PHP, PostgreSQL, Unix, Linux, IP Network Engineering, Security
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Richard Welty]

On Tue, 20 Apr 2004 23:47:26 +0800 Alan Graham <al*********@infonetsystems.com.au> wrote:

I fixed my problem with pgReplicator, so the test system is merrily
replicating between Perth and Brisbane as I type. Bandwidth usage is
low at the moment. The problem was embarrassingly simple once I checked
the actual SQL being received on the remote server. (Thanks Stef of .za
for the suggestion).

any chance of your publishing notes on how you got pgreplicator going?
i've taken two shots at it, and gotten stuck on getting tcl-dp working properly
both times. from a functionality point of view, it's closer to what i want than
any of the others, but it's been frustrating as hell to try and get going.

richard
--
Richard Welty rw****@averillpark.net
Averill Park Networking 518-573-7592
Java, PHP, PostgreSQL, Unix, Linux, IP Network Engineering, Security
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)