Re: PG13 fails to startup even though the current transaction is equal to the target transaction

On 2021/03/22 21:40, Sean Jezewski wrote:
> Hi Kyotaro -
>
> Thanks for the response.
>
> I think it boils down to your comment:
>
> > I'm not sure.  The direct cause of the "issue" is a promotion trigger
> > that came before reaching recovery target.  That won't happen if the
> > "someone" doesn't do that.
>
> I think the question is 'under what conditions is it safe to do the promotion' ?
>
> What is your recommendation in this case? The end of the archive has been reached. All transactions have been replayed. And in fact the current transaction id is exactly equal to the target recovery transaction id.

I guess that the transaction with this current XID has not been committed
yet at that moment. Right? I thought that because you confirmed the XID
by SELECT pg_catalog.txid_snapshot_xmax(pg_catalog.txid_current_snapshot()).
IIUC this query doesn't return the XID of already-committed transaction.

The standby thinks that the recovery reaches the recovery target when
the XID of *committed* transaction get equal to the recovery_target_xid.
So in your case, the standby didn't reached the recovery target when you
requested the promotion. IMO this is why you got that FATAL error.

Regards,

--
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION