Comment on extension issues with pg_dump after upgrading to 9.1.2

Timothy Garnett wrote:

We recently upgraded to 9.1.2 from 9.0.3 and ran into some issues with

the process we've been using to

dump and restore databases. We typically use a super user (but not

the postgres user) to dump and

restore databases, but in moving the 9.1.2 we've run into trouble with

pg_dump outputing comment on

extension lines that cause permission issues on pg_restore.

Something like:
CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
COMMENT ON EXTENSION plpgsql IS '....';

The create is fine because the extension is already in the template

db, but the comment causes

problems because the extension is owned by the postgres user, but

we're trying to use a different user

(and apparently even super users can not comment on extensions owned

by other users). This happens

even if the comment isn't changing. There also appears to be no way

to change the ownership of an

extension. We have the same issue with other extensions. So far

we've worked around it by setting

the comment to NULL in the template and all other databases (in which

case pg_dump doesn't output the

comment line), but I was wondering if there was an easy way to keep

the comments.

I don't understand what you are doing.

If you dump a 9.0 database, how can there be a CREATE EXTENSION command
in the dump? Extensions were added in 9.1.

Next, I tried to run
COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
in a 9.1 database as a superuser different from "postgres",
and it worked fine as expected.

Maybe you can describe in more detail how you created the dump,
how you created the database into which the dump is loaded,
and the exact error messages you get when restoring the dump.

Yours,
Laurenz Albe