Обсуждение: Real time replication of PG DBs accross two servers - any experiences?

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

Bradley Kieser wrote:
> 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.

A long time ago, in a galaxy far, far away, peter_e@gmx.net (Peter Eisentraut) wrote:
> Bradley Kieser wrote:
>> 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...
--
(format nil "~S@~S" "cbbrowne" "acm.org")
http://cbbrowne.com/info/lisp.html
"Computers double in speed every  18 months or so, so any "exponential
time" problem  can be solved in  linear time by  waiting the requisite
number of months  for the problem to become solvable  in one month and
then starting the computation."  -- pratt@Sunburn.Stanford.EDU

Christopher Browne wrote:
> > 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.

> 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.

> 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.

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

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

On Wed, Apr 14, 2004 at 14:44:57 +0100,
  Paul Breen <pbreen@computerpark.co.uk> wrote:
>
> 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.

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

> On Wed, Apr 14, 2004 at 14:44:57 +0100,
>   Paul Breen <pbreen@computerpark.co.uk> wrote:
>>
>> 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:   (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

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

Bradley Kieser wrote:
> 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