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