In past PG upgrades, we've done a pg_dump on the current version, then a
pg_restore on the new version. But during the 8.4 to 9.2 upgrade (on
Linux x64), we ran into issues with the permissions associated with the
large objects after the restore.
Is this something new or were we just "lucky" before?
Our postmaster runs many databases, with each database owned by the PG
admin, but we normally just used a set of GRANT statements to provide
appropriate access control to the application user. In our each, each
database has it's own application user which accesses the DB for a web
app (and for convenience, the DBNAME and DBUSER are the same name).
Our pg_dump command is basically: pg_dump --format=c --oids DBNAME
Our pg_restore is basically: pg_restore -v -O -d DBNAME
Should we be doing this differently now as we never found an issue
before this somewhat significant update from 8.4 to 9.2?
We resolved the issue from a posting we saw online that basically
suggested this resolution after the restore with psql:
do $$
declare r record;
begin
for r in select loid from pg_catalog.pg_largeobject loop
execute 'ALTER LARGE OBJECT ' || r.loid || ' OWNER TO DBUSER';
end loop;
end$$;
CLOSE ALL;
Thanks for any good pointers or tips on this.
David
