Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
    
Version 8.1 is EOL this month, you'd better do an upgrade to a newer version. wiki.postgresql.org/wiki/PostgreSQL_Release_Support_Policy –  Frank Heikens Nov 25 '10 at 9:02
    
For the record, you should not run vacuum full. –  Stephen Eilert Dec 22 '11 at 16:39

1 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.

share|improve this answer
    
Good advice. If the system is Linux, I would also recommend checking the crontab for possible VACUUMing as part of a daily postgres cron job. I seem to recall an older version of Debian (etch, I think) that did that, and other distros may have done so as well. –  Steven Monday Nov 22 '10 at 23:16

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.