Thread: New DBs from existing schemas
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
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/
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