schema creation during initdb

sharath kumar wrote:

How can I create a schema during initdb time?
For example pg_catalog, pg_toast, information_schema are created
during initdb time. Likewise I want to create my own schema at initdb
time. How can i do it? Also how to create a table into this schema at
that time itself?

This question really does not belong on -hackers, which is about
postgres development, not usage. Next time use pgsql-general.

You have misunderstood the purpose of initdb, which is to prepare a
location for running a postmaster against.

After initdb has run there are no user databases yet created (unless you
count the postgres database).

So you would need to do something like this:

initdb ...
pg_ctl start ...
createdb mytemplate
psql -c 'create schema foo' mytemplate
psql -c 'create table foo.bar ...' mytemplate

After that you can do this:

createdb --template mytemplate newdb

and the newdb will have your schema and table.

HTH

andrew

Sorry i have put the question wrongly. I wanted to ask if we can create a
schema during createdb time so that i have to hide the following from the
user.
psql -c 'create schema foo' mytemplate
psql -c 'create table foo.bar ...' mytemplate
Whenever a user runs createdb command, the above schema and table should be
created automatically.
Thanks,
Sharat.

sharath kumar wrote:

Sorry i have put the question wrongly. I wanted to ask if we can create a
schema during createdb time so that i have to hide the following from the
user.
psql -c 'create schema foo' mytemplate
psql -c 'create table foo.bar ...' mytemplate
Whenever a user runs createdb command, the above schema and table should be
created automatically.

You should create a template database with the schema and the tables you
want. See chapter "19.3. Template Databases" in the user manual:

http://www.postgresql.org/docs/8.2/interactive/manage-ag-templatedbs.html

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

Hi,

On 4/18/07, sharath kumar <k.sharat@gmail.com> wrote:

Sorry i have put the question wrongly. I wanted to ask if we can create a
schema during createdb time so that i have to hide the following from the
user.
psql -c 'create schema foo' mytemplate
psql -c 'create table foo.bar ...' mytemplate
Whenever a user runs createdb command, the above schema and table should
be created automatically.
Thanks,
Sharat.

One way that I can think of doing this would be by adding the details about
the new schema and the tables that lie therein in the
src/backend/catalog/information_schema.sql file (I dont know if this is the
recommended way though). These will end up becoming a part of template1 and
any subsequent databases that are created will contain them.

Regards,
Nikhils

--
EnterpriseDB http://www.enterprisedb.com

NikhilS wrote:

One way that I can think of doing this would be by adding the details
about the new schema and the tables that lie therein in the
src/backend/catalog/information_schema.sql file (I dont know if this
is the recommended way though). These will end up becoming a part of
template1 and any subsequent databases that are created will contain
them.

Well, *I* wouldn't recommend it. That installed version of that file
(which if anything is what he should mangle rathwer than the source) is
really just an implementation artefact.

Why would this be better than either loading the schema+table directly
into template1 after initdb, or making your own template db and loading
them into that?

cheers

andrew