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 98 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.3010...@vmware.com. This is quite old, though. Two questions: (1) Can I revive this database? (2) How did this possibly happen? Best Regards, Jahwan
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 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.3010...@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 >
Re: Cannot vacuum even in single-user mode after xidStopLimit is reached
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 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 >
PostgreSQL temp table blues
Hi all, I'd like to share my (painful) experience, in which temp tables caused PostgreSQL shutdown. TL;DR. Do not use temp tables in PostgreSQL with connection pool. * My app uses connection pool AND temp tables, with default setting of ON COMMIT PRESERVE ROWS. * I found out later that autovacuum doesn't deal with temp tables. * The database ages as long as the connection is not closed. * So when the database age reaches XID STOP LIMIT, the database refuses to process any new transaction requests, saying "database is not accepting commands to avoid wraparound data loss... HINT: Stop the postmaster and use a standalone backend to vacuum that database. " After reading the docs, I expected this much. What happens after this surprised me. * Now the database needs to be shutdown. When shutting down, it tries to remove temp tables (of course), but since the database is not accepting any commands, ... The temp tables are then ORPHANED, although there was no database crash! * Because of these orphan temp tables, vacuuming the database in single mode won't work, as suggested by HINT. The orphaned temp tables must be manually dropped in single mode, and only then the database can be vacuumed back to normal state. Without dropping temp tables, vacuuming just takes (quite possibly a long) time and do (almost) nothing. Well, that's all. All of the above facts are documented, albeit tersely. If anybody I know ask me about temp tables in PostgreSQL, I'd just say "DON'T." Best Regards, Jahwan
