Re: Wal fetching standby refuses to promote

[Steven Crandell <steven.crandell@xxxxxxxxx>]

On Mar 18, 2018 09:27, "Bimal" <internetuser2008@xxxxxxxxx> wrote:

You can try to add the trigger file in recovery.conf and perform a promote. Never tried but should work. 

On Thursday, March 15, 2018, 10:56:06 AM PDT, Jerry Sievers <gsievers19@xxxxxxxxxxx> wrote:

This was an atomic snapshot put in recovery and happily reading WALs

from the repo.

It's consistent and allows read-only querying.  It's replayed a few days

worth of WALs and was previously running with a recovery_target_xid =

$some-xid which apparently was never hit and/or aborted.

It would not promote then, after pg_ctl promote.  The 'promote' file did

get created in pgdata.

Now it's running again with recovery.conf that contains just...

standby_mode = on

restore_command = 'cp /somedir/%f %p'

Still it fails to respond to the issuance of a promote.

There is no trigger file config's in recovery.conf and we have never

needed to do same since pg_ctl promote usually works.

Any idea what's causing this?

Thanks

                                                    version                                                   

-----------------------------------------------------------------------------------------------------------------

PostgreSQL 9.6.6 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.4) 5.4.0 20160609, 64-bit

(1 row)

--

Jerry Sievers

Postgres DBA/Development Consulting

e: postgres.consulting@comcast.net

p: 312.241.7800

I've seen situations where promotion is postponed while  there are still future  WAL files available.

 Would recommend:

Stop your postgres service, set your recovery target to a new transaction id or timestamp that  is known to be in the future. Start the postmaster. Manually and then verify that 1) the recovery target is actually in the future after starting and that 2) you are advancing toward that target. It should promote when the target is reached.