Re: PostgreSQL HA config recommendations

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


In my experience streaming replication fits most use cases due to
inherent its simplicity and robustness, but you might need to adjust
your software design to get the best out of it.


More specifically, latency issues can be heavily mitigated by having
application software commit no more than one transaction per user
operation, provided 1 x "master<->sync_slave round trip time" is
acceptable delay when they submit forms or the like.

It can get much worse if the application server is on a different
geographical node than the DB master. In such case it is realistically
beneficial to batch multiple write operations in a single STATEMENT instead.
If the replication synchronous slave is on yet another node, the best
case (single statement) scenario would be 2 x round trip time. This
configuration is more common than you might think as some setups feature
remote app servers reading off synchronous slaves at their own physical
location but committing against a master that is somewhere else.


Cheers






On 30/04/15 11:06, Jim Nasby wrote:
> 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.