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

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