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

bruce wrote:
> 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.

We could modify pg_dump to emit RESET AUTHORIZATION in --binary-upgrade
mode. I am unclear if that might cause some other problems though.

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

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