patternsqlMinor
Is there a way to make pg_dump ignore errors instead of stopping?
Viewed 0 times
ignorepg_dumpstoppingmakewayinsteaderrorsthere
Problem
I'm running a pg_dump of my postgres 9.4 DB and occassionally it'll hit an error if some other process has an exclusive lock on a table. Is there a way for the pg_dump to just ignore that table and proceed with the rest of the database instead of just stopping in its tracks?
Solution
This functionality is missing from pg_dump and the right solution would be to enhance it by adding a flag with something like
In a scenario where you only want to restore some tables this would be helpful.
There are few options using which you can implement a workaround:
-
Use the parameter below to reduce timeout to 10 sec, log the table and dynamically exclude that table from a new pg_dump command.
-
If you have a standby DB, use that to do your backups and you will have lesser chance of this issue happening.
-
If the issue is due to some unlogged stage tables, you can use the parameter below to ignore them ( I have not tested this so not 100% sure)
-
Write a wrapper script and backup each schema or table, this way if the failure happens you will miss one schema or table, you will have to make sure to implement a similar wrapper for restore as doing it by hand would be problematic, also this would not be consistent at a DB level.
--ignore-errors, the comments miss the point that backing up some tables is better than none.In a scenario where you only want to restore some tables this would be helpful.
There are few options using which you can implement a workaround:
-
Use the parameter below to reduce timeout to 10 sec, log the table and dynamically exclude that table from a new pg_dump command.
--lock-wait-timeout=timeout--exclude-table=table-
If you have a standby DB, use that to do your backups and you will have lesser chance of this issue happening.
-
If the issue is due to some unlogged stage tables, you can use the parameter below to ignore them ( I have not tested this so not 100% sure)
--no-unlogged-table-data-
Write a wrapper script and backup each schema or table, this way if the failure happens you will miss one schema or table, you will have to make sure to implement a similar wrapper for restore as doing it by hand would be problematic, also this would not be consistent at a DB level.
Context
StackExchange Database Administrators Q#171217, answer score: 3
Revisions (0)
No revisions yet.