Search Postgresql Archives

Re: pg_restore issues with intarray

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxx





[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux