Re: Help with restoring a dump in Tar format? (dependencies/ordering)

On Mon, Jun 5, 2017 at 3:35 PM, Ken Tanzer <ken(dot)tanzer(at)gmail(dot)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.​