From: Paul Breen <pbreen@computerpark.co.uk>
To: Bradley Kieser <brad@kieser.net>
Cc: pgsql-admin@postgresql.org
Subject: Re: Real time replication of PG DBs accross two servers -
Date: Wed, 14 Apr 2004 14:44:57 +0100 (BST)
Message-ID: <23864.10.0.0.170.1081950297.squirrel@mailserver.computerpark.local> (raw)
In-Reply-To: <407B85CF.4010404@kieser.net>
References: <407B85CF.4010404@kieser.net>
Hello Brad,
We've now done a couple of projects where it was essential to have full,
real time replication (for traceability & legal reasons).
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_.
Of course, this approach is better suited to a new application rather than
trying to retro-fit an already existing application with replication
capabilities. That's not impossible though.
We spent a bit of time up-front, developing a libpq wrapper library. The
design goal of this library was to be as similar to the libpq API as
possible. In that way, the coding was familiar & for a trivial piece of
code you could do a global replace of libpq functions for the replicated
equivalents (e.g., s/PQexec/PQREPexec/g).
Regards - Paul
> Hi All,
>
> I desperately need to set up a real time replication of several
> databases (for failover) between two servers. Last time I looked at the
> PG replication it wasn't yet production level. I wonder if anyone on
> this list is doing this successfully and if you won't mind sharing your
> experience and giving me any tips that you may think would be handy from
> a real world perspective I would very much appreciate it.
>
> I am 100% Linux-based, in case that makes a difference! I have also
> considered using the CODA files system in case the replication isn't
> quite up to production levels still.
>
> Thanks,
>
> Brad
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>
--
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
=========================================================
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, brad@kieser.net
Subject: Re: Real time replication of PG DBs accross two servers -
In-Reply-To: <23864.10.0.0.170.1081950297.squirrel@mailserver.computerpark.local>
References: <407B85CF.4010404@kieser.net> <23864.10.0.0.170.1081950297.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