pg.ddx.io pgsql-interfaces@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Database replication under Postgres 3+ messages / 3 participants [nested] [flat]
* Database replication under Postgres @ 1999-12-16 14:36 Paul Breen <pbreen@computerpark.co.uk> 0 siblings, 0 replies; 3+ messages in thread From: Paul Breen @ 1999-12-16 14:36 UTC (permalink / raw) To: pgsql-admin@postgresql.org; +Cc: pgsql-interfaces 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Database replication under Postgres @ 1999-12-16 16:31 Paul Breen <paulb@computerpark.co.uk> 0 siblings, 1 reply; 3+ messages in thread From: Paul Breen @ 1999-12-16 16:31 UTC (permalink / raw) To: pgsql-admin@postgresql.org; +Cc: pgsql-interfaces 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [INTERFACES] Database replication under Postgres @ 1999-12-17 01:25 Gerhard Hintermayer <g_hintermayer@magnet.at> parent: Paul Breen <paulb@computerpark.co.uk> 0 siblings, 0 replies; 3+ messages in thread From: Gerhard Hintermayer @ 1999-12-17 01:25 UTC (permalink / raw) To: Paul Breen <paulb@computerpark.co.uk>; +Cc: pgsql-admin@postgresql.org; pgsql-interfaces 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. Attachments:^ permalink raw reply [nested|flat] 3+ messages in threadg_hintermayer.vcf (text/x-vcard, 369B) download
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
end of thread, other threads:[~1999-12-17 01:25 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 1999-12-16 14:36 Database replication under Postgres Paul Breen <pbreen@computerpark.co.uk> 1999-12-16 16:31 Database replication under Postgres Paul Breen <paulb@computerpark.co.uk> 1999-12-17 01:25 ` Gerhard Hintermayer <g_hintermayer@magnet.at>
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