Thread: Copy Schema
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
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
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 ===
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 > > > >
>>> 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
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
>