pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Paul Breen <pbreen@computerpark.co.uk>
To: Bruno Wolff III <bruno@wolff.to>
Cc: Paul Breen <pbreen@computerpark.co.uk>
Cc: Bradley Kieser <brad@kieser.net>
Cc: pgsql-admin@postgresql.org
Subject: Re: Real time replication of PG DBs accross two servers -
Date: Thu, 15 Apr 2004 13:47:38 +0100 (BST)
Message-ID: <25298.10.0.0.170.1082033258.squirrel@mailserver.computerpark.local> (raw)
In-Reply-To: <20040414145753.GA30859@wolff.to>
References: <407B85CF.4010404@kieser.net>
	<23864.10.0.0.170.1081950297.squirrel@mailserver.computerpark.local>
	<20040414145753.GA30859@wolff.to>

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@computerpark.co.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@computerpark.co.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@computerpark.co.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 

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



view thread (8+ messages)  latest in thread

Message-ID: <25298.10.0.0.170.1082033258.squirrel@mailserver.computerpark.local>
Permalink:  ../25298.10.0.0.170.1082033258.squirrel@mailserver.computerpark.local/
Also on:    postgresql.org/message-id/25298.10.0.0.170.1082033258.squirrel@mailserver.computerpark.local

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: pbreen@computerpark.co.uk, bruno@wolff.to, brad@kieser.net
  Subject: Re: Real time replication of PG DBs accross two servers -
  In-Reply-To: <25298.10.0.0.170.1082033258.squirrel@mailserver.computerpark.local>
  References: <407B85CF.4010404@kieser.net> <23864.10.0.0.170.1081950297.squirrel@mailserver.computerpark.local> <20040414145753.GA30859@wolff.to> <25298.10.0.0.170.1082033258.squirrel@mailserver.computerpark.local>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox