On 3/11/20 2:46 PM, Kevin Brannen wrote:
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
Which version of pg_dump 9.6.5 or 12.2?
More below.
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"
What is in public.check_data_ip(integer,integer,character varying)?
Is it a trigger function on subscribers?
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.
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
