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

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