I'm working thru our system to upgrade from 9.6.5 to 12.2. One of the last
things I'm verifying is backup/restore and it's failing for no reason I can
figure out. So I'm looking for pointers on this.
If it matters, the code is from the version 12.2 from the Pg site, RPMs for
Centos 6 (.10).
The backup is made like:
# $PGPATH/pg_dump --format=d --jobs=2 --file=$exp --dbname=nms --schema=public
As far as I can tell, it worked well as the result is about the right size,
exit code was at least 0. I then go change a couple of things so I know if the
restore works or not (i.e. they should disappear).
Restore looks something like:
# cd $exp
# echo "
ALTER SCHEMA public RENAME TO savepublic;
CREATE SCHEMA public AUTHORIZATION nmsroot;
" | $PGPATH/psql -d nms
# /usr/pgsql-12/bin/pg_restore --jobs=2 --dbname=nms --schema=public .
So we move the current schema to the side just in case something goes wrong and
we need to move it back, create an empty schema for it, then restore into that
schema. Then it goes bad...
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 6356; 0 23653 TABLE DATA subscribers nmsroot
pg_restore: error: COPY failed for table "subscribers": ERROR: relation
"su_profiles" does not exist
LINE 1: SELECT srvc_data FROM su_profiles WHERE su_profile...
^
QUERY: SELECT srvc_data FROM su_profiles WHERE su_profile_pk =
p_profile_fk
CONTEXT: PL/pgSQL function public.check_data_ip(integer,integer,character
varying) line 7 at SQL statement
COPY subscribers, line 1: "61 1002 \N SU_1002 t \N 4
\N 1 250 0 2015-06-22 16:56:27.79333+00 nmsmgr \N
\N \N"
And it goes further downhill with missing FK's, etc in other tables later
because of the missing data. The exit code is definitely not 0.
I've tried removing the "--jobs=2" so it'll go single threaded -- it still
fails. I also did a new dump with the default plain text dump, and the restore
with that also failed in the same way. Checking out the backup itself, I get:
# pg_restore -Fd . --list | egrep 'su_profile|subscriber' > k1
254; 1259 23653 TABLE public subscribers nmsroot
335; 1259 24222 TABLE public su_profiles nmsroot
5442; 2604 18650 DEFAULT public su_profiles su_profile_pk nmsroot
5303; 2604 18651 DEFAULT public subscribers subscriber_pk nmsroot
6437; 0 24222 TABLE DATA public su_profiles nmsroot
6356; 0 23653 TABLE DATA public subscribers nmsroot
....... constraints, index, triggers, FK -- down here
I've removed FUNCTIONS, VIEWS, & SEQ objects from the list. That list is in
order that is in the file. I suppose creating the tables in that order is fine,
but there is a FK constraint in subscribers pointing to su_profiles, so that
order does worry me a little, except all the FK stuff is after the data load so
I really should be fine. The data looks like it'll be in the right order (at
least for --jobs=1, yet that failed too).
I don't see anything extra in the Pg error log either.
I don't think I'm running into any issues with "other DB objects being
missing". Looking at the release notes, I see there were some changes for 12.1
with parallel restores, but I can't tell if that's affecting me or not. I know
that the public schema & search_path changed for dump & restore in 10.3, but
this doesn't look like that's a problem here. I've reread the doc pages on
pg_dump and pg_restore and don't see anything that look like it'd be bad for me.
Can anyone see anything that looks wrong? Suggest something else to try or look
at?
Kevin
This e-mail transmission, and any documents, files or previous e-mail messages
attached to it, may contain confidential information. If you are not the
intended recipient, or a person responsible for delivering it to the intended
recipient, you are hereby notified that any disclosure, distribution, review,
copy or use of any of the information contained in or attached to this message
is STRICTLY PROHIBITED. If you have received this transmission in error, please
immediately notify us by reply e-mail, and destroy the original transmission
and its attachments without reading them or saving them to disk. Thank you.
