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

From: Jahwan Kim <blgl13(dot)net(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Cannot vacuum even in single-user mode after xidStopLimit is reached
Date: 2019-02-18 12:05:29
Message-ID: CADU5SwN6u4radqQgUY2VjEyqXF0KJ6A09PYuJjT=o9d7vzM=Cg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jitendra Loyal 2019-02-18 12:06:44 BEFORE ... Statement-level trigger
Previous Message Achilleas Mantzios 2019-02-18 11:31:31 Re: Slony and triggers on slavenodes.