duplicating a schema

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.

--
★ Clemens 呉 Schwaighofer
★ IT Engineer/Web Producer/Planning
★ E-Graphics Communications SP Digital
★ 6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN
★ Tel: +81-(0)3-3545-7706
★ Fax: +81-(0)3-3545-7343
★ http://www.e-gra.co.jp

This e-mail is intended only for the named person or entity to which
it is addressed and contains valuable business information that is
privileged, confidential and/or otherwise protected from disclosure.
If you received this e-mail in error, any review, use, dissemination,
distribution or copying of this e-mail is strictly prohibited.
Please notify us immediately of the error via e-mail to
disclaimer@tbwaworld.com and please delete the e-mail from your system, retaining no copies in any media.
We appreciate your cooperation.

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

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