On Mon, May 13, 2013 at 2:00 PM, Alvaro Herrera <alvhe...@2ndquadrant.com>wrote:
> Jeff Janes escribió: > > > I think "reassign owned" should detect that it is being invoked on the > > internal user (as it does now) but then instead of refusing to run, it > > should DWIM. I suppose that was not implemented because it is difficult > to > > do so (but of course that is all the more reason not to leave it to the > dba > > to figure out how to do it themselves). Perhaps this is a todo item? > > Hm, so what would you have it do, precisely? > >From the users perspective, I would have it reassign ownership of exactly those objects which are not "required by the database system", as the error message puts it. >From the implementers perspective, I don't really know. It does occur to me that pg_dump must know which objects those are, but how to get that knowledge into "reassign owned" may be another matter. Maybe I'll transfer this over to the hackers list once I have some time to look into it. But knowing that pg_dump knows how to do this, leads me to this semi-automated solution to the original question (assuming you already ran "make installcheck" to obtain the database you want to refactor): psql -c 'create role regression login;' pg_dump -s regression | \ perl -lne 's/^(ALTER.*OWNER TO) postgres;/$1 regression;/ and print' | \ psql regression postgres psql -c 'alter database regression owner to regression' I don't know if there is any circumstance in which pg_dump will split the ALTER.*OWNER TO over more than one line. Cheers, Jeff
