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

> 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.