Schema pg_dump failed due to a Lock on a table

lockingpg-dumptable

I'm running backup restore on a schema every day and get this every now and then:

pg_dump: Error message from server: ERROR: relation not found (OID
86157003) DETAIL: This can be validly caused by a concurrent delete
operation on this object. pg_dump: The command was: LOCK TABLE
myschema.products IN ACCESS SHARE MODE

How can this be avoided? It seems the table was being used at the time, or someone was running something aginst the table.

As far as I understand, pg_dump could run even if users are doing something with the table but it doesn't seem to be the case.

Thanks,

Best Answer

From pg_dump manual:

Requesting exclusive locks on database objects while running a parallel dump could cause the dump to fail. The reason is that the pg_dump master process requests shared locks on the objects that the worker processes are going to dump later in order to make sure that nobody deletes them and makes them go away while the dump is running. If another client then requests an exclusive lock on a table, that lock will not be granted but will be queued waiting for the shared lock of the master process to be released. Consequently any other access to the table will not be granted either and will queue after the exclusive lock request. This includes the worker process trying to dump the table. Without any precautions this would be a classic deadlock situation. To detect this conflict, the pg_dump worker process requests another shared lock using the NOWAIT option. If the worker process is not granted this shared lock, somebody else must have requested an exclusive lock in the meantime and there is no way to continue with the dump, so pg_dump has no choice but to abort the dump.

So pg_dump can run when other users use database, but not when tables are being dropped. Your error message clearly states, that the object was not found and that's what caused locking to fail. It's also hinted in that message that it probably got deleted.

To avoid in the future, verify what could cause the object to be deleted during backup (maintenance? maybe some periodically run scripts/functions delete tables?) and schedule your backup after that.

Related Question