Sender: root@modem.mfa.at
Message-ID: <38599115.E0E32503@magnet.at>
Date: Thu, 16 Dec 1999 18:25:41 -0700
From: Gerhard Hintermayer <g_hintermayer@magnet.at>
MIME-Version: 1.0
To: Paul Breen <paulb@computerpark.co.uk>
CC: pgsql-admin@postgresql.org, pgsql-interfaces@postgresql.org
Subject: Re: [INTERFACES] Database replication under Postgres
References: 
 <Pine.LNX.3.96.991216162724.12454A-100000@cpark37.computerpark.co.uk>
Content-Type: multipart/mixed; boundary="------------EB54A23BCCA1B02D322E7584"

This is a multi-part message in MIME format.
--------------EB54A23BCCA1B02D322E7584
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Paul Breen wrote:

> Hello all, I wonder if anyone could help me.
>
> I'm currently working on a project where it is essential that we have some
> kind of replication of our production database.  I've found a number of
> 'near' solutions (e.g., doing a piped 'COPY IN/OUT' using psql - one
> local and one remote - from a simple shell script) but this doesn't
> 'synchronise' the data, it just piles ALL the data from one database into
> the other.
>
> I've also looked into setting up rules on a table to NOTIFY me of an
> insert|update|delete and then have a program that queries the difference
> between the two databases and synchronises them accordingly. This is ok to
> a point but is not particularly elegant or efficient.  Basically I was
> wondering if anyone knows of a better way of doing it or if someone has
> written a tool for automating replication with Postgres.
>
> If we were replicating data from one table to another in the SAME DATABASE
> it would be easy to set up a rule such as:
>
> CREATE RULE itbl1 AS ON INSERT TO tbl1 DO (INSERT INTO tbl2
> values(new.i));
>
> Ideally, I suppose what I am after - and I dare say I'm hoping to much -
> is a simple solution that says the same as the above rule but where
> "tbl2" is a table in a remote host's database.
>
> I would be really grateful to hear from other users' experiences with any
> form of replication/synchronisation under Postgres.
>
> Thanks in advance.
>
> Paul M. Breen
> Computer Park Ltd
> Email: pbreen@computerpark.co.uk
>
> ************

What I did to keep 2 databases on two different hosts consistent is the
following:

everytime an insert/update is done ,I call pg_exec for both connections to
two different host.
Don't know if that meet's your needs, but syncronising at the point, where
modifications take place, is the easiest approach.


--------------EB54A23BCCA1B02D322E7584
Content-Type: text/x-vcard; charset=us-ascii;
 name="g_hintermayer.vcf"
Content-Transfer-Encoding: 7bit
Content-Description: Card for Gerhard Hintermayer
Content-Disposition: attachment;
 filename="g_hintermayer.vcf"

begin:vcard 
n:Hintermayer;Gerhard
tel;cell:+43 664 1537810
tel;fax:+43 2162 68924
tel;home:+43 2162 68924
tel;work:+43 2162 601 4357
x-mozilla-html:FALSE
url:http://members.magnet.at/g_hintermayer
org:free lance
adr:;;Mühlgasse 6/2/15;BRUCK/LEITHA;;A-2460;Austria
version:2.1
email;internet:g_hintermayer@magnet.at
x-mozilla-cpt:;32032
fn:Gerhard Hintermayer
end:vcard

--------------EB54A23BCCA1B02D322E7584--