Thread: pg_restore issues with intarray
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 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
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
> From: Adrian Klaver <adrian.klaver@aklaver.com> > > 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. > > > > 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 occuron the restore then add them to the pg_restore line. Though if you are going to create a new database it will inheritobjects from template1(as you found below), assuming you have not set WITH TEMPLATE to something else. > Good point that I'm not doing plain text dumps. Are you saying that my problem is that I need "--clean" on the pg_restore? I can try that. The fact that this only happens on a few DBs and not all still mystifies me. See below on the template... > > > > 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; > > ... > > > > 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? I've had time since I wrote the post to drop the intarray extension from template1 and try the restore. Sadly, same errors happen, so it's not template1 that's doing this to me, or at least not in a way I understand. 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, youare hereby notified that any disclosure, distribution, review, copy or use of any of the information contained in or attachedto this message is STRICTLY PROHIBITED. If you have received this transmission in error, please immediately notifyus by reply e-mail, and destroy the original transmission and its attachments without reading them or saving them todisk. Thank you.
On 9/5/19 4:06 PM, Kevin Brannen wrote: >> From: Adrian Klaver <adrian.klaver@aklaver.com> >> >> 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. >>> >>> 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 tooccur on the restore then add them to the pg_restore line. Though if you are going to create a new database it will inheritobjects from template1(as you found below), assuming you have not set WITH TEMPLATE to something else. >> > > Good point that I'm not doing plain text dumps. > > Are you saying that my problem is that I need "--clean" on the pg_restore? No, just that if you where expecting the clean to happen on the restore you would be disappointed. > I can try that. The fact that this only happens on a few DBs and not all still > mystifies me. See below on the template.. My guess is you where restoring into a database with preexisting objects because neither create or clean was being done. -- Adrian Klaver adrian.klaver@aklaver.com
On 9/5/19 4:24 PM, Adrian Klaver wrote: > On 9/5/19 4:06 PM, Kevin Brannen wrote: >>> From: Adrian Klaver <adrian.klaver@aklaver.com> >>> >>> 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. >>>> >>>> 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. >>> >> >> Good point that I'm not doing plain text dumps. >> >> Are you saying that my problem is that I need "--clean" on the >> pg_restore? > > No, just that if you where expecting the clean to happen on the restore > you would be disappointed. > >> I can try that. The fact that this only happens on a few DBs and not >> all still >> mystifies me. See below on the template.. > > My guess is you where restoring into a database with preexisting objects > because neither create or clean was being done. Just be aware that --clean & --create drops the existing database before creating a new one. > > > -- Adrian Klaver adrian.klaver@aklaver.com
> On 9/5/19 4:24 PM, Adrian Klaver wrote: > > On 9/5/19 4:06 PM, Kevin Brannen wrote: > >>> From: Adrian Klaver <adrian.klaver@aklaver.com> > >>> > >>> 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. > >>>> > >>>> 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. > >>> > >> > >> Good point that I'm not doing plain text dumps. > >> > >> Are you saying that my problem is that I need "--clean" on the > >> pg_restore? Not the issue, that made various things worse. :) > No, just that if you were expecting the clean to happen on the > restore you would be disappointed. To be crystal clear, on restore I do this from a bash script: # move old to the side in case we need this on failure $PGPATH/psql -d template1 -c "DROP DATABASE IF EXISTS save$db;" $PGPATH/psql -d template1 -c "ALTER DATABASE $db RENAME TO save$db;" # restore $PGPATH/createdb -e -O $dbowner -T template0 $db $PGPATH/pg_restore $VERBOSE --jobs=$NCPU --dbname=$db . So by using template0, I'm expecting nothing to be there and the restore to put everything in there I need to get back to the point where the backup/dump happened. This is why I'm surprised I'm getting this error. It feels like the restore is adding the intarray extension, which does a CREATE OPERATOR FAMILY on its own, then later the restore does CREATE OPERATOR FAMILY on again causing the problem. Yet this doesn't happen on most of our databases, just a few. It's maddening to me. > >> I can try that. The fact that this only happens on a few DBs and not > >> all still mystifies me. See below on the template.. > > My guess is you where restoring into a database with preexisting > objects because neither create or clean was being done. Shouldn't be happening with that createdb command. Hmm, I wonder what I'd see if I put a "psql" command with "\dx" after the createdb and before the restore... Nope, the only extension is plpgsql, so the problem is coming from the restore. Maybe I gave a bad option to pg_dump, but pg_restore seems to be the issue. It really makes me want to modify the toc.dat file and hack out those CREATE OPERATOR FAMILY lines and see what happens. K. 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, youare hereby notified that any disclosure, distribution, review, copy or use of any of the information contained in or attachedto this message is STRICTLY PROHIBITED. If you have received this transmission in error, please immediately notifyus by reply e-mail, and destroy the original transmission and its attachments without reading them or saving them todisk. Thank you.
On 9/5/19 5:05 PM, Kevin Brannen wrote: >> On 9/5/19 4:24 PM, Adrian Klaver wrote: >>> On 9/5/19 4:06 PM, Kevin Brannen wrote: >>>>> From: Adrian Klaver <adrian.klaver@aklaver.com> >>>>> >>>>> 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. >>>>>> >>>>>> 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. >>>>> >>>> >>>> Good point that I'm not doing plain text dumps. >>>> >>>> Are you saying that my problem is that I need "--clean" on the >>>> pg_restore? > > Not the issue, that made various things worse. :) > >> No, just that if you were expecting the clean to happen on the >> restore you would be disappointed. > > To be crystal clear, on restore I do this from a bash script: > > # move old to the side in case we need this on failure > $PGPATH/psql -d template1 -c "DROP DATABASE IF EXISTS save$db;" > $PGPATH/psql -d template1 -c "ALTER DATABASE $db RENAME TO save$db;" > # restore > $PGPATH/createdb -e -O $dbowner -T template0 $db > $PGPATH/pg_restore $VERBOSE --jobs=$NCPU --dbname=$db . > > So by using template0, I'm expecting nothing to be there and the restore > to put everything in there I need to get back to the point where the > backup/dump happened. This is why I'm surprised I'm getting this error. > > It feels like the restore is adding the intarray extension, which does a > CREATE OPERATOR FAMILY on its own, then later the restore does CREATE OPERATOR > FAMILY on again causing the problem. Yet this doesn't happen on most of our > databases, just a few. It's maddening to me. > >>>> I can try that. The fact that this only happens on a few DBs and not >>>> all still mystifies me. See below on the template.. >> >> My guess is you where restoring into a database with preexisting >> objects because neither create or clean was being done. > > Shouldn't be happening with that createdb command. Hmm, I wonder what > I'd see if I put a "psql" command with "\dx" after the createdb and before > the restore... What does \dx show in the database you taking the dump from? > > Nope, the only extension is plpgsql, so the problem is coming from the > restore. Maybe I gave a bad option to pg_dump, but pg_restore seems to be > the issue. It really makes me want to modify the toc.dat file and hack > out those CREATE OPERATOR FAMILY lines and see what happens. What if you do a restore to a file only the schema e.g.: pg_restore -s -f some_file.sql This will create a plain text version of only the schema objects in some_file.sql instead of restoring to the database. It might help shed some light. > > K. -- Adrian Klaver adrian.klaver@aklaver.com
Kevin Brannen <KBrannen@efji.com> writes: >> On 9/5/19 4:24 PM, Adrian Klaver wrote: >> > On 9/5/19 4:06 PM, Kevin Brannen wrote: >> >>> From: Adrian Klaver <adrian.klaver@aklaver.com> >> >>> >> >>> 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. >> >>>> >> >>>> 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. >> >>> >> >> >> >> Good point that I'm not doing plain text dumps. >> >> >> >> Are you saying that my problem is that I need "--clean" on the >> >> pg_restore? > > Not the issue, that made various things worse. :) > >> No, just that if you were expecting the clean to happen on the >> restore you would be disappointed. > > To be crystal clear, on restore I do this from a bash script: > > # move old to the side in case we need this on failure > $PGPATH/psql -d template1 -c "DROP DATABASE IF EXISTS save$db;" > $PGPATH/psql -d template1 -c "ALTER DATABASE $db RENAME TO save$db;" > # restore > $PGPATH/createdb -e -O $dbowner -T template0 $db > $PGPATH/pg_restore $VERBOSE --jobs=$NCPU --dbname=$db . > > So by using template0, I'm expecting nothing to be there and the restore > to put everything in there I need to get back to the point where the > backup/dump happened. This is why I'm surprised I'm getting this error. > > It feels like the restore is adding the intarray extension, which does a > CREATE OPERATOR FAMILY on its own, then later the restore does CREATE OPERATOR > FAMILY on again causing the problem. Yet this doesn't happen on most of our > databases, just a few. It's maddening to me. > I've seen this sort of problem before. It was due to some legacy DBs where I work having a few missing extension membership registrations. pg_dump wants to include any such things in the output which may run afoul of same having been already created by the extension load. HTH <snip> -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consulting@comcast.net
>From: Jerry Sievers <gsievers19@comcast.net> > >>Kevin Brannen <KBrannen@efji.com> writes: >> >> It feels like the restore is adding the intarray extension, which does >> a CREATE OPERATOR FAMILY on its own, then later the restore does >> CREATE OPERATOR FAMILY on again causing the problem. Yet this doesn't >> happen on most of our databases, just a few. It's maddening to me. >> > >I've seen this sort of problem before. > >It was due to some legacy DBs where I work having a few missing extension membership registrations. > >pg_dump wants to include any such things in the output which may run afoul of same having been already created by theextension load. Ugh! That would explain why it's only on some installs and only on the "older" ones. Thanks for the info! Did you ever find any work around? Or is it just a matter of recognizing what's going on, ignore those "warnings", and moving to a later version to get past this so it doesn't happen again? 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, youare hereby notified that any disclosure, distribution, review, copy or use of any of the information contained in or attachedto this message is STRICTLY PROHIBITED. If you have received this transmission in error, please immediately notifyus by reply e-mail, and destroy the original transmission and its attachments without reading them or saving them todisk. Thank you.
>From: Adrian Klaver <adrian.klaver@aklaver.com> >On 9/5/19 5:05 PM, Kevin Brannen wrote: >> >> It feels like the restore is adding the intarray extension, which does >> a CREATE OPERATOR FAMILY on its own, then later the restore does >> CREATE OPERATOR FAMILY on again causing the problem. Yet this doesn't >> happen on most of our databases, just a few. It's maddening to me. >> >What does \dx show in the database you taking the dump from? Sadly, I don't have access to that system. >What if you do a restore to a file only the schema e.g.: > >pg_restore -s -f some_file.sql > >This will create a plain text version of only the schema objects in some_file.sql instead of restoring to the database.It might help shed some light. No CREATE EXTENSION or CREATE OPERATOR FAMILY statements. Jerry's post indicates this is something that just happens with some older versions and it seems I got unlucky. I do have a work around (ignore) but I'd rather be proactive in knowing I'm ignoring something I should be and not ignoring meaningful errors. Thanks for the help Adrian, I really appreciate it! 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, youare hereby notified that any disclosure, distribution, review, copy or use of any of the information contained in or attachedto this message is STRICTLY PROHIBITED. If you have received this transmission in error, please immediately notifyus by reply e-mail, and destroy the original transmission and its attachments without reading them or saving them todisk. Thank you.
Kevin Brannen <KBrannen@efji.com> writes:
>>From: Jerry Sievers <gsievers19@comcast.net>
>>
>>>Kevin Brannen <KBrannen@efji.com> writes:
>>>
>>> It feels like the restore is adding the intarray extension, which does
>>> a CREATE OPERATOR FAMILY on its own, then later the restore does
>>> CREATE OPERATOR FAMILY on again causing the problem. Yet this doesn't
>>> happen on most of our databases, just a few. It's maddening to me.
>>>
>>
>>I've seen this sort of problem before.
>>
>>It was due to some legacy DBs where I work having a few missing extension membership registrations.
>>
>>pg_dump wants to include any such things in the output which may run afoul of same having been already created by
theextension load.
>
> Ugh! That would explain why it's only on some installs and only on the
> "older" ones. Thanks for the info!
>
> Did you ever find any work around? Or is it just a matter of recognizing
> what's going on, ignore those "warnings", and moving to a later version to
> get past this so it doesn't happen again?
Try running \dx+ for intarray on one of your deviant systems. You may
find the item pg_dump is trying to be explicit about *missing* from the
extension member list.
In such a case, see the ALTER EXTENSION ADD... which can be run manually
to register whatever is missing.
You will do this on the system that is *origin* for the pg_dump.
This is what we've done.
YMMV
postgres# \dx+ intarray
Objects in extension "intarray"
Object description
------------------------------------------------------------------------------------------------------
function boolop(integer[],query_int)
function bqarr_in(cstring)
function bqarr_out(query_int)
function ginint4_consistent(internal,smallint,integer[],integer,internal,internal,internal,internal)
function ginint4_queryextract(integer[],internal,smallint,internal,internal,internal,internal)
function g_intbig_compress(internal)
function g_intbig_consistent(internal,integer[],smallint,oid,internal)
function g_intbig_decompress(internal)
function g_intbig_penalty(internal,internal,internal)
function g_intbig_picksplit(internal,internal)
function g_intbig_same(intbig_gkey,intbig_gkey,internal)
function g_intbig_union(internal,internal)
function g_int_compress(internal)
function g_int_consistent(internal,integer[],smallint,oid,internal)
function g_int_decompress(internal)
function g_int_penalty(internal,internal,internal)
function g_int_picksplit(internal,internal)
function g_int_same(integer[],integer[],internal)
function g_int_union(internal,internal)
function icount(integer[])
function idx(integer[],integer)
function intarray_del_elem(integer[],integer)
function intarray_push_array(integer[],integer[])
function intarray_push_elem(integer[],integer)
function _intbig_in(cstring)
function _intbig_out(intbig_gkey)
function _int_contained(integer[],integer[])
function _int_contained_joinsel(internal,oid,internal,smallint,internal)
function _int_contained_sel(internal,oid,internal,integer)
function _int_contains(integer[],integer[])
function _int_contains_joinsel(internal,oid,internal,smallint,internal)
function _int_contains_sel(internal,oid,internal,integer)
function _int_different(integer[],integer[])
function _int_inter(integer[],integer[])
function _int_matchsel(internal,oid,internal,integer)
function _int_overlap(integer[],integer[])
function _int_overlap_joinsel(internal,oid,internal,smallint,internal)
function _int_overlap_sel(internal,oid,internal,integer)
function _int_same(integer[],integer[])
function intset(integer)
function intset_subtract(integer[],integer[])
function intset_union_elem(integer[],integer)
function _int_union(integer[],integer[])
function querytree(query_int)
function rboolop(query_int,integer[])
function sort_asc(integer[])
function sort_desc(integer[])
function sort(integer[])
function sort(integer[],text)
function subarray(integer[],integer)
function subarray(integer[],integer,integer)
function uniq(integer[])
operator class gin__int_ops for access method gin
operator class gist__intbig_ops for access method gist
operator class gist__int_ops for access method gist
operator family gin__int_ops for access method gin
operator family gist__intbig_ops for access method gist
operator family gist__int_ops for access method gist
operator ~(integer[],integer[])
operator <@(integer[],integer[])
operator |(integer[],integer)
operator |(integer[],integer[])
operator -(integer[],integer)
operator -(integer[],integer[])
operator @>(integer[],integer[])
operator @(integer[],integer[])
operator &(integer[],integer[])
operator &&(integer[],integer[])
operator #(integer[],integer)
operator +(integer[],integer)
operator +(integer[],integer[])
operator @@(integer[],query_int)
operator #(NONE,integer[])
operator ~~(query_int,integer[])
type intbig_gkey
type query_int
(76 rows)
meta_a:postgres# \h alter extension
Command: ALTER EXTENSION
Description: change the definition of an extension
Syntax:
ALTER EXTENSION name UPDATE [ TO new_version ]
ALTER EXTENSION name SET SCHEMA new_schema
ALTER EXTENSION name ADD member_object
ALTER EXTENSION name DROP member_object
where member_object is:
ACCESS METHOD object_name |
AGGREGATE aggregate_name ( aggregate_signature ) |
CAST (source_type AS target_type) |
COLLATION object_name |
CONVERSION object_name |
DOMAIN object_name |
EVENT TRIGGER object_name |
FOREIGN DATA WRAPPER object_name |
FOREIGN TABLE object_name |
FUNCTION function_name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] |
MATERIALIZED VIEW object_name |
OPERATOR operator_name (left_type, right_type) |
OPERATOR CLASS object_name USING index_method |
OPERATOR FAMILY object_name USING index_method |
[ PROCEDURAL ] LANGUAGE object_name |
PROCEDURE procedure_name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] |
ROUTINE routine_name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] |
SCHEMA object_name |
SEQUENCE object_name |
SERVER object_name |
TABLE object_name |
TEXT SEARCH CONFIGURATION object_name |
TEXT SEARCH DICTIONARY object_name |
TEXT SEARCH PARSER object_name |
TEXT SEARCH TEMPLATE object_name |
TRANSFORM FOR type_name LANGUAGE lang_name |
TYPE object_name |
VIEW object_name
and aggregate_signature is:
* |
[ argmode ] [ argname ] argtype [ , ... ] |
[ [ argmode ] [ argname ] argtype [ , ... ] ] ORDER BY [ argmode ] [ argname ] argtype [ , ... ]
URL: https://www.postgresql.org/docs/12/sql-alterextension.html
>
> 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.
--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
>From: Jerry Sievers <gsievers19@comcast.net> > > >Try running \dx+ for intarray on one of your deviant systems. You may find the item pg_dump is trying to be explicit about*missing* from the extension member list. > >In such a case, see the ALTER EXTENSION ADD... which can be run manually to register whatever is missing. > >You will do this on the system that is *origin* for the pg_dump. > >This is what we've done. Hmm, I was really hoping for something on the restore side instead of the dump side as that makes it harder, but we don't always get what we want. :) Still, at least was have something to pursue and this is now documented on the mailing list. 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, youare hereby notified that any disclosure, distribution, review, copy or use of any of the information contained in or attachedto this message is STRICTLY PROHIBITED. If you have received this transmission in error, please immediately notifyus by reply e-mail, and destroy the original transmission and its attachments without reading them or saving them todisk. Thank you.
On 9/6/19 8:45 AM, Kevin Brannen wrote:
>> From: Adrian Klaver <adrian.klaver@aklaver.com>
>> On 9/5/19 5:05 PM, Kevin Brannen wrote:
>>>
>>> It feels like the restore is adding the intarray extension, which does
>>> a CREATE OPERATOR FAMILY on its own, then later the restore does
>>> CREATE OPERATOR FAMILY on again causing the problem. Yet this doesn't
>>> happen on most of our databases, just a few. It's maddening to me.
>>>
>> What does \dx show in the database you taking the dump from?
>
> Sadly, I don't have access to that system.
>
>> What if you do a restore to a file only the schema e.g.:
>>
>> pg_restore -s -f some_file.sql
>>
>> This will create a plain text version of only the schema objects in some_file.sql instead of restoring to the
database.It might help shed some light.
>
> No CREATE EXTENSION or CREATE OPERATOR FAMILY statements.
I do not see that as possible. Something ran CREATE OPERATOR FAMILY:
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;
So either someone opened up template0 and loaded the extension into it
or the dump file has the CREATE OPERATOR FAMILY in it. Another thought
search on intarray.
>
> Jerry's post indicates this is something that just happens with some older
> versions and it seems I got unlucky. I do have a work around (ignore) but
> I'd rather be proactive in knowing I'm ignoring something I should be and
> not ignoring meaningful errors.
We are not dealing with magic here, there is some mechanism at work.
Dollars to donuts there is an unpackaged version of the extension in one
or more of the source databases. See below for more information on this:
https://www.postgresql.org/docs/11/extend-extensions.html
38.16.5. Extension Updates
You will need to look at the source databases in situ.
>
> Thanks for the help Adrian, I really appreciate it!
--
Adrian Klaver
adrian.klaver@aklaver.com