Re: pg_restore restores out of order

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'sfailing 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
changea 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
emptyschema 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-2216: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
isdefinitely 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
defaultplain 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
creatingthe tables in that order is fine, but there is a FK constraint in subscribers pointing to su_profiles, so that
orderdoes worry me a little, except all the FK stuff is after the data load so I really should be fine. The data looks
likeit'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
therewere some changes for 12.1 with parallel restores, but I can't tell if that's affecting me or not. I know that the
publicschema & search_path changed for dump & restore in 10.3, but this doesn't look like that's a problem here. I've
rereadthe 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
confidentialinformation. If you are not the intended recipient, or a person responsible for delivering it to the
intendedrecipient, you are hereby notified that any disclosure, distribution, review, copy or use of any of the
informationcontained 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
readingthem or saving them to disk. Thank you.
 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com