Re: duplicating a schema

On Tue, 1 Dec 2009 09:53:12 +0100
Ivan Sergio Borgonovo <mail(at)webthatworks(dot)it> wrote:

> On Tue, 1 Dec 2009 11:39:06 +0900
> Schwaighofer Clemens <clemens(dot)schwaighofer(at)tequila(dot)jp> wrote:
>
> > On Sun, Nov 29, 2009 at 21:21, Ivan Sergio Borgonovo
> > <mail(at)webthatworks(dot)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