How to make sure autovacuum does not block dropdb?

Question

The title basically says it. Running dropdb seems to fail if autovacuum is active. I'm currently using vacuumdb --full x && dropdb x as workaround (I assume that after a full vacuum run no vacuum will be active afterwards if the db is not modified.) However this obviously is not very efficient since I don't need a db which I'm about to delete to be cleaned.

I'm not sure but it seems that autovacuum runs via the postgres user and thus one could maybe use https://dba.stackexchange.com/questions/11893/force-drop-db-while-others-may-be-connected-in-postgresql to kick it out while vacuum is running.

Still, is there a better/simpler way?

Answer 1

You can stop the postgres instance and start again in single user mode. Insert --single as option to postgres or pg_ctl command while starting postgres. More to dig here. Please note that You need to connect to another database than the one You want to drop.

Answer 2

In my case for postgres version 15. I found no other alternative than to call the following to allow the dropping of the database

VACUUM FULL; DROP DATABASE mydatabase;

It will take a number of seconds however otherwise it seems that postgres only deletes data on autovacuum being called. Calling VACUUM in this fashion manually forces this to occur and stops the the drop query from idling.