Re: PostgreSQL HA config recommendations

The streaming replication built into PostgreSQL would work fine for your
use case, assuming that you are OK with having only one primary supporting
writes and any slaves being read only as it currently (9.0-9.4) only
supports a single master. This will put minimal load on your primary server
and in most cases will get you what you need. An excellent benefit of using
the built in streaming replication in PostgreSQL 9.4 or newer for WAN
replication is that with Replication Slots the master will keep track of
when the slave gets disconnected or falls behind and will retain WAL logs
as necessary. It puts minimal load on the master as the WAL logs are
written regardless and adding additional details to them are cheap. Slony
and Bucardo use triggers which put transactional load on the master, and
aren't really feasible over a distant WAN.

A common configuration is to have master-slave replication set up via
streaming replication and using pgpool-II to load balance. pgpool-II can be
configured to send all the writes to the master and distribute selects to
both. However, this will not get you all the desired HA you want because
pgpool-II does not have any logic to promote the slave to become the new
master if the master goes down. It is very easy to promote a slave to be a
master (you simply create a file that triggers auto-promote, then
reconfigure pgpool or do a DNS switch to point the application there) but
to have failover completely automated is much more complicated and
pgpool-II will not get you there.

The built in streaming replication can only replicate your entire
PostgreSQL cluster, so if you need finer grain control over what to
replicate (for example only a particular database of the cluster) you will
need to look to one of the other tools, such as Slony.

I would also recommend you take a look at the BDR Project from 2ndQuadrant.
The docs are located at http://bdr-project.org/docs/stable/index.html. Like
Bucardo it provides master-master replication capabilities with conflict
resolution which may allow you to avoid having logic differentiating write
transactions and directing them to a particular PostgreSQL Cluster, and
will avoid you having to have logic to promote slaves in the case of
failure of the master. Unlike Bucardo it uses streaming replication rather
than triggers so load on the master is minimal. The primary components of
the BDR project are being incorporated into core PostgreSQL and will very
likely be part of the standard streaming replication in PostgreSQL 9.5 and
above.

*Will J. Dunn*
willjdunn.com

On Wed, Apr 29, 2015 at 2:57 PM, Joshua D. Drake <jd(at)commandprompt(dot)com>
wrote:

>
> On 04/29/2015 10:53 AM, Alex Gregory wrote:
>
>> Hello-
>>
>> I have been doing lots of reading and I really want to make sure that I
>> get this HA architecture I am working on correct. I figured the best way
>> would be to reach out to the community for advice.
>>
>> I am installing Cisco Jabber and want to use Postgres for the back end.
>> The Postgres servers will be running on CentOS.
>>
>> The requirement is to have two servers in HA using a database stored on
>> shared NetApp Filer storage in California. A third server will be in
>> Ireland for DR purposes. There only really needs to be one writeable
>> server in California if it keeps things simple. Automatic conflict
>> resolution should be easier this way causing lower overhead.
>>
>> 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.
>>
>> 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.
>>
>
> This is a can of worms topic but:
>
> You can use streaming replication (or log shipping) asynchronously which
> will allow you to use it over WAN just fine.
>
> Other than that, use the Linux HA suite. That is what it is there for.
>
> JD
>
>
> --
> Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564
> PostgreSQL Centered full stack support, consulting and development.
> Announcing "I'm offended" is basically telling the world you can't
> control your own emotions, so everyone else should do it for you.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>