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.tanzer@xxxxxxxxxxxxxxxxxxx
<https://mail.google.com/mail/?view=cm&fs=1&tf=1&to=ken.tanzer@xxxxxxxxxxxxxxxxxxx>
(253) 245-3801
Subscribe to the mailing list
<https://mail.google.com/mail/?view=cm&fs=1&tf=1&to=agency-general-request@xxxxxxxxxxxxxxxxxxxxx&body=subscribe> to
learn more about AGENCY or
follow the discussion.
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
