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:

1
2
3

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.