Re: Real time replication of PG DBs accross two servers - - Mailing list pgsql-admin

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

=========================================================