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.graham@infonetsystems.com.au>
Attachment:
signature.asc
Description: This is a digitally signed message part
