Re: dump + restore didn't include schemas

"Dan Langille" <dan@langille.org> writes:
>>> I upgraded two servers today from 7.3.* to 7.4.1.  In both cases, the 
>>> schemas which existed in the original databases were not created in 
>>> the new database.

> I found three of these messages in /var/log/messages on the box which 
> had the problem.

> ERROR:  permission denied for database pg_freebsddiary.org.schemas

Oh, I bet I know what this is: the owners of those schemas don't have
CREATE SCHEMA privileges, right?  You made the schemas as superuser with
CREATE SCHEMA foo AUTHORIZATION bar.

7.4's pg_dump will use AUTHORIZATION so that situations like this
restore correctly, but 7.3's pg_dump is stupid and tries to create the
schema as its owner.

In general I recommend that during an upgrade, you use the new version's
pg_dump to dump from the old server.  This way you get the benefit of
whatever improvements have been made in pg_dump since the previous
release.
        regards, tom lane