How to cancel vacuum process in AWS RDS PostgreSQL

Question

Is there a way to cancel an automatic vacuum in rds postgresql? When running

• select pg_cancel_backend(...);
• select pg_terminate_backend(...);

with a user that has the rds_superuser role an error occurs:

• ERROR: must be a superuser to cancel superuser query
• ERROR: must be a superuser to terminate superuser process

Answer 1

rds_superuser is not a superuser.

You should not cancel autovacuum, it is doing an important job for the integrity of your database. But unless it is an anti-wraparound autovacuum run, you can make it die with a conflicting lock:

BEGIN;
SET LOCAL lock_timeout = '2s';
LOCK table_being_vacuumed IN SHARE UPDATE EXCLUSIVE MODE;
ROLLBACK;

Normal autovacuum runs die if they block a user session for more than a minute. There is nothing you can do about anti-wraparound autovacuum runs.