Re: Copy Schema

[Keith Worthington <KeithW(at)NarrowPathInc(dot)com>]

> > > Is there a way to create a "template" schema, similar to modifying  
> > > the template1 to use for new database creation?
> >
> > Certainly. Any database can be used as a template. template1 is used  
> > by default. Check out the CREATE DATABASE docs:
> >
> > http://www.postgresql.org/docs/current/interactive/sql- 
> > createdatabase.html
> >
> > > I read that there is a way to restore a schema with pg_restore  
> > > command. Is the best or only way to accomplish this?
> >
> >
> > Well, if you haven't loaded data into a database you want to use as a 
> > template (or it only has data you want to include in the new  
> > database), you can use CREATE DATABASE as well.
>
> Derrick Betts wrote:
> I know how to create new databases by using the template1 already.  I 
> want to create new schemas with the requirements outlined in the 
> original email. Any thoughts?
>
> Derrick

Derrick,

There seems to be some confusion as to whether you want to create a new 
database with an existing schema or add a predefined schema to an 
existing database.

If the former then check out the docs at:
http://www.postgresql.org/docs/current/interactive/sql-createdatabase.html
and try something like
CREATE DATABASE mydb
  WITH TEMPLATE = mytemplatedb;

If the latter then check out the docs at:
http://www.postgresql.org/docs/8.1/interactive/app-pgdump.html
and try something like
pg_dump --file=outfile.sql
        --format=p
        --schema=myschema
        --schema-only
        --verbose
        mytemplatedb
to generate the schema commands and psql to run them in the target database.

HTH

--

Kind Regards,
Keith