Обсуждение: duplicating a schema
I need to create a new schema with all the content in an existing one, just with a new name. The way I've found is: - make a backup - load it in a dev box - rename the schema - make a backup of the new schema - restore the new schema on the original DB. Is there a more efficient approach? -- Ivan Sergio Borgonovo http://www.webthatworks.it
On Sun, Nov 29, 2009 at 21:21, Ivan Sergio Borgonovo <mail@webthatworks.it> wrote: > I need to create a new schema with all the content in an existing > one, just with a new name. > > The way I've found is: > - make a backup > - load it in a dev box > - rename the schema > - make a backup of the new schema > - restore the new schema on the original DB. > > Is there a more efficient approach? Sadly no. With smaller DBs I do a sed on the dump ... wished there would be a restore with not only a target DB but also a target schema. -- ★ Clemens 呉 Schwaighofer ★ IT Engineer/Web Producer/Planning ★ E-Graphics Communications SP Digital ★ 6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN ★ Tel: +81-(0)3-3545-7706 ★ Fax: +81-(0)3-3545-7343 ★ http://www.e-gra.co.jp This e-mail is intended only for the named person or entity to which it is addressed and contains valuable business information that is privileged, confidential and/or otherwise protected from disclosure. If you received this e-mail in error, any review, use, dissemination, distribution or copying of this e-mail is strictly prohibited. Please notify us immediately of the error via e-mail to disclaimer@tbwaworld.com and please delete the e-mail from your system, retaining no copies in any media. We appreciate your cooperation.
On Tue, 1 Dec 2009 11:39:06 +0900 Schwaighofer Clemens <clemens.schwaighofer@tequila.jp> wrote: > On Sun, Nov 29, 2009 at 21:21, Ivan Sergio Borgonovo > <mail@webthatworks.it> wrote: > > I need to create a new schema with all the content in an existing > > one, just with a new name. > > The way I've found is: > > - make a backup > > - load it in a dev box > > - rename the schema > > - make a backup of the new schema > > - restore the new schema on the original DB. > > Is there a more efficient approach? > Sadly no. With smaller DBs I do a sed on the dump ... wished there > would be a restore with not only a target DB but also a target > schema. I thought about sed but I think postgresql parse better SQL than me and sed together. Why do you prefer sed over backup/restore on smaller DB? I didn't test this... but I think it could be even better if I wouldn't prefer to have a full backup before such operation: pg_dump -dmydb --schema=XXX -Fp > XXX.bak begin; alter schema XXX rename to YYY; create schema XXX; \i XXX.bak; commit; This could be obtained with a pipe... but in case something goes wrong I'd prefer to have the "backup" of the schema somewhere in spite of needing to recreate it. Renaming a schema seems pretty fast. So I don't think in case the transaction abort it would make any big difference compared to changing the schema name in another DB. Still being able to have a schema as a target would make things cleaner, faster and safer. -- Ivan Sergio Borgonovo http://www.webthatworks.it
On Tue, 1 Dec 2009 09:53:12 +0100 Ivan Sergio Borgonovo <mail@webthatworks.it> wrote: > On Tue, 1 Dec 2009 11:39:06 +0900 > Schwaighofer Clemens <clemens.schwaighofer@tequila.jp> wrote: > > > On Sun, Nov 29, 2009 at 21:21, Ivan Sergio Borgonovo > > <mail@webthatworks.it> wrote: > > > I need to create a new schema with all the content in an > > > existing one, just with a new name. > > > > The way I've found is: > > > - make a backup > > > - load it in a dev box > > > - rename the schema > > > - make a backup of the new schema > > > - restore the new schema on the original DB. > > > > Is there a more efficient approach? > > > Sadly no. With smaller DBs I do a sed on the dump ... wished > > there would be a restore with not only a target DB but also a > > target schema. > I thought about sed but I think postgresql parse better SQL than me > and sed together. semi-tested solution: pg_dump -d mydb -Fp --schema=XXX > mydb.bak (echo 'begin;'; echo 'alter schema XXX rename to YYY'; echo 'create schema XXX' authorization AAA; pg_restore --schema=XXX < mydb.bak; echo 'commit;') > psql -d mydb And some further notes and a script here http://www.webthatworks.it/d1/node/page/howto_duplicating_schema_postgresql -- Ivan Sergio Borgonovo http://www.webthatworks.it