Re: pg_restore issues with intarray

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