Skip to content

Avoid autovacuum for entire databases

We have a database with production data for a platform and another database with a production database dump for testing queries. We also have a test database but this one has slightly different DDL because it's for developing new features. Now autovacuum is running on this server and keeps vacuuming this test database.
A simple way for stopping autovacuum from even checking this db at all is adding an entry for every table in pg_autovacuum:


INSERT INTO pg_autovacuum
(vacrelid, enabled, vac_base_thresh, vac_scale_factor, anl_base_thresh, anl_scale_factor, vac_cost_delay, vac_cost_limit, freeze_min_age, freeze_max_age)
SELECT oid,false,-1,-1,-1,-1,-1,-1,-1,-1 FROM pg_class WHERE relkind='r';


This adds a stop entry for every table in your database, even for system tables. If you want the autovacuum daemon to just ignore tables from a specific schema, add a "relnamespace=scheme_oid" in the WHERE clause.


  • Twitter
  • Bookmark Avoid autovacuum for entire databases at del.icio.us
  • Facebook
  • Google Bookmarks
  • FriendFeed
  • Digg Avoid autovacuum for entire databases
  • Bloglines Avoid autovacuum for entire databases
  • Technorati Avoid autovacuum for entire databases
  • Fark this: Avoid autovacuum for entire databases
  • Bookmark Avoid autovacuum for entire databases at YahooMyWeb
  • Bookmark Avoid autovacuum for entire databases at Furl.net
  • Bookmark Avoid autovacuum for entire databases at reddit.com
  • Bookmark Avoid autovacuum for entire databases at blinklist.com
  • Bookmark Avoid autovacuum for entire databases at Spurl.net
  • Bookmark Avoid autovacuum for entire databases at Simpy.com
  • Bookmark Avoid autovacuum for entire databases at blogmarks
  • Bookmark Avoid autovacuum for entire databases with wists
  • wong it!
  • Bookmark using any bookmark manager!
  • Stumble It!
  • Identi.ca

Trackbacks

No Trackbacks

Comments

Display comments as Linear | Threaded

No comments

Add Comment

Enclosing asterisks marks text as bold (*word*), underscore are made via _word_.
E-Mail addresses will not be displayed and will only be used for E-Mail notifications.
To leave a comment you must approve it via e-mail, which will be sent to your address after submission.
Form options