On Tue, 1 Dec 2009 09:53:12 +0100 Ivan Sergio Borgonovo <mail@xxxxxxxxxxxxxxx> wrote: > 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. 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
