HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Is there a way to make pg_dump ignore errors instead of stopping?

Submitted by: @import:stackexchange-dba··
0
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 --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.