I believe this is because tbl_payment has a constraint that calls a function has_perm() that relies on data in a couple of other tables
Indeed this is the cause. That configuration is not supported. If you need to lookup values in other tables you either need to use an actual FK constraint or create a trigger for the validation.
So I can switch to Custom format for future backups. But regarding the existing backups I have in Tar format, is there any way to successfully restore them? Specifically:
- Any way to ignore or delay constraint checking? Something like disable-triggers?
Using and then disabling triggers is the "closest" solution.
- Any way to tell pg_restore to skip past the failing row, and restore the rest of what was in tbl_payment?
No, COPY doesn't have that capability and that is what is being used under the hood.
- Some other way to go about this?
Ideally figure out how to write an actual FK constraint - otherwise use triggers.
I also wonder if you folks might consider adding something like a --test_restore option to pg_dump
-1; pg_dump should not be trying to restore things. The core developers shouldn't really concern themselves with the various and sundry ways people might want to setup such a process. You have tools for dump, and tools for restore, and you can combine them in whatever fashion you deem useful. Or otherwise acquire someone else's ideas.
David J.
