Failover with a tertiary read-only secondary

From: "Hammerman, Joseph" <JosephHammerman(at)iheartmedia(dot)com>
To: "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org>
Subject: Failover with a tertiary read-only secondary
Date: 2017-03-31 16:24:58
Message-ID: D503FB0C.6C95%JosephHammerman@iheartmedia.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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 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

I believe that these messages are being generated because the WAL checkpoints 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 full resync to both the readonly secondary and the demoted primary?

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

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Samed YILDIRIM 2017-03-31 16:36:44 Re: Help with bad performance with synchronous commit
Previous Message David G. Johnston 2017-03-31 14:57:01 Re: Help with bad performance with synchronous commit