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

Thanks for the reply.

(1) The problem is, I cannot drop any of the temp tables, not even in the
single-user mode. It says
'vacuum', 'You might also need to commit or roll back old prepared
transactions.'
And, no, there seems to be no pending prepared transaction either.

Best,
Jahwan

On Mon, Feb 18, 2019 at 10:49 PM Arthur Zakirov <a(dot)zakirov(at)postgrespro(dot)ru>
wrote:

> 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
>