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@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 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@xxxxxxxxxxx
