Обсуждение: Cannot vacuum even in single-user mode after xidStopLimit is reached
Any help or hint would be greatly appreciated.
Version 9.5.10
* PostgreSQL stopped, saying it needs vacuum.
* Checked the tables' age, and the top rows are as follows:
table_name | age | table_size
--------------------------------------------+------------+------------
pg_temp_31.tt_wms2_status | 2146483647 | 0 bytes
pg_temp_31.tt_wms2_string | 2134022566 | 0 bytes
pg_temp_16.tt_wms2_status | 1464468964 | 0 bytes
pg_temp_182.tt_wms2_status | 1461637687 | 0 bytes
pg_temp_182.tt_wms2_string | 1453357513 | 0 bytes
pg_temp_16.tt_wms2_string | 1452951861 | 0 bytes
pg_temp_4.tt_wms2_status | 1452494376 | 0 bytes
...
( i.e., all are temp tables. )
* PostgreSQL hint says, "Stop the postmaster and vacuum"
* Tried to vacuum in single-user mode, only in vain, with something like drop that table, and "You might also need to commit or roll back old prepared transactions." also says xid will wrap around after 999998 transactions.
* Tried to drop table pg_temp_31.tt_wms2_status, tried to drop schema pg_temp_31. Nope. PostgreSQL hint says, "Stop the postmaster and vacuum"
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?
Best Regards,
Jahwan
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
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.zakirov@postgrespro.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
On 18.02.2019 17:18, Jahwan Kim wrote: > 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. Can you show the exact whole message? Do I understand correctly. Are temp tables persist in pg_class even after trying to drop temp tables in single-user mode? -- Arthur Zakirov Postgres Professional: http://www.postgrespro.com Russian Postgres Company
Sorry I don't have the exact message right now, but YES.
To be exact, drop table | schema pg_temp_XX... in single-user mode results in "HINT: run vaccum"
On Tue, Feb 19, 2019 at 12:11 AM Arthur Zakirov <a.zakirov@postgrespro.ru> wrote:
On 18.02.2019 17:18, Jahwan Kim wrote:
> 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.
Can you show the exact whole message?
Do I understand correctly. Are temp tables persist in pg_class even
after trying to drop temp tables in single-user mode?
--
Arthur Zakirov
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company