Argh. I just realized that I gave this the wrong subject-- it should've been
"Can't pg_restore large objects"
> Digging a bit, I've discovered this:
> 1) usesysid 1 owns the database in the old server, but all
> the tables are
> owned by 'qvowner' (and others).
> 2) qvowner does not have dba privs
>
> My theory is that I'm getting this last message (not
> authorized to create
> temp tables) because the permissions have been tightened down.
This test case works just fine with 7.2.1, but fails with my 'checked out
today' code.
Here is my shell script test case:
# this script assumes that the current user can connect without
# being prompted for a password
createuser -A -D lotest1
createuser -A -D lotest2
createdb lotest1
TESTF=/tmp/pgtest$$
cat >> $TESTF <<EOF
This is just a simple little file
EOF
#I don't think that this table is absolutely necessary for this test
psql lotest1 lotest1 -c "create table a (bah int);"
#now create the troublemaker table
psql lotest1 lotest2 -c "create table z (bah int);"
psql lotest1 lotest1 -c "\lo_import $TESTF";
pg_dump --blobs --format=c --quotes --oids --compress=5 lotest1 >
/tmp/lotest1.dump
psql template1 -c "drop database lotest1;"
createdb lotest1
pg_restore -d lotest1 < /tmp/lotest1.dump
## cleanup
rm $TESTF
rm /tmp/lotest1.dump
dropdb lotest1
dropuser lotest1
dropuser lotest2
########## End of test case
If that is according to design, then migration could be very painful going
to 7.3 because some databases could have tables owned by several different
users.
>
> I believe that I can safely change the ownership of the
> database in the old
> server to qvowner, right? And run the pg_dump and pg_restore again? Or
> should pg_restore connect as the superuser and just change ownership
> afterwards?
>
> -ron
>
>
>
> > (I've thought several times that we should clean up pg_dump and
> > pg_restore so that they report the failed query and backend
> message in
> > *all* cases; right now they're pretty haphazard about it.)
> >
> > regards, tom lane
> >
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
