A67: Skipping vacuum - lock not available

Example Postgres Log Output:

LOG: skipping vacuum of "mytable" --- lock not available

Explanation:

This event indicates that a VACUUM operation couldn't start because it couldn't acquire the required lock.

That is, for regular VACUUM it couldn't acquire an ShareUpdateExclusiveLock on the specified table.

In the case of VACUUM FULL this indicates a AccessExclusiveLock could not be acquired, which is required since VACUUM FULL rewrites the table.

Recommended Action:

Review all current lock holders on the table by running the following:

SELECT * FROM pg_locks WHERE relation = 'mytable'::regclass;

This will return something like this:

 locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid  |        mode         | granted | fastpath
----------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+------+---------------------+---------+----------
 relation |  1691898 |  1693426 |      |       |            |               |         |       |          | 3/22886            | 3502 | AccessExclusiveLock | t       | f
(1 row)

In this case you can see that there is a granted AccessExclusiveLock that prevents VACUUM from running.

The locks that are relevant and can block regular VACUUM are:

• ShareUpdateExclusiveLock
• ShareLock
• ShareRowExclusiveLock
• ExclusiveLock
• AccessExclusiveLock

The locks that can block VACUUM FULL are:

• AccessExclusiveLock

You will need to wait for the lock to be available, or cancel the operation running that holds the lock (see pid field) for VACUUM to run.

Learn More:

No additional resources available.