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
