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 008FED1E298 for ; Wed, 14 Apr 2004 11:54:12 -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 60779-02 for ; Wed, 14 Apr 2004 11:54:09 -0300 (ADT) Received: from wolff.to (wolff.to [66.93.249.74]) by svr1.postgresql.org (Postfix) with SMTP id C3327D1DF8E for ; Wed, 14 Apr 2004 11:54:04 -0300 (ADT) Received: (qmail 31133 invoked by uid 500); 14 Apr 2004 14:57:53 -0000 Date: Wed, 14 Apr 2004 09:57:53 -0500 From: Bruno Wolff III To: Paul Breen Cc: Bradley Kieser , pgsql-admin@postgresql.org Subject: Re: Real time replication of PG DBs accross two servers - Message-ID: <20040414145753.GA30859@wolff.to> Mail-Followup-To: Paul Breen , Bradley Kieser , pgsql-admin@postgresql.org References: <407B85CF.4010404@kieser.net> <23864.10.0.0.170.1081950297.squirrel@mailserver.computerpark.local> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <23864.10.0.0.170.1081950297.squirrel@mailserver.computerpark.local> User-Agent: Mutt/1.5.6i X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200404/219 X-Sequence-Number: 13192 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.