Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1cu1QB-0004bM-9V for pgsql-admin@arkaria.postgresql.org; Fri, 31 Mar 2017 18:37:15 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1cu1QA-0003Ka-SR for pgsql-admin@arkaria.postgresql.org; Fri, 31 Mar 2017 18:37:14 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1cu1OL-0005tk-O6 for pgsql-admin@postgresql.org; Fri, 31 Mar 2017 18:35:22 +0000 Received: from mx0b-001b1801.pphosted.com ([148.163.158.1] helo=mx0a-001b1801.pphosted.com) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1cu1OH-0005mD-UM for pgsql-admin@postgresql.org; Fri, 31 Mar 2017 18:35:19 +0000 Received: from pps.filterd (m0077986.ppops.net [127.0.0.1]) by mx0b-001b1801.pphosted.com (8.16.0.20/8.16.0.20) with SMTP id v2VIX3Av007752; Fri, 31 Mar 2017 13:35:09 -0500 Received: from hybridhubout.iheartmedia.com (HybridHubOut.iheartmedia.com [207.230.136.8]) by mx0b-001b1801.pphosted.com with ESMTP id 29h7rf18e3-2 (version=TLSv1 cipher=ECDHE-RSA-AES256-SHA bits=256 verify=OK); Fri, 31 Mar 2017 13:35:09 -0500 Received: from NAM01-SN1-obe.outbound.protection.outlook.com (207.46.163.111) by hybridhubin.iheartmedia.com (10.121.10.36) with Microsoft SMTP Server (TLS) id 14.3.319.2; Fri, 31 Mar 2017 13:35:08 -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=kTTlAKfNDRaDmeJv5unmMmGiVitxyTrlF1yngQddLUw=; b=Pf5LDxBZTY/uqprAnnCRrLEMnH60RS0bbv6ex4JasNH9g5jWTa7C/tw4wJOURxo8Ll28mRN3F70rDHiamJj4V5JWZeHzbnrHQdZiYJQRzi5UuYr8J5Ow3NS1LLmAy0I2cGnTdAbReWYuie9NBOayQ46l4p5l+bmHngor91a4tbc= Received: from BN6PR06MB2868.namprd06.prod.outlook.com (10.175.127.150) by BN6PR06MB2865.namprd06.prod.outlook.com (10.175.127.147) 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 18:35:07 +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 18:35:07 +0000 From: "Hammerman, Joseph" To: bricklen CC: "pgsql-admin@postgresql.org" Subject: Re: Failover with a tertiary read-only secondary Thread-Topic: [ADMIN] Failover with a tertiary read-only secondary Thread-Index: AQHSqjtX0jLaEHOcnkmXPpbqywIlMqGvRZmA//+96oA= Date: Fri, 31 Mar 2017 18:35:06 +0000 Message-ID: References: In-Reply-To: Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: authentication-results: gmail.com; dkim=none (message not signed) header.d=none;gmail.com; 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;BN6PR06MB2865;7:MBR/UJ/AYhO3TCmKPFgZqY05C662lluXxAuQdnnCXD+/ix7qCAgs5BT39xwBsRCJHt9xdMGCEcIEcFqdlYoTJRddM1hiP3DLO0iHGCKMhSx7F3NLNC3P0Fgf6jXH7IW+K64fXLepDLDdtfubzN5BwScnZWfVfqPMfShVRAZgRFsOwVWHiQODtsPjdwCsrRsWWIb3N1lpwkTVpXCeQbjLT0YMrTCArB6XO0xIbwnCECeQ44xwkELdR2IBILnn/3E8j1oV7NFV4FKBF7R5RYV2N2v8pGAaRv2nHom3C3GgG+zlsY86whIj1nlpf42OxfIyCWCRuMG6ZEBEUzPJJ8nsjA==;20:MwifWNQw6RzHkHo50LLfxwzNs4W5GadiaJPoAU/RP/mitJq/J9leljE1OFE5akGRZJhqCvVBduD9UTOWd8MygUFdhc26rDIOlNMVwWPX0+BWsEMiW2VNDBEVE7xu+Glp8BtIIyNqdrRaOm12ni59hIO5HB0ESNGjh7GO8ME1aamhXFfV0G8U6kUQXuO4nb7SKkk3OPxOim21XGQyQ8R1QYDKEdbuNP2MMGRY5Mretls0UPdRM8/R3l2e8h29pqbZ3mEhQp4g6JII9gFUo2G9WNi3H4UjtBXh03lWJwVvjT+7KddxvM4JaLj7dr5GXa8iJSfsuv4I3zQX+3N3+FgIcmWz3wl2g9lpeFRWpx8QyvRbZiqohZOCaLGFefrwvlWWmoVEATNVQ4ASLH+cJC7HK3PIAGkUrtXgt8I2cAOIvFQaAsHqovdqhue37Or13xpob1OZMsd8m3aoeWTOiL2W4jK3JWe3DYLh1ndpMFHtTTuAAvOwmy/PgwlKYqdiJY0H x-ms-office365-filtering-correlation-id: 3f2a992e-8dd2-4f23-e04c-08d47864a7a8 x-microsoft-antispam: UriScan:;BCL:0;PCL:0;RULEID:(22001)(2017030254075)(201703131423075)(201703031133081)(201702281549075);SRVR:BN6PR06MB2865; x-microsoft-antispam-prvs: x-exchange-antispam-report-test: UriScan:(158342451672863)(68501114006493); x-exchange-antispam-report-cfa-test: BCL:0;PCL:0;RULEID:(6040450)(601004)(2401047)(8121501046)(5005006)(10201501046)(3002001)(93006095)(93001095)(6041248)(20161123555025)(20161123562025)(20161123564025)(20161123560025)(201703131423075)(201702281528075)(201703061421075)(6072148);SRVR:BN6PR06MB2865;BCL:0;PCL:0;RULEID:;SRVR:BN6PR06MB2865; x-forefront-prvs: 02638D901B x-forefront-antispam-report: SFV:NSPM;SFS:(10019020)(39840400002)(39450400003)(39410400002)(39850400002)(39400400002)(24454002)(51914003)(377424004)(377454003)(1411001)(122556002)(66066001)(50986999)(54356999)(76176999)(36756003)(2900100001)(77096006)(6486002)(5660300001)(7736002)(54896002)(99286003)(236005)(6512007)(6436002)(6506006)(86362001)(53936002)(2906002)(110136004)(6246003)(6916009)(38730400002)(39060400002)(2950100002)(3280700002)(189998001)(3660700001)(80792005)(81156014)(81166006)(8936002)(8676002)(229853002)(3846002)(6116002)(102836003)(4326008)(25786009)(53546009);DIR:OUT;SFP:1102;SCL:1;SRVR:BN6PR06MB2865;H:BN6PR06MB2868.namprd06.prod.outlook.com;FPR:;SPF:None;MLV:ovrnspm;PTR:InfoNoRecords;LANG:en; spamdiagnosticoutput: 1:99 spamdiagnosticmetadata: NSPM Content-Type: multipart/alternative; boundary="_000_D50419346CA6JosephHammermaniheartmediacom_" MIME-Version: 1.0 X-MS-Exchange-CrossTenant-originalarrivaltime: 31 Mar 2017 18:35:06.9016 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Hosted X-MS-Exchange-CrossTenant-id: 122a527e-5b71-4eba-878d-9810b495b9e3 X-MS-Exchange-Transport-CrossTenantHeadersStamped: BN6PR06MB2865 X-OriginatorOrg: iHeartMedia.com X-Proofpoint-Virus-Version: vendor=fsecure engine=2.50.10432:,, definitions=2017-03-31_15:,, 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_D50419346CA6JosephHammermaniheartmediacom_ Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable Hi bricklen, Thanks for the assistance! To answer your questions, 1. Yes, the behavior persisted following a restart. 2. Not a bad idea, but that only delays the necessity of a resync until = the next failover=85. Unless I=92m missing something? Thanks, Joseph Hammerman From: bricklen > Date: Friday, March 31, 2017 at 2:31 PM To: Joseph Hammerman > Cc: "pgsql-admin@postgresql.org" > Subject: Re: [ADMIN] Failover with a tertiary read-only secondary On Fri, Mar 31, 2017 at 9:24 AM, Hammerman, Joseph > wrote: 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 Two questions: 1). After the DNS change, did you restart the downstream replica? (after th= e upstream was promoted) 2). You might be able to sidestep the issue if you set up cascading replica= s, as in the first replica is streaming from the master, and the second rep= lica is streaming from the upstream replica. --_000_D50419346CA6JosephHammermaniheartmediacom_ Content-Type: text/html; charset="Windows-1252" Content-ID: Content-Transfer-Encoding: quoted-printable
Hi bricklen,

Thanks for the assistance! To answer your questions,

  1. Yes, the behavior persisted following a restart.
  2. Not a bad idea= , but that only delays the necessity of a resync until the next failover=85= . Unless I=92m missing something?
Thanks,
Joseph Hammerman

From: bricklen <bricklen@gmail.com>
Date: Friday, March 31, 2017 at 2:3= 1 PM
To: Joseph Hammerman <JosephHammerman@iheartmedia.com>
Cc: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Subject: Re: [ADMIN] Failover with = a tertiary read-only secondary


On Fri, Mar 31, 2017 at 9:24 AM, Hammerman, Jose= ph <Jo= sephHammerman@iheartmedia.com> wrote:

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


Two questions:
1). After the DNS change, did you restart the downstream replica? (aft= er the upstream was promoted)
2). You might be able to sidestep the issue if you set up cascading re= plicas, as in the first replica is streaming from the master, and the secon= d replica is streaming from the upstream replica.

 --_000_D50419346CA6JosephHammermaniheartmediacom_--