Set new owner on cloned database

On Wed, 8 Dec 2010 13:40:29 -0500 (EST)
"James B. Byrne" <byrnejb@harte-lyne.ca> wrote:

I am testing a Rails deployment and wish to copy a database
assigning it an new owner. I have tried this:

createdb --owner=hll_theheart_db_devl
--template=hll_th_deploytest_prod hll_theheart_devl

While this indeed sets the database owner to hll_theheart_db_devl
everything else, schema, tables whatever, remains owned by the
original owner. Is there no way to change the owner everywhere in
the cloned database using cretedb? Or am I constrained to do a
dump all and restore?

http://www.mail-archive.com/pgsql-hackers@postgresql.org/msg51048.html
http://www.mail-archive.com/pgsql-hackers@postgresql.org/msg51047.html

I'm not sure if there has been any progress in newer postgres to
support easier change of owner.
I'm not aware of any more current better solution.

Unfortunately I think the license of the above didn't help to make
people willing to improve and make the code more popular.

--
Ivan Sergio Borgonovo
http://www.webthatworks.it

Le 08/12/2010 22:41, Ivan Sergio Borgonovo a �crit :

On Wed, 8 Dec 2010 13:40:29 -0500 (EST)
"James B. Byrne" <byrnejb@harte-lyne.ca> wrote:

I am testing a Rails deployment and wish to copy a database
assigning it an new owner. I have tried this:

createdb --owner=hll_theheart_db_devl
--template=hll_th_deploytest_prod hll_theheart_devl

While this indeed sets the database owner to hll_theheart_db_devl
everything else, schema, tables whatever, remains owned by the
original owner. Is there no way to change the owner everywhere in
the cloned database using cretedb?

No, you can't. --owner changes only the owner of the database.

Or am I constrained to do a
dump all and restore?

http://www.mail-archive.com/pgsql-hackers@postgresql.org/msg51048.html
http://www.mail-archive.com/pgsql-hackers@postgresql.org/msg51047.html

I'm not sure if there has been any progress in newer postgres to
support easier change of owner.
I'm not aware of any more current better solution.

You should try REASSIGN OWNED BY. See
http://www.postgresql.org/docs/9.0/interactive/sql-reassign-owned.html

--
Guillaume
http://www.postgresql.fr
http://dalibo.com

On Wed, December 8, 2010 17:46, Guillaume Lelarge wrote:

You should try REASSIGN OWNED BY. See
http://www.postgresql.org/docs/9.0/interactive/sql-reassign-owned.html

Thanks for that. I ended up doing a pg_dump followed by a sed
followed by a psql < which sufficed for my purposes, even it it did
seem a bit convoluted. The REASSIGN OWNED BY seems the more
sensible approach.

--
*** E-Mail is NOT a SECURE channel ***
James B. Byrne mailto:ByrneJB@Harte-Lyne.ca
Harte & Lyne Limited http://www.harte-lyne.ca
9 Brockley Drive vox: +1 905 561 1241
Hamilton, Ontario fax: +1 905 561 0757
Canada L8E 3C3