RDS Postgresql 12.11
Backed up a database from one RDS instance, and now am trying to restore it
to a new instance. (If you're wondering why, it's to restore in an instance
with less disk space.)
Here are the commands:
export PGHOST=${RDSENV}.xxxxxxxxxxxx.us-east-1.rds.amazonaws.com
cd /migrate/TASK001793786/2022-10-19b
DB=sides
pg_restore -v --create --clean --jobs=`nproc` -Fd --dbname=template1 $DB
Attached are log file extracts showing the errors. The errors are only on
partitioned child tables, the list of which is also attached. However, some
(but not many)
Some of the erroring tables are owned by sides_owner and some are owned by
"stransuser".
"\z" on a child with errors looks exactly like a child without errors.
What could be the problem?
--
Angular momentum makes the world go 'round.
[a467197@uisawsclitstlinrds TASK001793786]$ grep -B3 ERROR: TASK001793786_restore.out | more
pg_restore: processing data for table "strans.response_xml_p2019_10"
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 7699; 0 3661008 TABLE DATA response_xml_p2019_10 sides_owner
pg_restore: error: COPY failed for table "response_xml_p2019_10": ERROR: permission denied for schema strans
--
QUERY: SELECT 1 FROM ONLY "strans"."employer_response" x WHERE "employer_response_id" OPERATOR(pg_catalog.=) $1 AND "part_date" OPERATOR(pg_catalog.=) $
2 FOR KEY SHARE OF x
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 7931; 0 3847065 TABLE DATA response_xml_p2022_01 stransuser
pg_restore: error: COPY failed for table "response_xml_p2022_01": ERROR: permission denied for schema strans
--
pg_restore: launching item 8045 TABLE DATA dd_request_attachment
pg_restore: processing data for table "strans.dd_request_attachment"
pg_restore: from TOC entry 7715; 0 3661104 TABLE DATA response_xml_p2021_02 sides_owner
pg_restore: error: COPY failed for table "response_xml_p2021_02": ERROR: permission denied for schema strans
--
pg_restore: launching item 7692 TABLE DATA response_xml_p2019_03
pg_restore: processing data for table "strans.response_xml_p2019_03"
pg_restore: from TOC entry 7694; 0 3660978 TABLE DATA response_xml_p2019_05 sides_owner
pg_restore: error: COPY failed for table "response_xml_p2019_05": ERROR: permission denied for schema strans
--
pg_restore: launching item 7687 TABLE DATA response_xml_p2018_10
pg_restore: processing data for table "strans.response_xml_p2018_10"
pg_restore: from TOC entry 7692; 0 3660966 TABLE DATA response_xml_p2019_03 sides_owner
pg_restore: error: COPY failed for table "response_xml_p2019_03": ERROR: permission denied for schema strans
--
pg_restore: launching item 7998 TABLE DATA ev_request_xml
pg_restore: processing data for table "strans.ev_request_xml"
pg_restore: from TOC entry 7687; 0 3660936 TABLE DATA response_xml_p2018_10 sides_owner
pg_restore: error: COPY failed for table "response_xml_p2018_10": ERROR: permission denied for schema strans
--
pg_restore: launching item 7688 TABLE DATA response_xml_p2018_11
pg_restore: processing data for table "strans.response_xml_p2018_11"
pg_restore: from TOC entry 7688; 0 3660942 TABLE DATA response_xml_p2018_11 sides_owner
pg_restore: error: COPY failed for table "response_xml_p2018_11": ERROR: permission denied for schema strans
--
pg_restore: launching item 7429 FK CONSTRAINT employer_response response_to_request
pg_restore: creating FK CONSTRAINT "strans.employer_response response_to_request"
pg_restore: from TOC entry 7691; 0 3660960 TABLE DATA response_xml_p2019_02 sides_owner
pg_restore: error: COPY failed for table "response_xml_p2019_02": ERROR: permission denied for schema strans
--
pg_restore: launching item 7725 TABLE DATA response_xml_p2021_12
pg_restore: processing data for table "strans.response_xml_p2021_12"
pg_restore: from TOC entry 7683; 0 3660543 TABLE DATA request_xml_p2021_10 sides_owner
pg_restore: error: COPY failed for table "request_xml_p2021_10": ERROR: permission denied for schema strans
--
pg_restore: launching item 7670 TABLE DATA request_xml_p2020_09
pg_restore: processing data for table "strans.request_xml_p2020_09"
pg_restore: from TOC entry 7725; 0 3661164 TABLE DATA response_xml_p2021_12 sides_owner
pg_restore: error: COPY failed for table "response_xml_p2021_12": ERROR: permission denied for schema strans
--
pg_restore: launching item 7719 TABLE DATA response_xml_p2021_06
pg_restore: processing data for table "strans.response_xml_p2021_06"
pg_restore: from TOC entry 7670; 0 3660465 TABLE DATA request_xml_p2020_09 sides_owner
pg_restore: error: COPY failed for table "request_xml_p2020_09": ERROR: permission denied for schema strans
--
pg_restore: launching item 8031 TABLE DATA dd_report_extract
pg_restore: processing data for table "strans.dd_report_extract"
pg_restore: from TOC entry 7719; 0 3661128 TABLE DATA response_xml_p2021_06 sides_owner
pg_restore: error: COPY failed for table "response_xml_p2021_06": ERROR: permission denied for schema strans
--
pg_restore: launching item 7657 TABLE DATA request_xml_p2019_08
pg_restore: processing data for table "strans.request_xml_p2019_08"
pg_restore: from TOC entry 7649; 0 3660339 TABLE DATA request_xml_p2018_12 sides_owner
pg_restore: error: COPY failed for table "request_xml_p2018_12": ERROR: permission denied for schema strans
--
pg_restore: launching item 8007 TABLE DATA ev_day
pg_restore: processing data for table "strans.ev_day"
pg_restore: from TOC entry 7657; 0 3660387 TABLE DATA request_xml_p2019_08 sides_owner
pg_restore: error: COPY failed for table "request_xml_p2019_08": ERROR: permission denied for schema strans
--
pg_restore: launching item 6582 INDEX xif2ev_day
pg_restore: creating INDEX "strans.xif2ev_day"
pg_restore: from TOC entry 7940; 0 3847173 TABLE DATA response_xml_p2022_10 stransuser
pg_restore: error: COPY failed for table "response_xml_p2022_10": ERROR: permission denied for schema strans
--
pg_restore: launching item 6736 INDEX xif1dd_request_response
pg_restore: creating INDEX "strans.xif1dd_request_response"
pg_restore: from TOC entry 7714; 0 3661098 TABLE DATA response_xml_p2021_01 sides_owner
pg_restore: error: COPY failed for table "response_xml_p2021_01": ERROR: permission denied for schema strans
--
pg_restore: launching item 7659 TABLE DATA request_xml_p2019_10
pg_restore: processing data for table "strans.request_xml_p2019_10"
pg_restore: from TOC entry 7659; 0 3660399 TABLE DATA request_xml_p2019_10 sides_owner
pg_restore: error: COPY failed for table "request_xml_p2019_10": ERROR: permission denied for schema strans
$ psql sides -c "\d" | grep " re[qs]" | grep table
strans | request_xml | partitioned table | sides_owner
strans | request_xml_p2018_10 | table | sides_owner
strans | request_xml_p2018_11 | table | sides_owner
strans | request_xml_p2018_12 | table | sides_owner
strans | request_xml_p2019_01 | table | sides_owner
strans | request_xml_p2019_02 | table | sides_owner
strans | request_xml_p2019_03 | table | sides_owner
strans | request_xml_p2019_04 | table | sides_owner
strans | request_xml_p2019_05 | table | sides_owner
strans | request_xml_p2019_06 | table | sides_owner
strans | request_xml_p2019_07 | table | sides_owner
strans | request_xml_p2019_08 | table | sides_owner
strans | request_xml_p2019_09 | table | sides_owner
strans | request_xml_p2019_10 | table | sides_owner
strans | request_xml_p2019_11 | table | sides_owner
strans | request_xml_p2019_12 | table | sides_owner
strans | request_xml_p2020_01 | table | sides_owner
strans | request_xml_p2020_02 | table | sides_owner
strans | request_xml_p2020_03 | table | sides_owner
strans | request_xml_p2020_04 | table | sides_owner
strans | request_xml_p2020_05 | table | sides_owner
strans | request_xml_p2020_06 | table | sides_owner
strans | request_xml_p2020_07 | table | sides_owner
strans | request_xml_p2020_08 | table | sides_owner
strans | request_xml_p2020_09 | table | sides_owner
strans | request_xml_p2020_10 | table | sides_owner
strans | request_xml_p2020_11 | table | sides_owner
strans | request_xml_p2020_12 | table | sides_owner
strans | request_xml_p2021_01 | table | sides_owner
strans | request_xml_p2021_02 | table | sides_owner
strans | request_xml_p2021_03 | table | sides_owner
strans | request_xml_p2021_04 | table | sides_owner
strans | request_xml_p2021_05 | table | sides_owner
strans | request_xml_p2021_06 | table | sides_owner
strans | request_xml_p2021_07 | table | sides_owner
strans | request_xml_p2021_08 | table | sides_owner
strans | request_xml_p2021_09 | table | sides_owner
strans | request_xml_p2021_10 | table | sides_owner
strans | request_xml_p2021_11 | table | sides_owner
strans | request_xml_p2021_12 | table | sides_owner
strans | request_xml_p2022_01 | table | stransuser
strans | request_xml_p2022_02 | table | stransuser
strans | request_xml_p2022_03 | table | stransuser
strans | request_xml_p2022_04 | table | stransuser
strans | request_xml_p2022_05 | table | stransuser
strans | request_xml_p2022_06 | table | stransuser
strans | request_xml_p2022_07 | table | stransuser
strans | request_xml_p2022_08 | table | stransuser
strans | request_xml_p2022_09 | table | stransuser
strans | request_xml_p2022_10 | table | stransuser
strans | request_xml_p2022_11 | table | stransuser
strans | request_xml_p2022_12 | table | stransuser
strans | request_xml_p2023_01 | table | sides_owner
strans | request_xml_p2023_02 | table | sides_owner
strans | request_xml_p2023_03 | table | sides_owner
strans | request_xml_p2023_04 | table | sides_owner
strans | request_xml_p2023_05 | table | sides_owner
strans | request_xml_p2023_06 | table | sides_owner
strans | request_xml_p2023_07 | table | sides_owner
strans | request_xml_p2023_08 | table | sides_owner
strans | request_xml_p2023_09 | table | sides_owner
strans | request_xml_p2023_10 | table | sides_owner
strans | request_xml_p2023_11 | table | sides_owner
strans | request_xml_p2023_12 | table | sides_owner
strans | request_xml_ple2018_09 | table | sides_owner
strans | response_attachment | table | sides_owner
strans | response_x_error | table | sides_owner
strans | response_xml | partitioned table | sides_owner
strans | response_xml_p2018_10 | table | sides_owner
strans | response_xml_p2018_11 | table | sides_owner
strans | response_xml_p2018_12 | table | sides_owner
strans | response_xml_p2019_01 | table | sides_owner
strans | response_xml_p2019_02 | table | sides_owner
strans | response_xml_p2019_03 | table | sides_owner
strans | response_xml_p2019_04 | table | sides_owner
strans | response_xml_p2019_05 | table | sides_owner
strans | response_xml_p2019_06 | table | sides_owner
strans | response_xml_p2019_07 | table | sides_owner
strans | response_xml_p2019_08 | table | sides_owner
strans | response_xml_p2019_09 | table | sides_owner
strans | response_xml_p2019_10 | table | sides_owner
strans | response_xml_p2019_11 | table | sides_owner
strans | response_xml_p2019_12 | table | sides_owner
strans | response_xml_p2020_01 | table | sides_owner
strans | response_xml_p2020_02 | table | sides_owner
strans | response_xml_p2020_03 | table | sides_owner
strans | response_xml_p2020_04 | table | sides_owner
strans | response_xml_p2020_05 | table | sides_owner
strans | response_xml_p2020_06 | table | sides_owner
strans | response_xml_p2020_07 | table | sides_owner
strans | response_xml_p2020_08 | table | sides_owner
strans | response_xml_p2020_09 | table | sides_owner
strans | response_xml_p2020_10 | table | sides_owner
strans | response_xml_p2020_11 | table | sides_owner
strans | response_xml_p2020_12 | table | sides_owner
strans | response_xml_p2021_01 | table | sides_owner
strans | response_xml_p2021_02 | table | sides_owner
strans | response_xml_p2021_03 | table | sides_owner
strans | response_xml_p2021_04 | table | sides_owner
strans | response_xml_p2021_05 | table | sides_owner
strans | response_xml_p2021_06 | table | sides_owner
strans | response_xml_p2021_07 | table | sides_owner
strans | response_xml_p2021_08 | table | sides_owner
strans | response_xml_p2021_09 | table | sides_owner
strans | response_xml_p2021_10 | table | sides_owner
strans | response_xml_p2021_11 | table | sides_owner
strans | response_xml_p2021_12 | table | sides_owner
strans | response_xml_p2022_01 | table | stransuser
strans | response_xml_p2022_02 | table | stransuser
strans | response_xml_p2022_03 | table | stransuser
strans | response_xml_p2022_04 | table | stransuser
strans | response_xml_p2022_05 | table | stransuser
strans | response_xml_p2022_06 | table | stransuser
strans | response_xml_p2022_07 | table | stransuser
strans | response_xml_p2022_08 | table | stransuser
strans | response_xml_p2022_09 | table | stransuser
strans | response_xml_p2022_10 | table | stransuser
strans | response_xml_p2022_11 | table | stransuser
strans | response_xml_p2022_12 | table | stransuser
strans | response_xml_p2023_01 | table | sides_owner
strans | response_xml_p2023_02 | table | sides_owner
strans | response_xml_p2023_03 | table | sides_owner
strans | response_xml_p2023_04 | table | sides_owner
strans | response_xml_p2023_05 | table | sides_owner
strans | response_xml_p2023_06 | table | sides_owner
strans | response_xml_p2023_07 | table | sides_owner
strans | response_xml_p2023_08 | table | sides_owner
strans | response_xml_p2023_09 | table | sides_owner
strans | response_xml_p2023_10 | table | sides_owner
strans | response_xml_p2023_11 | table | sides_owner
strans | response_xml_p2023_12 | table | sides_owner
strans | response_xml_ple2018_09 | table | sides_owner
