Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WVS5Y-00064M-2m for pgsql-admin@arkaria.postgresql.org; Wed, 02 Apr 2014 20:48:48 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1WVS5X-0008Rg-9o for pgsql-admin@arkaria.postgresql.org; Wed, 02 Apr 2014 20:48:47 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WVS5V-0008P3-JF for pgsql-admin@postgresql.org; Wed, 02 Apr 2014 20:48:45 +0000 Received: from mail.journyx.com ([209.163.206.41] helo=mail.int.journyx.com) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WVS5S-0002RE-1l for pgsql-admin@postgresql.org; Wed, 02 Apr 2014 20:48:44 +0000 Received: from localhost (localhost [127.0.0.1]) by mail.int.journyx.com (Postfix) with ESMTP id 376E630009A for ; Wed, 2 Apr 2014 15:48:41 -0500 (CDT) Received: from mail.int.journyx.com ([127.0.0.1]) by localhost (mail.int.journyx.com [127.0.0.1]) (amavisd-new, port 10032) with ESMTP id nQJ8Xk3Sff0O for ; Wed, 2 Apr 2014 15:48:37 -0500 (CDT) Received: from localhost (localhost [127.0.0.1]) by mail.int.journyx.com (Postfix) with ESMTP id 43D763000BB for ; Wed, 2 Apr 2014 15:48:37 -0500 (CDT) X-Virus-Scanned: amavisd-new at mail.int.journyx.com Received: from mail.int.journyx.com ([127.0.0.1]) by localhost (mail.int.journyx.com [127.0.0.1]) (amavisd-new, port 10026) with ESMTP id dMXvKhNxOkqe for ; Wed, 2 Apr 2014 15:48:37 -0500 (CDT) Received: from mail.int.journyx.com (zimbra.int.journyx.com [192.168.100.5]) by mail.int.journyx.com (Postfix) with ESMTP id 0381030009A for ; Wed, 2 Apr 2014 15:48:37 -0500 (CDT) Date: Wed, 2 Apr 2014 15:48:36 -0500 (CDT) From: Scott Whitney Reply-To: Scott Whitney To: pgsql-admin@postgresql.org Message-ID: <1509444490.1424182.1396471716878.JavaMail.zimbra@mail.int.journyx.com> In-Reply-To: References: <77F5F8FA4B407F43BFCFD3FC347896915D1D72@COVMSGCES-MBX01> Subject: Re: Cascade Standby - HELP! MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_1424181_33055288.1396471716877" X-Originating-IP: [192.168.100.188] X-Mailer: Zimbra 8.0.6_GA_5922 (ZimbraWebClient - GC33 (Win)/8.0.6_GA_5922) Thread-Topic: [ADMIN] Cascade Standby - HELP! Thread-Index: Ac9OpyKhRhYnETnBSn2qf58w7YuP2wACu/cpAAA0ISANFJ1C4w== X-Pg-Spam-Score: 0.2 (/) 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 ------=_Part_1424181_33055288.1396471716877 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable My only response that might help you with this is that my setup is:=20 Server 1) primary production db server (5432)=20 Server 2) replicates production (5432) also primary for non-production use = (6432)=20 Server 3) (offsite) replicates #2 (5432 -> 7432 internally and 6432 -> 6432= ) AND hosts internal databases=20 So:=20 1) Production=20 2) Replication + non production=20 3) Internal + replication of NON production + replication of PRODUCTION (vi= a the cascaded server)=20 I would find that when replicating the PRODUCTION server internally (#3), I= had not set wal_keep_segments high enough,=20 due to the lag of WAN replication, production and non production would repl= icate fine (gigabit backend), but sometimes when=20 a load of data was being updated/moved/deleted, the offsite (#3) would fall= behind far enough that it could not recover=20 without a new pg base backup, in essence setting up replication again.=20 I solved this by upping the wal_keep_segments.=20 ----- Original Message ----- > Hi Robert, > Thanks for the response. Mine is 9.2.4 and the issue is specifically with > cascading standby. I don=E2=80=99t think 8.3 had that feature. > Murthy > From: pgsql-admin-owner@postgresql.org > [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Burgholzer, Robert > (DEQ) > Sent: Wednesday, April 02, 2014 3:29 PM > To: pgsql-admin@postgresql.org > Subject: Re: [ADMIN] Cascade Standby - HELP! > Murthy, > I have experienced something similar to this issue, and recall that it ha= d to > do with log file difficulties. I tried my best to document this, but reca= ll > that I had problems working back through it, so please, note that this is= a > procedure that carries no guarantees. Note that the step-by-step calls a > couple of custom scripts that I wrote (they are at the end of the page), = AND > this is on an 8.3 postgresql install: > http://deq3.bse.vt.edu/sifnwiki/index.php/Db_warm_standby-rescue > Regards, > /r/b ------=_Part_1424181_33055288.1396471716877 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable
My only response that might help= you with this is that my setup is:

Server 1) = ;primary production db server (5432)

Server 2) replicates production (5432) also primar= y for non-production use (6432)

Server 3) (offsite) replicates #2 (5432 -> = 7432 internally and 6432 -> 6432) AND hosts internal databases

So:
1) Production
2) Replication + non = production
3) Internal + replication of NON production + replicat= ion of PRODUCTION (via the cascaded server)

I woul= d find that when replicating the PRODUCTION server internally (#3), I had n= ot set wal_keep_segments high enough,
due to the lag of WAN replication, production and non production= would replicate fine (gigabit backend), but sometimes when
a load of data was being updated/moved/d= eleted, the offsite (#3) would fall behind far enough that it could not rec= over
without a new pg bas= e backup, in essence setting up replication again.

I solved this by upping the wal_keep_segments.

 

Hi Robert,

 

Thanks for the respons= e. Mine is 9.2.4 and the issue is specifically with cascading standby. I do= n=E2=80=99t think 8.3 had that feature.

 

Murthy

 

From: pgsql-ad= min-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Burgholzer, Robert (DEQ)
Sent: Wednesday, April 02, 2014 3:29 PM
To: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Cascade Standby - HELP!

 

Murthy,

I have experienced something= similar to this issue, and recall that it had to do with log file difficul= ties.  I tried my best to document this, but recall that I had problems working back through it, so please, note that this is a pro= cedure that carries no guarantees.  Note that the step-by-step calls a= couple of custom scripts that I wrote (they are at the end of the page), A= ND this is on an 8.3 postgresql install:

 

 

Regards,

/r/b

 


------=_Part_1424181_33055288.1396471716877--