On Mon, Jun 5, 2017 at 3:35 PM, Ken Tanzer <ken.tan...@gmail.com> wrote:
> 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.