> 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.
