Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Yft0m-00040l-80 for pgsql-admin@arkaria.postgresql.org; Wed, 08 Apr 2015 16:39:32 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1Yft0l-00083Y-Ax for pgsql-admin@arkaria.postgresql.org; Wed, 08 Apr 2015 16:39:31 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1Yft0b-0007nF-3C for pgsql-admin@postgresql.org; Wed, 08 Apr 2015 16:39:21 +0000 Received: from mail-vn0-x232.google.com ([2607:f8b0:400c:c0f::232]) by magus.postgresql.org with esmtps (TLS1.2:RSA_AES_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1Yft0N-0002QE-RY for pgsql-admin@postgresql.org; Wed, 08 Apr 2015 16:39:18 +0000 Received: by vnbf1 with SMTP id f1so15988978vnb.0 for ; Wed, 08 Apr 2015 09:39:05 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=message-id:date:from:user-agent:mime-version:to:cc:subject :references:in-reply-to:content-type; bh=MVHNyBUbm0OIJtcZpPpOfkGPnJ76V+88qHNTdfNMNJE=; b=NArQoVqA9eW4HMccht8nNurPY3pq0MXDjE4fH5Kg/sdQX4oBlVqIIONH5EfZhb7HlP vrzt1lCK9YM/dxAd9n3v307IzO0Gu9SgNMZblfTfZyDzAR++KHJJ8xcyHc5vTBNSU87g 0mE2OeVcTwpflFaZbQ5d+Y7tjR3P/uDbNBovAXaZ3Q3HUJY8cgmLcgak+ePJePVlUsKd sYRwdaCNbm/rIXJ4AAVjslwVyPfHn1xenzZ9ynL4LLWzJWU55yoo2kLKQW7QeZcPRr3I ZCOpw+Dj/OQGEqMZuO7ATzCETp1nBhDZ7xBxb+WHnj+Hi4Cce9G+NzLd+Bxpa4XxxNbE eeag== X-Received: by 10.170.188.140 with SMTP id f134mr26168214yke.63.1428511145216; Wed, 08 Apr 2015 09:39:05 -0700 (PDT) Received: from [192.168.254.2] (h77.89.102.166.static.ip.windstream.net. [166.102.89.77]) by mx.google.com with ESMTPSA id e44sm9128954yho.8.2015.04.08.09.39.04 (version=TLSv1.2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Wed, 08 Apr 2015 09:39:04 -0700 (PDT) Message-ID: <552559A5.2000603@gmail.com> Date: Wed, 08 Apr 2015 12:39:01 -0400 From: John Scalia User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:31.0) Gecko/20100101 Thunderbird/31.6.0 MIME-Version: 1.0 To: Wei Shan , Jan Lentfer CC: pgsql-admin@postgresql.org Subject: Re: Maximum Availability Architecture(MAA) for PostgreSQL References: <3f89a3e198877cffec4b544a9fc0e033@imap.lan.net> In-Reply-To: Content-Type: multipart/alternative; boundary="------------000104080206080503070009" X-Pg-Spam-Score: -1.0 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org This is a multi-part message in MIME format. --------------000104080206080503070009 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable I probably should have said WAL archiving, but this is the act of sending= the WAL segments to a standby server. Technically, using synchronous str= eaming setup, this is not really required, but it's still a good insurance policy, especially if for whate= ver reason, the standby loses its connection to the primary. Also, synchr= onous streaming replication uses a walreceiver process to send transactions, one at a time, to the standby s= ervers. This is not the act of shipping a WAL segment to that standby, bu= t if you're still sending them to that standby, then when connectivity is restored, the standby can effecti= vely 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"?Acc= ording to the documentation > , if I= configure replication slot, "/the master does not remove WAL segments u= ntil they have been received by > all standbys/". In sync replication, if the standby is down, the transa= ction 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 ho= t standbys could serve read queries.. > > > > On 8 April 2015 at 20:44, Jan Lentfer > 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 u= sed to > load balance the connection in and for connection failover when= a DB > crashes. Between master and slave, sync replication is being us= ed for > zero-data-loss. > > > Why do you need 3 pg-pool instances? 2 instances uses watchdog shou= ld be sufficient? > And if you plan to use sync replication you need to plan for 3 Post= greSQL Servers (a 3rd one that the sync replication can fail-over to). > > Regards, > > > Jan > > > --=20 > Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org ) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-admin > > > > > --=20 > Regards, > Ang Wei Shan --------------000104080206080503070009 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit 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, 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

--------------000104080206080503070009--