[GENERAL] 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
(253) 245-3801
learn more about AGENCY or
follow the discussion.
В списке pgsql-general по дате отправления:
Следующее
От: "David G. Johnston"Дата:
Сообщение: Re: [GENERAL] Help with restoring a dump in Tar format? (dependencies/ordering)