Re: Failover with a tertiary read-only secondary

["Hammerman, Joseph" <JosephHammerman@xxxxxxxxxxxxxxx>]

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…. Unless I’m missing something?

Thanks,

Joseph Hammerman

From: bricklen <bricklen@xxxxxxxxx>
Date: Friday, March 31, 2017 at 2:31 PM
To: Joseph Hammerman <JosephHammerman@xxxxxxxxxxxxxxx>
Cc: "pgsql-admin@xxxxxxxxxxxxxx" <pgsql-admin@xxxxxxxxxxxxxx>
Subject: Re: Failover with a tertiary read-only secondary

On Fri, Mar 31, 2017 at 9:24 AM, Hammerman, Joseph <JosephHammerman@xxxxxxxxxxxxxxx> 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 slave copying from a CNAME service DNS record. If the primary is demoted and the secondary 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 streaming at 0/9000000 on timeline 1

2017-03-31 11:08:03 EDT [31062]: [800-1]  ( - ) LOG:  replication 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? (after the upstream was promoted)

2). You might be able to sidestep the issue if you set up cascading replicas, as in the first replica is streaming from the master, and the second replica is streaming from the upstream replica.