Re: duplicating a schema

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.
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