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