Re: Problem with pg_upgrade (8.4 -> 9.0) due to ALTER DATABASE SET ROLE

Robert Haas wrote:
> On Thu, Jan 6, 2011 at 1:59 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> > Well, if everyone who logs in gets the same username, you can easily
> > conclude that trying to dump/restore the database will cause problems if
> > you have objects in there that are not owned by that user.
>
> I can't, and neither could Florian. I'm not sure why this is so
> obvious to you and Tom. Unless I've made some catastrophic *manual*
> change to the system catalogs, like nuking pg_proc, I expect dump and
> restore to just work. pg_dump's job is to emit a series of commands
> that will work. Every time I run across a case where it doesn't, I'm
> violently annoyed, because it's happened to me as a user and it feels
> like a bug every time. Florian is probably made of a bit sterner
> stuff than the typical user, but a typical user doesn't go "Oh, gee,
> dump and restore didn't work, I guess that setting I installed in
> there six years ago must actually be something that the developers
> never intended for me to do." First they cuss, and then they blame us
> for not being able to dump the database that we let them create, and
> then if they're really ticked they go use some other product. When
> someone actually takes the time to troubleshoot what broke and let us
> know, the only correct response from our end is to say "thanks, we'll
> work on making that less confusing", not "well that was a stupid thing
> to do".

Well, we usually tell people to restore as super-user, particularly
pg_dumpall, but in this case, it is impossible. Certainly pg_upgrade
requires it, which is the root of the problem.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +