Re: refactoring a database owner without "reassign owned"

Поиск
Список
Период
Сортировка
От Jeff Janes
Тема Re: refactoring a database owner without "reassign owned"
Дата
Msg-id CAMkU=1xiiwf4mzgMxdxirxdQoYwJZJOaeFPKw4J_78nda27HCg@mail.gmail.com
обсуждение исходный текст
Ответ на Re: refactoring a database owner without "reassign owned"  (Alvaro Herrera <alvherre@2ndquadrant.com>)
Ответы Re: refactoring a database owner without "reassign owned"  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-general
Дерево обсуждения
On Mon, May 13, 2013 at 2:00 PM, Alvaro Herrera <alvherre@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

В списке pgsql-general по дате отправления:

Предыдущее
От: Alvaro Herrera
Дата:
Сообщение: Re: refactoring a database owner without "reassign owned"
Следующее
От: Tom Lane
Дата:
Сообщение: Re: refactoring a database owner without "reassign owned"