Re: Cannot vacuum even in single-user mode after xidStopLimit is reached

Hello,

On 18.02.2019 15:05, Jahwan Kim wrote:
> So apparently I'm in some loop without any clear way out.
> The most similar thing I found was "Could not finish anti-wraparound
> VACUUM when stop limit is reached"
> https://www.postgresql.org/message-id/53820ED9.3010003@vmware.com. This
> is quite old, though.
>
> Two questions:
>
> (1) Can I revive this database?
> (2) How did this possibly happen?

1 - I think yes. But you need to drop *ALL* (in all bases) temporary
tables manually and only then do VACUUM (in single mode of course).
Unfortunately VACUUM do not drop and vacuum orphan temporary tables.
2 - I think you have a long lived session with long lived temporary
tables. Autovacuum do not freeze temporary tables and it cannot move
relfrozenxid. That's why you get wraparound. To avoid it you need to
drop unnecessary temporary tables or do VACUUM in a long lived session.

--
Arthur Zakirov
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company