From: | Jerry Sievers <gsievers19(at)comcast(dot)net> |
---|---|
To: | Kevin Brannen <KBrannen(at)efji(dot)com> |
Cc: | "pgsql-general\(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: pg_restore issues with intarray |
Date: | 2019-09-06 00:37:27 |
Message-ID: | 87ftla1cqg.fsf@jsievers.enova.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Kevin Brannen <KBrannen(at)efji(dot)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(dot)klaver(at)aklaver(dot)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(dot)consulting(at)comcast(dot)net
From | Date | Subject | |
---|---|---|---|
Next Message | Stacey Haysler | 2019-09-06 06:20:47 | Seeking New Members for the Community Code of Conduct Committee |
Previous Message | Adrian Klaver | 2019-09-06 00:35:11 | Re: pg_restore issues with intarray |