Re: pg_restore issues with intarray
От | Adrian Klaver |
---|---|
Тема | Re: pg_restore issues with intarray |
Дата | |
Msg-id | 0e059013-15d7-b586-db9f-5ccd653b0689@aklaver.com обсуждение исходный текст |
Ответ на | RE: pg_restore issues with intarray (Kevin Brannen <KBrannen@efji.com>) |
Ответы |
RE: pg_restore issues with intarray
|
Список | 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
В списке pgsql-general по дате отправления: