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

On 06/05/2017 03:35 PM, Ken Tanzer wrote:
> 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.

See this thread for more info:
https://www.postgresql.org/message-id/alpine.DEB.2.20.1703311620581.12863%40tglase.lan.tarent.de

From the docs:

https://www.postgresql.org/docs/9.6/static/sql-createtable.html

"Currently, CHECK expressions cannot contain subqueries nor refer to
variables other than columns of the current row. The system column
tableoid may be referenced, but not any other system column.

>
> 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?

Change the check constraint to a trigger.

>
> 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

Not that I know of. It would be easy enough to point pg_restore at your
own scratch database for testing purposes.

> 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//
> /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.

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com