Thread: Copy Schema

Copy Schema

From
"Derrick Betts"
Date:
Is there a way to create a "template" schema, similar to modifying the template1 to use for new database creation?
 
I'm trying to get consistent tables and all other metadata set in one schema and then use that schema as a template for creating other schemas in the same database as needed.
 
I read that there is a way to restore a schema with pg_restore command.  Is the best or only way to accomplish this?
 
Thank you,
Derrick Betts

Re: Copy Schema

From
Michael Glaesemann
Date:
On Jul 28, 2006, at 14:09 , Derrick Betts wrote:

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

Hope this helps.

Michael Glaesemann
grzm seespotcode net




Re: Copy Schema

From
"A. Kretschmer"
Date:
am  28.07.2006, um 15:22:10 +0900 mailte Michael Glaesemann folgendes:
>
> On Jul 28, 2006, at 14:09 , Derrick Betts wrote:
>
> >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:

I think, he want to create new schemas in a existing database, not new
databases.


Regards, Andreas
--
Andreas Kretschmer    (Kontakt: siehe Header)
Heynitz:  035242/47215,      D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
 ===    Schollglas Unternehmensgruppe    ===

Re: Copy Schema

From
"Derrick Betts"
Date:
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

----- Original Message -----
From: "Michael Glaesemann" <grzm@seespotcode.net>
To: "Derrick Betts" <derrick@blueaxis.com>
Cc: <pgsql-novice@postgresql.org>
Sent: Friday, July 28, 2006 12:22 AM
Subject: Re: [NOVICE] Copy Schema


>
> On Jul 28, 2006, at 14:09 , Derrick Betts wrote:
>
>> 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.
>
> Hope this helps.
>
> Michael Glaesemann
> grzm seespotcode net
>
>
>
>



Re: Copy Schema

From
Keith Worthington
Date:
>>> 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

Re: Copy Schema

From
"Derrick Betts"
Date:
Thank you for your response.
    This one:  "add a predefined schema to an existing database."

I'll take a closer look at the document and information you provided.

Thanks again,
Derrick

----- Original Message -----
From: "Keith Worthington" <KeithW@NarrowPathInc.com>
To: "Derrick Betts" <derrick@blueaxis.com>
Cc: <pgsql-novice@postgresql.org>
Sent: Friday, July 28, 2006 10:28 AM
Subject: Re: [NOVICE] Copy Schema


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