Re: database replication

On Sep 2, 2006, at 7:32 AM, Marc Evans wrote:

> Hello -
>
> I am curious as to people's opinion about best practices in
> deployment of pgsql in a high-availablity (HA) situation. By HA I
> mean that data loss must be as close to zero as possible, and that
> web servers using the DB need to be deployed in multiple geographic
> locations world-wide, to provide for network availability.

My best practice would be "Don't". If you're supporting arbitrary SQL
operations you can't get to zero data loss with asynchronous
replication, and you can't get good performance with synchronous
replication.

Replication at the application level (where that application code may
be embedded in the db, but may not) where you have an understanding
of what data changes your system supports is much saner than trying
to rely on a replication engine that has to support asynchronous
multi-master replication of arbitrary SQL (I'd probably argue that no
such thing exists, but that's another story).

> In my mind, this suggests a need for a multi-master configuration,
> such as that which can be achieved with pgcluster. I see redundency
> both within a POP, as well as cross-POP. I am however concerned
> about the latency implied by POP locations (let's assume a POP in
> the EU, US and JP).

Multi-master is even worse. I've worked with big, international
master-master replicated database (under Oracle rather than PG) and
even though they were fairly low update traffic I hope to never, ever
do that again.

> I am aware of bizgress, though am concerned that it doesn't really
> address the wide-area replication issue. It does address the HA
> aspect within a single POP quite nicely though.
>
> How do you deal with this, commerical or OSS?

At the system architecture, or application levels. Not by layering a
non-replication aware application on top of a general purpose SQL
replication engine.

You can do it by using an off-the-shelf replication engine, of
course. There are a number of products out there that prove that. But
operationally you'll regret it.

Cheers,
Steve