Re: Permissions on large objects - db backup and restore

On 4/3/2013 3:14 PM, Tom Lane wrote:
> 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