Skip site navigation (1) Skip section navigation (2)

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

From: "Paul Breen" <pbreen(at)computerpark(dot)co(dot)uk>
To: "Bruno Wolff III" <bruno(at)wolff(dot)to>
Cc: "Paul Breen" <pbreen(at)computerpark(dot)co(dot)uk>,"Bradley Kieser" <brad(at)kieser(dot)net>, pgsql-admin(at)postgresql(dot)org
Subject: Re: Real time replication of PG DBs accross two servers -
Date: 2004-04-15 12:47:38
Message-ID: 25298.10.0.0.170.1082033258.squirrel@mailserver.computerpark.local (view raw or flat)
Thread:
Lists: pgsql-admin
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 

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

In response to

pgsql-admin by date

Next:From: Robert TreatDate: 2004-04-15 14:26:38
Subject: Re: Maximum Number Tables
Previous:From: Donald FraserDate: 2004-04-15 09:29:00
Subject: Re: Updgrade from 7.3 to 7.4

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group