From: | Ron Johnson <ronljohnsonjr(at)gmail(dot)com> |
---|---|
To: | Pgsql-admin <pgsql-admin(at)lists(dot)postgresql(dot)org> |
Subject: | Strange "permission denied" errors on pg_restore |
Date: | 2024-06-28 13:34:08 |
Message-ID: | CANzqJaBrzXfebHJaDLRWenr4WRgvRssTgEQR_O20N0sSv9MHLw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
PG 16.3, of a PG 16 pg_dump of a PG 15.7 database.
Here's a sample of the errors from the "pg_restore -v" log, while attached
are samples from the postgresql.log file:
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 847489; 0 1814433 TABLE DATA batch_rp4_y2022m08
fis_tap
pg_restore: error: COPY failed for table "batch_rp4_y2022m08": ERROR:
permission denied for schema tapschema
LINE 1: SELECT 1 FROM ONLY "tapschema"."lockbox" x WHERE "lockbox_id...
^
QUERY: SELECT 1 FROM ONLY "tapschema"."lockbox" x WHERE "lockbox_id"
OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 847492; 0 1814598 TABLE DATA batch_rp4_y2022m05
fis_tap
pg_restore: error: COPY failed for table "batch_rp4_y2022m05": ERROR:
permission denied for schema tapschema
LINE 1: SELECT 1 FROM ONLY "tapschema"."lockbox" x WHERE "lockbox_id...
^
QUERY: SELECT 1 FROM ONLY "tapschema"."lockbox" x WHERE "lockbox_id"
OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 847491; 0 1814543 TABLE DATA batch_rp4_y2022m06
fis_tap
pg_restore: error: COPY failed for table "batch_rp4_y2022m06": ERROR:
permission denied for schema tapschema
LINE 1: SELECT 1 FROM ONLY "tapschema"."lockbox" x WHERE "lockbox_id...
^
QUERY: SELECT 1 FROM ONLY "tapschema"."lockbox" x WHERE "lockbox_id"
OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x
pg_restore: finished item 847489 TABLE DATA batch_rp4_y2022m08
The only errors were on COPY statements. The tables and indices were all
created, and attached to their parent tables.
Individually restoring the tables from the backup succeeded without error.
There's a FK relationship from batch.lockbox_id to lockbox.lockbox_id, but
why should that matter, since FK constraints are created after tables are
loaded?
But that gets me thinking... table "batch" is doubly-partitioned: first on
the "rp" number, and then the batch_rpX tables are partitioned by the
process_date. Might the FK on the batch_rp4 table have already been
created?
Attachment | Content-Type | Size |
---|---|---|
restore_errors.txt | text/plain | 25.5 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Imran Khan | 2024-06-28 13:42:42 | Re: Postgresql 12.19 compatible with RHEL 9 |
Previous Message | Wasim Devale | 2024-06-28 10:00:17 | Re: Postgresql 12.19 compatible with RHEL 9 |