0

I have a process running that can not be killed safely: autovacuum: VACUUM public.mytable (to prevent wraparound)

This table has been cleared (aside from some entries that can not be deleted due to the table's corruption during a hardware issue) and can not be dropped, because the vacuum is blocking this. I had to run a kill -9 to stop this process and restarted the database, but you can't disable this autovacuum (to prevent [transaction] wraparound), so the autovacuum is coming back up and immediately getting stuck by this corrupt table.

Any insight into this?

Stephen Kennedy
  • 1
  • 2

1 Answers1

1

First of all, shutdown database server and make a physical copy of data directory to a safe place. Then you could truncate the datafile of corrupted table. E.g.:

--Get datafile path
db=# SELECT pg_relation_filepath('corrupted_table');
 pg_relation_filepath 
----------------------
 base/1234/56789
(1 row)
  1. Enter database directory (e.g: data/base/1234)
  2. Rename the file to 56789_bkp
  3. Create an empty file called 56789: touch 56789
  4. Start database server
  5. Issue a truncate table to force PostgreSQL overwrite datafile: TRUNCATE TABLE corrupted_table;
  6. You may want to VACUUM and make a backup afterwards

Hope this helps.

Michel Milezzi
  • 10,087
  • 3
  • 21
  • 36
  • Thanks, will try this tomorrow after processing is complete. – Stephen Kennedy Sep 06 '18 at 19:19
  • Had to hold off until just now to try this. * Shut down the database, * Moved the file base/16385/24986 to a back up, * Created an empty base/16385/24986 file, * Restarted the database, * And attempted the truncate. This also blocked due again to the autovacuum being stuck on the same table. Likely this is because of off-shoots of this table file: $ ls 24986* 24986 24986.1 24986.2 24986_bkp 24986_fsm 24986_vm – Stephen Kennedy Sep 12 '18 at 19:07