pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedMaximum Availability Architecture(MAA) for PostgreSQL
6+ messages / 5 participants
[nested] [flat]
* Maximum Availability Architecture(MAA) for PostgreSQL
@ 2015-04-08 08:22 Wei Shan <weishan.ang@gmail.com>
2015-04-08 12:44 ` Re: Maximum Availability Architecture(MAA) for PostgreSQL Jan Lentfer <Jan.Lentfer@web.de>
0 siblings, 1 reply; 6+ messages in thread
From: Wei Shan @ 2015-04-08 08:22 UTC (permalink / raw)
To: pgsql-admin
Hi everyone.
I posted this in the SG mailing list but there wasn't any response. Thus, I
decided to repost it here.
Anyone who is familiar with Oracle database would have heard of the Oracle
Maximum Availability Architecture where Oracle uses a range of products to
prevent the databases from suffering planned and unplanned outages.
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.
Let me know your thoughts!
Cheers!
--
Regards,
Ang Wei Shan
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Attachments:
Screen Shot 2015-04-06 at 0.01.12.png (image/png, 115.6K) download
view image
* Re: Maximum Availability Architecture(MAA) for PostgreSQL
2015-04-08 08:22 Maximum Availability Architecture(MAA) for PostgreSQL Wei Shan <weishan.ang@gmail.com>
@ 2015-04-08 12:44 ` Jan Lentfer <Jan.Lentfer@web.de>
2015-04-08 15:35 ` Re: Maximum Availability Architecture(MAA) for PostgreSQL Wei Shan <weishan.ang@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Jan Lentfer @ 2015-04-08 12:44 UTC (permalink / raw)
To: pgsql-admin
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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Maximum Availability Architecture(MAA) for PostgreSQL
2015-04-08 08:22 Maximum Availability Architecture(MAA) for PostgreSQL Wei Shan <weishan.ang@gmail.com>
2015-04-08 12:44 ` Re: Maximum Availability Architecture(MAA) for PostgreSQL Jan Lentfer <Jan.Lentfer@web.de>
@ 2015-04-08 15:35 ` Wei Shan <weishan.ang@gmail.com>
2015-04-08 15:59 ` Re: Maximum Availability Architecture(MAA) for PostgreSQL Chris Winslett <chris@compose.io>
2015-04-08 16:39 ` Re: Maximum Availability Architecture(MAA) for PostgreSQL John Scalia <jayknowsunix@gmail.com>
0 siblings, 2 replies; 6+ messages in thread
From: Wei Shan @ 2015-04-08 15:35 UTC (permalink / raw)
To: Jan Lentfer <Jan.Lentfer@web.de>; +Cc: pgsql-admin
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.Lentfer@web.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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>
--
Regards,
Ang Wei Shan
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Maximum Availability Architecture(MAA) for PostgreSQL
2015-04-08 08:22 Maximum Availability Architecture(MAA) for PostgreSQL Wei Shan <weishan.ang@gmail.com>
2015-04-08 12:44 ` Re: Maximum Availability Architecture(MAA) for PostgreSQL Jan Lentfer <Jan.Lentfer@web.de>
2015-04-08 15:35 ` Re: Maximum Availability Architecture(MAA) for PostgreSQL Wei Shan <weishan.ang@gmail.com>
@ 2015-04-08 15:59 ` Chris Winslett <chris@compose.io>
1 sibling, 0 replies; 6+ messages in thread
From: Chris Winslett @ 2015-04-08 15:59 UTC (permalink / raw)
To: Wei Shan <weishan.ang@gmail.com>; +Cc: Jan Lentfer <Jan.Lentfer@web.de>; pgsql-admin
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.ang@gmail.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.Lentfer@web.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@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-admin
>>
>
>
>
> --
> Regards,
> Ang Wei Shan
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Maximum Availability Architecture(MAA) for PostgreSQL
2015-04-08 08:22 Maximum Availability Architecture(MAA) for PostgreSQL Wei Shan <weishan.ang@gmail.com>
2015-04-08 12:44 ` Re: Maximum Availability Architecture(MAA) for PostgreSQL Jan Lentfer <Jan.Lentfer@web.de>
2015-04-08 15:35 ` Re: Maximum Availability Architecture(MAA) for PostgreSQL Wei Shan <weishan.ang@gmail.com>
@ 2015-04-08 16:39 ` John Scalia <jayknowsunix@gmail.com>
2015-04-08 17:35 ` Re: Maximum Availability Architecture(MAA) for PostgreSQL mark <dvlhntr@gmail.com>
1 sibling, 1 reply; 6+ messages in thread
From: John Scalia @ 2015-04-08 16:39 UTC (permalink / raw)
To: Wei Shan <weishan.ang@gmail.com>; Jan Lentfer <Jan.Lentfer@web.de>; +Cc: pgsql-admin
I probably should have said WAL archiving, but this is the act of sending the WAL segments to a standby server. Technically, using synchronous streaming setup, this is not really
required, but it's still a good insurance policy, especially if for whatever reason, the standby loses its connection to the primary. Also, synchronous streaming replication uses a
walreceiver process to send transactions, one at a time, to the standby servers. This is not the act of shipping a WAL segment to that standby, but if you're still sending them to
that standby, then when connectivity is restored, the standby can effectively catch back up to the primary. This is one of the major improvements in V9.4.x where the primary keeps
track of the segments and you no longer have to guess how many to keep on your primary in the event of connectivity loss. In 9.3.x and below, you had tell PostgreSQL how long to
hang onto WAL segments and guess correctly that this would be longer than any outage.
On 4/8/2015 11:35 AM, Wei Shan 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.Lentfer@web.de <mailto:Jan.Lentfer@web.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@postgresql.org <mailto:pgsql-admin@postgresql.org>)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>
>
>
>
> --
> Regards,
> Ang Wei Shan
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Maximum Availability Architecture(MAA) for PostgreSQL
2015-04-08 08:22 Maximum Availability Architecture(MAA) for PostgreSQL Wei Shan <weishan.ang@gmail.com>
2015-04-08 12:44 ` Re: Maximum Availability Architecture(MAA) for PostgreSQL Jan Lentfer <Jan.Lentfer@web.de>
2015-04-08 15:35 ` Re: Maximum Availability Architecture(MAA) for PostgreSQL Wei Shan <weishan.ang@gmail.com>
2015-04-08 16:39 ` Re: Maximum Availability Architecture(MAA) for PostgreSQL John Scalia <jayknowsunix@gmail.com>
@ 2015-04-08 17:35 ` mark <dvlhntr@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: mark @ 2015-04-08 17:35 UTC (permalink / raw)
To: weishan.ang@gmail.com; +Cc: pgsql-admin
> 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.
>>>
>>
>>
>
>
>
>
If you haven't already, You probably should take a look at BDR:
https://wiki.postgresql.org/wiki/BDR_Quick_Start
-Mark
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2015-04-08 17:35 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-04-08 08:22 Maximum Availability Architecture(MAA) for PostgreSQL Wei Shan <weishan.ang@gmail.com>
2015-04-08 12:44 ` Jan Lentfer <Jan.Lentfer@web.de>
2015-04-08 15:35 ` Wei Shan <weishan.ang@gmail.com>
2015-04-08 15:59 ` Chris Winslett <chris@compose.io>
2015-04-08 16:39 ` John Scalia <jayknowsunix@gmail.com>
2015-04-08 17:35 ` mark <dvlhntr@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox