Autovacuum Blocking Vacuum

This mainly shows how to kill sessions and look at locking in postgres

SET  vacuum_cost_delay  = 0

pg_terminate_backend()

ALTER TABLE hd_property_instance SET (autovacuum_enabled = false,
toast.autovacuum_enabled = false);

SELECT pg_stat_activity, pg_locks.mode
FROM pg_stat_activity
JOIN pg_locks USING (pid)
JOIN pg_class ON pg_locks.relation = pg_class.oid
WHERE pg_class.relname = ‘users’
AND pg_locks.mode IN
(‘ShareUpdateExclusiveLock’, ‘ShareLock’, ‘ShareRowExclusiveLock’, ‘ExclusiveLock’, ‘AccessExclusiveLock’);

\x

SELECT pg_stat_activity.pid, pg_stat_activity.query ,pg_stat_activity.state, pg_locks.mode
FROM pg_stat_activity
JOIN pg_locks USING (pid)
JOIN pg_class ON pg_locks.relation = pg_class.oid
WHERE pg_class.relname = ‘users’
AND pg_locks.mode IN
(‘ShareUpdateExclusiveLock’, ‘ShareLock’, ‘ShareRowExclusiveLock’, ‘ExclusiveLock’, ‘AccessExclusiveLock’);

SELECT locktype, relation::regclass AS tablename
FROM pg_locks
JOIN pg_stat_activity USING (pid)
WHERE pid = 22328;