Re: Maximum Availability Architecture(MAA) for PostgreSQL

In my testing, I had issues with pg-pool not reinitializing connections
properly after multiple failovers. In the end, I went with haproxy in TCP
mode. The two assets needed to make this work are:

* haproxy config:
https://github.com/compose/governor/blob/master/haproxy.cfg
* http status server:
https://github.com/compose/governor/blob/master/haproxy_status.sh

For maximum availability, run haproxy at the application server level.
Then, you don't have to worry about a proxy host failing.

As Jan said, you need 3 total Postgres (1 master - 2 standby) for sync
replication. If you have 2 total (1 master - 1 standby), and you take 1
down for maintenance, then you are left with 0 standby hosts. That will
make your cluster go read-only.

On Wed, Apr 8, 2015 at 8:35 AM, Wei Shan <weishan(dot)ang(at)gmail(dot)com> wrote:

> Hi Jay,
>
> I totally agree with you that having a pair hot standbys will be good.
> What do you mean by "using WAL shipping in addition to WAL
> receivers"?According to the documentation
> <http://www.postgresql.org/docs/current/static/warm-standby.html>, if I
> configure replication slot, "*the master does not remove WAL segments
> until they have been received by all standbys*". In sync replication, if
> the standby is down, the transaction will not be able to commit. However,
> if we have a pair, as long as 1 of the hot standby is up, the transaction
> will still go through.
>
> Hi Jan,
>
> Technically, 2 instance of pg-pool will suffice. However, if we have to
> bring 1 instance down for maintenance, there's no standby if the master
> crashes.
>
> Anyway, I do realise a tiny flaw in my design, pg-pool is not a
> active/active design. It means there's a wasted resource. At least my pair
> of hot standbys could serve read queries..
>
>
>
> On 8 April 2015 at 20:44, Jan Lentfer <Jan(dot)Lentfer(at)web(dot)de> wrote:
>
>> Am 2015-04-08 10:22, schrieb Wei Shan:
>>
>>> In the PostgreSQL world, what do you guys think would be the
>>> equivalent?
>>>
>>> Attached is a diagram I have thought of. Clusters of pg-pool2 used to
>>> load balance the connection in and for connection failover when a DB
>>> crashes. Between master and slave, sync replication is being used for
>>> zero-data-loss.
>>>
>>
>> Why do you need 3 pg-pool instances? 2 instances uses watchdog should be
>> sufficient?
>> And if you plan to use sync replication you need to plan for 3 PostgreSQL
>> Servers (a 3rd one that the sync replication can fail-over to).
>>
>> Regards,
>>
>>
>> Jan
>>
>>
>> --
>> Sent via pgsql-admin mailing list (pgsql-admin(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-admin
>>
>
>
>
> --
> Regards,
> Ang Wei Shan
>