0

CentOS 7.9, postgres 9.6.20 We discovered what we believe to be a corrupt index on our postgres db. This is in production and pulling it down completely is not something we can do. We're working to recreate the index but autovacuum seems to be stuck trying to process the index as well. Sending the autovac pid a terminate command is ignored. Question is how do we disable the autovacuum long enough to perform the index recreation?
To exacerbate the issue, it seems we've now hit the transaction wraparound limit and while miles away from the actual limit, its not a good place to be.

user3038068
  • 33
  • 6

1 Answers1

1

Raise autovacuum_freeze_max_age to 1000000000 for that table:

ALTER TABLE tab SET (autovacuum_freeze_max_age = 1000000000);

This should pacify autovacuum for the time being, while it still is on the safe side.

Then kill the anti-wraparound autovacuum.

Now you can create the new index and drop the old one.

Then change the setting back:

ALTER TABLE tab RESET (autovacuum_freeze_max_age);
Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • That was basically what we ended up doing. Set those parameters and turned off autovac completely in config, restarted server (did require a kill of the autovac daemon as it was not listening to signals to stop). Reindexed the index in question, restarted and reverted autovac/freeze age to normal. – user3038068 Dec 09 '20 at 15:25
  • Note that anti-wraparound autovacuum also runs if `autovacuum = off`. – Laurenz Albe Dec 09 '20 at 15:36