Re: PostgreSQL HA config recommendations

On 4/29/15 1:13 PM, Alex Gregory wrote:
> I was thinking that I could use Slony but then I read that it does not like WAN replication. I have also read about streaming replication native to Postgres but was not sure how that would work over the WAN. Bucardo seems better for Data Warehousing or multimaster situations which this is not. That leaves pgpool ii which seems like it would add an extra layer of complexity.

WAN delays can cause problems for any replication system; you just have
to be aware of that and not push things too hard (or try and violate the
laws of physics). For example, streaming replication set to be
synchronous crossing the planet is something you'd probably be rather
unhappy with. :)

I haven't played with Slony in forever, but when I did it loved to lock
things. That would not play well with high latency.

I have run londiste between sites within the same city, and that worked
well.

Bucardo and pg_pool are both based on the idea of replaying SQL
statements instead of replicating actual data. They have their uses, but
I personally distrust that idea, especially for DR.

> When it comes down to to there are so many choices I am not sure if I need one or a combination of two. Any help you could provide could be greatly appreciated.

If you want to replicate within a data center then streaming replication
is pretty nice, and as a bonus you might be able to do synchronous as
well. The downside to streaming rep is that it's binary, so if you ever
suffer data corruption you're practically guaranteed that corruption
will end up on the replica. Logical replication like londiste or Slony
are much more robust against that. You also can't use temporary tables
with streaming rep, and you have to replicate the details of ALL
activity, including maintenance like VACUUM. In some environments that
might be slower than logical replication.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com