Re: Anyone using pgreplicator?

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 <alan(dot)graham(at)infonetsystems(dot)com(dot)au>