Обсуждение: Set new owner on cloned database
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? -- *** 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
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