Re: refactoring a database owner without "reassign owned"

On Mon, May 13, 2013 at 2:00 PM, Alvaro Herrera <alvherre(at)2ndquadrant(dot)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