Re: Real time replication of PG DBs accross two servers -

Hello Bruno,

If we successfully commit on the master but fail on the slave, we simply
log a "Major Error" & then the dbs would have to be manually made
consistent. However, in practice this has never happened (the 1st
application has been running 24/7 for 5 years).

As you say, you have to be careful with sequences. How we handle them is:

(master) -- select nextval('seqname'); insert ... etc.
(slave) -- select setval('seqname', 'seqvalue'); ...

i.e., we only ever select the next seq. no. from the master & always sync.
to the slave.

Regards - Paul

> On Wed, Apr 14, 2004 at 14:44:57 +0100,
> Paul Breen <pbreen(at)computerpark(dot)co(dot)uk> wrote:
>>
>> The way we approached it was to do the replication ourselves at the
>> application level. This isn't as hard as it sounds: you just open 2 db
>> connections (1 to the master, 1 to the slave), start transactions on
>> both
>> connections, then write your data. If an error occurs on either
>> connection, you rollback on both connections. This means you can still
>> have writes that fail (for whatever reason) but both dbs are
>> _consistent_.
>
> This is guarenteed to work in general. When there are concurrent
> transactions,
> one transaction may succeed on commit and the other fail. If you are using
> sequences you could also end up with different values in each database.
> That may or may not be a problem depending on how you use them.
>

--
Paul M. Breen, Software Engineer - Computer Park Ltd.

Tel: (01536) 417155
Email: pbreen(at)computerpark(dot)co(dot)uk
---------------------------------------------------------

This private and confidential e-mail has been sent to you
by Computer Park Ltd.

If you are not the intended recipient of this e-mail and
have received it in error, please notify us via the email
address or telephone number below, and then delete it from
your mailbox.

Email: mailbox(at)computerpark(dot)co(dot)uk

Tel: +44 (0) 1536 417155
Fax: +44 (0) 1536 417566

Head Office:
Computer Park Ltd, Broughton Grange, Headlands, Kettering
Northamptonshire NN15 6XA

Registered in England: 3022961.

Registered Office:
6 North Street, Oundle, Peterborough PE8 4AL

=========================================================