On Tue, 1 Dec 2009 11:39:06 +0900 Schwaighofer Clemens <clemens.schwaighofer@xxxxxxxxxx> wrote: > On Sun, Nov 29, 2009 at 21:21, Ivan Sergio Borgonovo > <mail@xxxxxxxxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
