Re: Failover with a tertiary read-only secondary

[bricklen <bricklen@xxxxxxxxx>]

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.