Good memory you have and you exactly right.
Yes... the replication is using posgres's normal transactions ie 2 phase
commits.... and it works like a dream.
When moving data during replication, the locks are happening at record
level, and its intrinsic to the postgres transaction machinery.
ie postgres is deciding how 'fine grained' the locks should be, and doing
all that other amazing MVCC stuff.
The part I'm toying with and struggling with is the start and stop, or the
admin side of the replication. As it stands now, one has to start with
identicle databases, then setup replication, and then the users come on. But
now say I want to make a structural change... as it stands I have to claim
the dB's back, fix them all, make sure they identicle, re-set up the
replication, and then the users can come back on. Its that.... the dB's must
be identicle on setting up replication, that I'm trying to get around. I
think that when it comes to the structural side, I have to hold(LOCK) those
dB's, while the software removes the replication, changes the structures of
all the dB's, reinstalls the scripts and triggers... and I want to make that
invisible to a system thats already active. Ideally the user software just
delays for say 10 seconds, and in that time, 6 dB's have been restructured,
checked and the replication restarted.
In terms of the set up I want to get it to... make those 5 dB's the same as
this template and start or continue replicating.... becomes a mind twister.
Thats the idea anyway... current version is at http://coolese.100free.com/
it works great, but you'll see it has a setup, breakdown problem on an
active system.
Thx 4 the help Johnny
From: "Markus Schiltknecht" <markus@bluegap.ch>
>> But I was thinking (climbing out of the wrong tree;)... I can just aquire
>> exclusive locks on the tables, and hey presto, users are on hold while
>> the software checks the dB's.
>
> I'm sure, that's possible. However, I remember you were talking about
> replication, thus I have to add a warning: please keep in mind that this
> does not scale. You're most probably better using two phase commit, aren't
> you?
>
> Regards
>
> Markus
>