Kevin Brannen <KBrannen@xxxxxxxx> 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@xxxxxxxxxxx> >> >>> >> >>> 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@xxxxxxxxxxx
