Real time replication of PG DBs accross two servers - any experiences?

[Bradley Kieser]

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

[Peter Eisentraut]

Post by Bradley Kieser
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.

To ensure high availability, using DRBD to replicate the storage or
using a shared storage (e.g., EMC^2) does the job. That saves you the
trouble of having to set up and manage a database replication solution
at all.


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Christopher Browne]

Post by Peter Eisentraut

Post by Bradley Kieser
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.

To ensure high availability, using DRBD to replicate the storage or
using a shared storage (e.g., EMC^2) does the job. That saves you
the trouble of having to set up and manage a database replication
solution at all.

Regrettably, "disk replication" schemes aren't generally able to cope
with keeping the database up and alive while replication is taking
place.

You need to shut down the database that is attached to a
"target/slave" system every time you "sync" the target/slave against
the source/master.

If the _sole_ goal is failover, then that will be defined to be
"fine."

But when the people paying for the duplicate set of hardware realize
that it's sitting there "otherwise useless" at any time that there
hasn't been a failure, they'll presumably agitate for some 'better'
sort of replication...

[Peter Eisentraut]

Post by Christopher Browne

Post by Peter Eisentraut
To ensure high availability, using DRBD to replicate the storage or
using a shared storage (e.g., EMC^2) does the job. That saves you
the trouble of having to set up and manage a database replication
solution at all.

Regrettably, "disk replication" schemes aren't generally able to cope
with keeping the database up and alive while replication is taking
place.

The ones mentioned above certainly keep the database running at all
times until a failover.

Post by Christopher Browne
You need to shut down the database that is attached to a
"target/slave" system every time you "sync" the target/slave against
the source/master.

The slave database doesn't run at all while the master is alive.

Post by Christopher Browne
But when the people paying for the duplicate set of hardware realize
that it's sitting there "otherwise useless" at any time that there
hasn't been a failure, they'll presumably agitate for some 'better'
sort of replication...

If you can set up a true database-level replication system that does
something useful with the slave server while the master is running
(i.e., load balancing) for the same price (hardware + labor +
post-installation service) that you (or I or someone) can set up a disk
replication system for then it would be interesting. So far I haven't
seen it happening. In my experience, load balancing is not needed in
most cases, so no one is going to bother paying for the extra
functionality that they don't need.

The other advantage of disk replication is that you can secure almost
any service in that same way (web, dns, ldap, etc.), so the management
and setup effort spent on any particular service diminishes.


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Paul Breen]

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

Post by Bradley Kieser
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: [phone]
Email: ***@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: ***@computerpark.co.uk

Tel: [phone]
Fax: [phone]

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

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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Bruno Wolff III]

On Wed, Apr 14, 2004 at 14:44:57 +0100,

Post by Paul Breen
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.

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Paul Breen]

Hello Bruno,

If we successfully commit on the master but fail on the slave, we simply
log a "Major Error" & then the dbs would have to be manually made
consistent. However, in practice this has never happened (the 1st
application has been running 24/7 for 5 years).

As you say, you have to be careful with sequences. How we handle them is:

(master) -- select nextval('seqname'); insert ... etc.
(slave) -- select setval('seqname', 'seqvalue'); ...

i.e., we only ever select the next seq. no. from the master & always sync.
to the slave.

Regards - Paul

Post by Bruno Wolff III
On Wed, Apr 14, 2004 at 14:44:57 +0100,

Post by Paul Breen
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.

--
Paul M. Breen, Software Engineer - Computer Park Ltd.

Tel: [phone]
Email: ***@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: ***@computerpark.co.uk

Tel: [phone]
Fax: [phone]

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

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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Gaetano Mendola]

Post by Bradley Kieser
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.

We are using a SAN server, and 2 nodes running a Red Hat HA.

Regards
Gaetano Mendola