Thread: New DBs from existing schemas

New DBs from existing schemas

From
Nishad Prakash
Date:
I want to create a new database with the exact schema of an existing one,
but a different name.  After some reading, it seems

pg_dump -s old_db > old_schema
createdb -t old_schema new_db

should work.  Will it?  Note that old_db has lots of stored functions and
user-defined operators in addition to tables and indices.

Also, is there any way to selectively restore some of the data from old_db
into new_db as well?  I'll settle for all data if selectivity isn't
possible.  Here, I don't have any good guesses, as the docs for pg_restore
don't say anything specific about restoring into a db other than the one
dumped from.

Is there a nice way to do these things that doesn't involve pg_dump | sed?

nishad
--
"Underneath the concrete, the dream is still alive" -- Talking Heads


Re: New DBs from existing schemas

From
Chris
Date:
Nishad Prakash wrote:
> I want to create a new database with the exact schema of an existing one,
> but a different name.  After some reading, it seems
>
> pg_dump -s old_db > old_schema

That will dump all schemas from that db. If you only want a particular
schema, use '-n'.

> createdb -t old_schema new_db

There is no '-t' at least in 8.1.1..

'-T' on the other hand lets you specify an existing db and it will use
that as a base instead of template1.

> should work.  Will it?  Note that old_db has lots of stored functions and
> user-defined operators in addition to tables and indices.

You could just use the first database as a template (using '-T') for the
second one and that will copy everything across but that includes your data.

> Also, is there any way to selectively restore some of the data from old_db
> into new_db as well?  I'll settle for all data if selectivity isn't
> possible.  Here, I don't have any good guesses, as the docs for pg_restore
> don't say anything specific about restoring into a db other than the one
> dumped from.

You can dump particular tables only but you don't get anything more
selective.

--
Postgresql & php tutorials
http://www.designmagick.com/

Re: New DBs from existing schemas

From
Karsten Hilbert
Date:
On Wed, May 24, 2006 at 04:14:46PM -0700, Nishad Prakash wrote:

> I want to create a new database with the exact schema of an existing one,
> but a different name.  After some reading, it seems
>
> pg_dump -s old_db > old_schema
> createdb -t old_schema new_db
Now new_db is a 1:1 copy of old_schema, triggers, data and
all. You may need to setup login permissins in pg_hba for
users to be able to connect to new_db.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346