How do I disable autovacuum on Postgres 8.1.22?

Question

I'm in the process of migrating an old environment to a more modern software stack, and am trying to bandaid what I can while this process goes on.

Right now I'm trying to keep the database server from falling over by disabling autovacuum (and instead run full vacuums once a night, the autovacuum load is causing problems during peak hours), and am somehow failing to do. I have autovacuum = off in postgresql.conf and restarted postgres, but that doesn't seem to do the trick.

My stop-gap measure is a while loop to kill off autovacuum minutely, but I cannot imagine this is healthy.

Answer 1

Setting autovacuum to off in the postgresql.conf and signalling the server to reload the configuration has always stopped autovacuum in my experience. I always turn off autovacuum when doing a large DB restore for example.

Where are you changing the configuration? If you're doing so in PGAdmin, ensure that you've saved your change before signalling the server to reload the config.

What's your evidence that autovacuum hasn't stopped? Make sure you refresh any screen in pgAdmin which tells you about running processes when you make changes. There's been a few times where I've been caught out by PGAdmin not refreshing automatically.

Double check your actual postgresql.conf file. If you're running in a windows environment, try running "reload configuration" as administrator. Try restarting the service.

Read the log files. When you restart the service or reload the config after making a change to the configuration, is the change reflected in the log? It should be.

As a longer term effort, consider upgrading to newer versions of postgreSQL. In 8.1 auto-vacuum was very new; in 8.3 onwards there are marked improvements to the way that auto-vacuum works, reducing the impact on your database.