X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 978F5D1E0CD for ; Thu, 15 Apr 2004 09:51:29 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 12049-05 for ; Thu, 15 Apr 2004 09:51:31 -0300 (ADT) Received: from anchor-post-30.mail.demon.net (anchor-post-30.mail.demon.net [194.217.242.88]) by svr1.postgresql.org (Postfix) with ESMTP id 78654D1DB99 for ; Thu, 15 Apr 2004 09:51:26 -0300 (ADT) Received: from no-dns-yet.demon.co.uk ([80.176.179.58] helo=mailserver.computerpark.local) by anchor-post-30.mail.demon.net with esmtp (Exim 3.35 #1) id 1BE6L3-00003M-0U; Thu, 15 Apr 2004 13:51:29 +0100 Received: (from root@localhost) by mailserver.computerpark.local (8.12.6/8.12.6/SuSE Linux 0.6) id i3FClfuG013036; Thu, 15 Apr 2004 13:47:41 +0100 Received: from mailserver.computerpark.local (localhost [127.0.0.1]) by mailserver.computerpark.local (8.12.6/8.12.6/SuSE Linux 0.6) with SMTP id i3FClcFb012963; Thu, 15 Apr 2004 13:47:38 +0100 Received: from 10.0.0.170 (SquirrelMail authenticated user pbreen) by mailserver.computerpark.local with HTTP; Thu, 15 Apr 2004 13:47:38 +0100 (BST) Message-ID: <25298.10.0.0.170.1082033258.squirrel@mailserver.computerpark.local> 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> Date: Thu, 15 Apr 2004 13:47:38 +0100 (BST) Subject: Re: Real time replication of PG DBs accross two servers - From: "Paul Breen" To: "Bruno Wolff III" Cc: "Paul Breen" , "Bradley Kieser" , pgsql-admin@postgresql.org Reply-To: pbreen@computerpark.co.uk User-Agent: SquirrelMail/1.4.1 MIME-Version: 1.0 Content-Type: text/plain;charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Priority: 3 Importance: Normal X-scanner: scanned by Inflex 1.0.12.3 - (http://pldaniels.com/inflex/) X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200404/228 X-Sequence-Number: 13201 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 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 =========================================================