Re: Permissions on large objects - db backup and restore

A 9.2->9.2 dump and restore certainly should preserve large object ownership (and permissions, if you've set any). In a quick check I do see "ALTER LARGE OBJECT nnn OWNER TO ..." commands in pg_dump's output for such a case. Are you sure this is really the "same" issue? Are you doing something strange like using pg_dump's --no-owner option? Did you get any errors while doing the pg_dump or pg_restore? (Trying to run the restore as non-superuser would mean the ALTER OWNER commands would fail, but you should have gotten plenty of bleats about that.) regards, tom lane

We used this to backup on server1 (9.2.2):

pg_dump --format=c --oids dbname

And we restored on server2 (9.2.3) with:

 pg_restore -v -O -d dbname

The application user/role is the same name as the dbname but has more limited permissions than the superuser/role $PGUSER used when running pg_dump and pg_restore.

How can I check if ALTER LARGE OBJECT is specified in my backup?  Do I need to change the --format option?

I'm not positive it was the same issue as when I upgraded from 8.4, but I did note that my db had a different pg_largeobject_metadata.lomowner value before and after I ran that DO script to alter each and the problem with reading a large object in my code went away.

Thanks, David