Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1ctzMR-0003Bl-S8 for pgsql-admin@arkaria.postgresql.org; Fri, 31 Mar 2017 16:25:16 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1ctzMR-00028Q-Bg for pgsql-admin@arkaria.postgresql.org; Fri, 31 Mar 2017 16:25:15 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1ctzMQ-00027R-FF for pgsql-admin@postgresql.org; Fri, 31 Mar 2017 16:25:14 +0000 Received: from mx0b-001b1801.pphosted.com ([148.163.158.1] helo=mx0a-001b1801.pphosted.com) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1ctzMM-0001tU-Iu for pgsql-admin@postgresql.org; Fri, 31 Mar 2017 16:25:13 +0000 Received: from pps.filterd (m0077987.ppops.net [127.0.0.1]) by mx0b-001b1801.pphosted.com (8.16.0.20/8.16.0.20) with SMTP id v2VGMho0004756 for ; Fri, 31 Mar 2017 11:25:02 -0500 Received: from hybridhubout.iheartmedia.com (HybridHubOut.iheartmedia.com [207.230.136.1]) by mx0b-001b1801.pphosted.com with ESMTP id 29gptcr1t1-1 (version=TLSv1 cipher=ECDHE-RSA-AES256-SHA bits=256 verify=OK) for ; Fri, 31 Mar 2017 11:25:02 -0500 Received: from NAM03-DM3-obe.outbound.protection.outlook.com (207.46.163.21) by hybridhubin.iheartmedia.com (10.121.10.31) with Microsoft SMTP Server (TLS) id 14.3.319.2; Fri, 31 Mar 2017 11:25:01 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=Iheartmedia.onmicrosoft.com; s=selector1-iHeartMedia-com; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version; bh=us8L7GjdzJfEz0FbYn7FlCNQG/YprRkguJwh3Ms3120=; b=cApRcFiFoO44VkAmlcjXfYdC4UW3JgfeFWoH4znavPfiAL6CVinCYpCTqqfoowWvFBQiLNeuOtYm7i9NOYZvY2e1fzppD1ss41iXV8vHotqHWeZK0/ehirEx5+1QOzAsdZMHCRa7Jr3ljpzPyFhIsgKt7Taxn7IjtDGuF6gW8rA= Received: from BN6PR06MB2868.namprd06.prod.outlook.com (10.175.127.150) by BN6PR06MB2867.namprd06.prod.outlook.com (10.175.127.149) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA256_P256) id 15.1.1005.10; Fri, 31 Mar 2017 16:24:59 +0000 Received: from BN6PR06MB2868.namprd06.prod.outlook.com ([10.175.127.150]) by BN6PR06MB2868.namprd06.prod.outlook.com ([10.175.127.150]) with mapi id 15.01.1005.015; Fri, 31 Mar 2017 16:24:59 +0000 From: "Hammerman, Joseph" To: "pgsql-admin@postgresql.org" Subject: Failover with a tertiary read-only secondary Thread-Topic: Failover with a tertiary read-only secondary Thread-Index: AQHSqjtX0jLaEHOcnkmXPpbqywIlMg== Date: Fri, 31 Mar 2017 16:24:58 +0000 Message-ID: Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: authentication-results: postgresql.org; dkim=none (message not signed) header.d=none;postgresql.org; dmarc=none action=none header.from=iheartmedia.com; x-ms-exchange-messagesentrepresentingtype: 1 x-originating-ip: [206.53.223.2] x-microsoft-exchange-diagnostics: 1;BN6PR06MB2867;7:0xMUmF2+SzU5VHCrk1a86x+BON0SLzAH3gIi9InVtN6PogONKVjIbCy1F748xql9zFyt+21n0k05sI44aSl57Gsu0XPJKgQstIzUQQPO6OXnUsdJ824+FrJAS6wje1+Zn/jPEKeboEIMfR4rBxgK5gneF55XXo9M01ePSj6Eyr19gKBPL5aEFfdk/Di9WyzxWfD89/gzUb98eSmG6DFP3H15rFuFS3gyLPZqWRVB8HaGR94naivDFgXGQUQCQlQgOSXFszszedISUz1mxLQ3nNUgYhxvXbZwianmYIikgRS5rVD3N2c2syd2/ahhvhRrxI3hNctiKhbUa7cVjQCVxQ==;20:PIiNhsMmO8NPdH940I82eZx8a4xYLbWmncuNuvULKaChoOItZFZQ39a2BZRoQuCRhMl1yZDy/8sAuHzjqOi3b6uoAC9PhxKTo8csAZyP5sKU26ROITxXdGLUqNm9zJrPJcZU7GrqNl9THbYrvScnbXqrC5O575hD42dppgFlhpyX67YTJhJ/3MzpYLQAeKsIDbYAOeGsHdYnELYbnLOVn/IXI2/FTC5bcI8pCX3zu2GfH1OaCNgoobf+8xBdKqlcQwp6hxSQQXnooXzwawIn+4lEUfM5fRSFUlk8phArKsqIVfb09FYGX9ixwopat/Mpl1LgZ7JNTummfDHt63Wq3Ik3btKqV3qHOdfTEFO/4Ml4kKSsxqzp+lO8A2HpP/0UutyjCp3lJQaedpCCvDtrA5Yv5AbJ0vWecF+r+8Srf0PWFhpFGzKyJfO1mN4dYvtMvmDIX8HFZqoMmuIZtQbF5K3zj6zJjIQIjpxdwT0XiVUylxZkloIjQo56dGu5HhPc x-ms-office365-filtering-correlation-id: 9ffd4733-bd3a-4042-c9e8-08d4785279ce x-microsoft-antispam: UriScan:;BCL:0;PCL:0;RULEID:(22001)(2017030254075)(201703131423075)(201703031133081);SRVR:BN6PR06MB2867; x-microsoft-antispam-prvs: x-exchange-antispam-report-test: UriScan:(158342451672863); x-exchange-antispam-report-cfa-test: BCL:0;PCL:0;RULEID:(6040450)(601004)(2401047)(8121501046)(5005006)(93006095)(93001095)(3002001)(10201501046)(6041248)(201703131423075)(201702281528075)(201703061421075)(20161123560025)(20161123555025)(20161123564025)(20161123562025)(6072148);SRVR:BN6PR06MB2867;BCL:0;PCL:0;RULEID:;SRVR:BN6PR06MB2867; x-forefront-prvs: 02638D901B x-forefront-antispam-report: SFV:NSPM;SFS:(10019020)(6009001)(39850400002)(39450400003)(39400400002)(39410400002)(39840400002)(377424004)(57704003)(2900100001)(6916009)(2351001)(122556002)(5660300001)(66066001)(86362001)(54356999)(6506006)(6486002)(77096006)(6436002)(8676002)(25786009)(80792005)(110136004)(38730400002)(5640700003)(3846002)(6116002)(102836003)(189998001)(2906002)(3660700001)(36756003)(3280700002)(8936002)(50986999)(6512007)(99286003)(54896002)(53936002)(2501003)(81166006);DIR:OUT;SFP:1102;SCL:1;SRVR:BN6PR06MB2867;H:BN6PR06MB2868.namprd06.prod.outlook.com;FPR:;SPF:None;MLV:sfv;LANG:en; spamdiagnosticoutput: 1:99 spamdiagnosticmetadata: NSPM Content-Type: multipart/alternative; boundary="_000_D503FB0C6C95JosephHammermaniheartmediacom_" MIME-Version: 1.0 X-MS-Exchange-CrossTenant-originalarrivaltime: 31 Mar 2017 16:24:58.9707 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Hosted X-MS-Exchange-CrossTenant-id: 122a527e-5b71-4eba-878d-9810b495b9e3 X-MS-Exchange-Transport-CrossTenantHeadersStamped: BN6PR06MB2867 X-OriginatorOrg: iHeartMedia.com X-Proofpoint-Virus-Version: vendor=fsecure engine=2.50.10432:,, definitions=2017-03-31_14:,, signatures=0 X-Pg-Spam-Score: -2.6 (--) 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 --_000_D503FB0C6C95JosephHammermaniheartmediacom_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Hi Postgres Admin users list, We have a PostgreSQL 93 master instance. We have a secondary set up as a ho= t streaming replica. There is a third machine that is a read only slave cop= ying from a CNAME service DNS record. If the primary is demoted and the sec= ondary is promoted and the CNAME redirected to the secondary, the read only= slave will not pick up replication. The error messages in the log are: 2017-03-31 11:08:03 EDT [31062]: [799-1] ( - ) LOG: restarted WAL streami= ng at 0/9000000 on timeline 1 2017-03-31 11:08:03 EDT [31062]: [800-1] ( - ) LOG: replication terminate= d by primary server 2017-03-31 11:08:03 EDT [31062]: [801-1] ( - ) DETAIL: End of WAL reached= on timeline 1 at 0/9000090 I believe that these messages are being generated because the WAL checkpoin= ts on the promoted secondary are not the same as they were on demoted prima= ry. Is there a method to remediate this? Or do I need to perform a full res= ync to both the readonly secondary and the demoted primary? Thanks in advance for any assistance anyone can provide, Joseph Hammerman --_000_D503FB0C6C95JosephHammermaniheartmediacom_ Content-Type: text/html; charset="us-ascii" Content-ID: <9042AD621CA38048BD1FE6F9641799CC@namprd06.prod.outlook.com> Content-Transfer-Encoding: quoted-printable
Hi Postgres Admin users list,

We have a PostgreSQL 93 master instance. We have a secondary set up as= a hot streaming replica. There is a third machine that is a read only slav= e copying from a CNAME service DNS record. If the primary is demoted and th= e secondary is promoted and the CNAME redirected to the secondary, the read only slave will not pick up re= plication. The error messages in the log are:

2017-03-31 11:08:03 EDT [31062]: [799-1]  ( - ) LOG:  restar= ted WAL streaming at 0/9000000 on timeline 1
2017-03-31 11:08:03 EDT [31062]: [800-1]  ( - ) LOG:  replic= ation terminated by primary server
2017-03-31 11:08:03 EDT [31062]: [801-1]  ( - ) DETAIL:  End= of WAL reached on timeline 1 at 0/9000090

I believe that these messages are being generated because the WAL chec= kpoints on the promoted secondary are not the same as they were on demoted = primary. Is there a method to remediate this? Or do I need to perform a ful= l resync to both the readonly secondary and the demoted primary?

Thanks in advance for any assistance anyone can provide,
Joseph Hammerman
--_000_D503FB0C6C95JosephHammermaniheartmediacom_--