Thread: Wal fetching standby refuses to promote

Wal fetching standby refuses to promote

From

Jerry Sievers

Date:

15 March 2018, 20:55:09

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

Re: Wal fetching standby refuses to promote

From

Bimal

Date:

18 March 2018, 19:26:42

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@comcast.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.consulting@comcast.net

p: 312.241.7800

Re: Wal fetching standby refuses to promote

From

Steven Crandell

Date:

18 March 2018, 23:52:05

On Mar 18, 2018 09:27, "Bimal" <internetuser2008@yahoo.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@comcast.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.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.