Re: pg_restore issues with intarray - Mailing list pgsql-general

On 9/5/19 2:57 PM, Kevin Brannen wrote:
> I think I need some help to understand what’s going here because I can’t 
> figure it out and google isn’t helping…
> 
> This is for Pg 9.6.5. (Yes, we are upgrading to 12.x in a few months, or 
> so the plan is.) Pg code came from the community and we compiled it with 
> no changes. This is on Centos 6.7, though I think the OS doesn’t matter.
> 
> I’ve found that when we do a pg_restore, that sometimes we get “errors”. 
> I quote that because it turns out they’re really only warnings we can 
> ignore, but when you check the return code ($?) after pg_restore, you 
> get a non-zero value.
> 
> We’re calling pg_restore like:
> 
> $PGPATH/pg_restore -jobs=$NCPU --dbname=x .
> 
> FWIW, the backup was created with:
> 
> $PGPATH/pg_dump --clean --create --format=d --jobs=$NCPU --file=$EXP 
> --dbname=x

The options you are adding for --clean, --create only have meaning for 
plain text dumps. If you want those actions to occur on the restore 
then add them to the pg_restore line. Though if you are going to create 
a new database it will inherit objects from template1(as you found 
below), assuming you have not set WITH TEMPLATE to something else.

> 
> The issue I’m seeing is:
> 
> pg_restore: [archiver (db)] Error while PROCESSING TOC:
> 
> pg_restore: [archiver (db)] Error from TOC entry 3605; 2753 18784 
> OPERATOR FAMILY gin__int_ops postgres
> 
> pg_restore: [archiver (db)] could not execute query: ERROR:  operator 
> family "gin__int_ops" for access method "gin" already exists
> 
>      Command was: CREATE OPERATOR FAMILY gin__int_ops USING gin;
> 
> pg_restore: [archiver (db)] Error from TOC entry 3606; 2753 18806 
> OPERATOR FAMILY gist__int_ops postgres
> 
> pg_restore: [archiver (db)] could not execute query: ERROR:  operator 
> family "gist__int_ops" for access method "gist" already exists
> 
>      Command was: CREATE OPERATOR FAMILY gist__int_ops USING gist;
> 
> pg_restore: [archiver (db)] Error from TOC entry 3607; 2753 18829 
> OPERATOR FAMILY gist__intbig_ops postgres
> 
> pg_restore: [archiver (db)] could not execute query: ERROR:  operator 
> family "gist__intbig_ops" for access method "gist" already exists
> 
>      Command was: CREATE OPERATOR FAMILY gist__intbig_ops USING gist;
> 
> Those operators come from the *intarray* extension. Looking into the 
> toc.dat, I can see entries like:
> 
> ^@3079^@^E^@^@^@16441^@^H^@^@^@intarray^@   
> ^@^@^@EXTENSION^@^B^@^@^@^@<^@^@^@CREATE EXTENSION IF NOT EXISTS 
> intarray WITH SCHEMA common;
> 
>
^A^A^@^@^@^@^F^@^@^@public^A^A^@^@^@^@^G^@^@^@nmsroot^@^E^@^@^@false^@^B^@^@^@15^@^A^@^@^@3^A^A^@^@^@^@^@^@^@^@^@^U^N^@^@^@^@^@^@^@^@^D^@^@^@2753^@^E^@^@^@18784^@^L^@^@^@gin__int_ops^@^O^@^@^@OPERATOR

> FAMILY^@^B^@^@^@^@/^@^@^@CREATE OPERATOR FAMILY gin__int_ops USING gin;
> 
> …same sort of thing for the other 2…
> 
> Those seem reasonable to me. It seems the problem is with the CREATE 
> OPERATOR as there is not “if not exists” for it. Considering we created 
> a new DB for the restore and went with --create to pg_restore, there 
> should be nothing in the DB to create a conflict, yet somehow it already 
> exists!
> 
> Interestingly, this doesn’t happen all the time. It seems that once we 
> can get a restore in, that it never happens again in any subsequent 
> restores. My theory for this is that the databases (or really installs) 
> where this happens started are an earlier version that was pg_upgrade’d 
> (probably from 9.5 or even from 9.3). But I can take the shell script 
> that runs this, turn off checking for the return code from pg_restore 
> and we’ve found no issues with the DB (hence I can treat them like 
> warnings). Of course, the downside there is that if I always ignore the 
> return code from pg_restore, how do I catch real errors? 😊
> 
> If it matters, here’s the version we’re dealing with for intarray:
> 
> # \dx
> 
>                                            List of installed extensions
> 
>          Name        | Version |   Schema   |                            
> Description
> 
> --------------------+---------+------------+--------------------------------------------------------------------
> 
> intarray           | 1.2     | common     | functions, operators, and 
> index support for 1-D arrays of integers
> 
> If there is a logical reason for this, I’d appreciate an explanation so 
> I can deal with it properly. If more research is needed, I can do that 
> and pointers as to what to look for would be great. I have a VM and a 
> backup I can restore & test as many times as needed.
> 
> Last minute discovery and thought. It just occurred to me to look in 
> template1 and intarray is in there. Is it possible that gives me a 
> pre-existing extension & operators and therefore the source of the 
> conflict? If so, is the solution as simple as deleting that extension 
> from template1? In fact, should I delete all the extensions from 
> template1 that it shows as being in the public schema?
> 
> Thanks!
> 
> 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@aklaver.com