Re: Wal fetching standby refuses to promote

On Mar 18, 2018 09:27, "Bimal" <internetuser2008(at)yahoo(dot)com> 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(at)comcast(dot)net> 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(dot)consulting(at)comcast(dot)net
p: 312.241.7800 <(312)%20241-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.