Re: PostgreSQL HA config recommendations

On Apr 29, 2015, at 1:01 PM, William Dunn <dunnwjr@gmail.com> wrote:

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@commandprompt.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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general