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

On 9.3.17, I tried to restore a tar from pg_dump. It failed thusly:

bash-4.1$ pg_restore -d spc_test_1 agency_backup.spc.2017.06.05_10.30.01.tar

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 10608; 0 107743 TABLE DATA
tbl_payment spc
pg_restore: [archiver (db)] COPY failed for table "tbl_payment": ERROR:
new row for relation "tbl_payment" violates check constraint
"authorized_approvers_only"
DETAIL: Failing row contains (286541, 3685, 2015-09-14, ADJUST, null,
null, 137798, 93.00, HONEY, 4841, 2, SHONCRE, September adjustment,
2015-10-01, null, null, null, null, null, f, f, t, f, f, f, f, null, null,
null, null, 6, 2015-09-14 16:43:37, 25, 2016-02-08 16:34:20, f, null, null,
null, Adjusting approved_at to changed_at for first few approvals
, 6, 2015-09-14 16:43:37, 2015-09-17).
CONTEXT: COPY tbl_payment, line 179785: "286541 3685 2015-09-14
ADJUST \N \N 137798 93.00 HONEY 4841 2
SHONCRE September adjustment 2015-10-0..."
WARNING: errors ignored on restore: 1

The rest of the DB is fine, but tbl_payment has 0 rows. 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, and that tbl_payment is being
restored before those tables. I was able to created a new dump in Custom
format, reorder the List file, and restore that successfully.

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?

- Any way to tell pg_restore to skip past the failing row, and restore
the rest of what was in tbl_payment?

- Some other way to go about this?

I also wonder if you folks might consider adding something like a
--test_restore option to pg_dump that would attempt to create a new
(scratch) DB from the output it creates, and report any errors? I know the
pieces are all there for us users to do that ourselves, but it would be
handy for automated backups and might help us to avoid creating backups
that won't restore successfully. In my case, I think the problem started
from changes we made about 9 months ago, and happily I discovered it during
development/testing and not after a DB crash, which is why I'm also happily
not gouging my eyeballs out right now. :)

Cheers, and thanks in advance!

Ken

--
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ <http://agency-software.org/>*
*https://agency-software.org/demo/client
<https://agency-software.org/demo/client>*
ken(dot)tanzer(at)agency-software(dot)org
<https://mail.google.com/mail/?view=cm&fs=1&tf=1&to=ken(dot)tanzer(at)agency-software(dot)org>
(253) 245-3801

Subscribe to the mailing list
<https://mail.google.com/mail/?view=cm&fs=1&tf=1&to=agency-general-request(at)lists(dot)sourceforge(dot)net&body=subscribe>
to
learn more about AGENCY or
follow the discussion.