Re: Create database bug in 8.1.3 ? -> solved

Hi Wiliam,
Than you very much for your prompt reply.

I can not understand why pg_dump, pg_dumpall have hard-coded "template0" ?
In my case I haven't modified template1 DB ( e.g. template0  equals template1 DB), so it
Doesn't mater for me, bit I am still wondering ... ;( .
The documentation is saying : (http://www.postgresql.org/docs/8.1/static/sql-createdatabase.html)
"
By default, the new database will be created by cloning the standard system database template1. A different template
can
be specified by writing TEMPLATE name.
"


Anyway, I have just found that PG is applying all the additional features(tables, functions, types) that I have
installed on my database, this ensuring that I really  get a copy of my database as I have configured it. Obviously (as
you already pointed out), all this is done to avoid " db  dependencies creation".

Sample output of my test:
1) Creating my own template DB
CREATE  DATABASE  mytemplate TEMPLATE=template1 ENCODING='UTF8' ;
2)  Install Tsearchd   operators, data types, tables  and etc
psql  -d mytemplate  < tsearch2.sql
3)  Create my custom template table
mytemplate=# create table tab_template (id int) ;
mytemplate=# insert  into tab_template  VALUES  (1 );
INSERT 0 1

4) Create DB using my custom template
CREATE  DATABASE  testdb1 TEMPLATE=mytemplate ENCODING='UTF8' TABLESPACE=tbs1 ;

5) Create tables  of sample schma
psql  -d  testdb1 < world.sql


6) Create Archive of my  testdb1  DB.
pg_dump -C -Fp  -d   testdb1 > tdb1.bak

7)  After checking the content of tdb1.bak I have found that each installed funtionality is there, although that my DB
is derived from template0  and not from mytemplate
CREATE DATABASE testdb1 WITH TEMPLATE = template0 ENCODING = 'UTF8' TABLESPACE = tbs1;



-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of William ZHANG
Sent: Sunday, April 09, 2006 2:37 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Create database bug in 8.1.3 ?


The backend doesnot save the name of the template database used by "CREATE DATABASE".

pg_dump, pg_dumpall have hard code like this:
    appendPQExpBuffer(buf, " WITH TEMPLATE = template0"); That's why you found the template database is always
`template0`.

If the backend saves the name of the template database, and dump the database as
    "CREATE DATABASE foo WITH TEMPLATE 'bar';",
dump and restore should be careful to dump/restore 'bar' before 'foo'. If 'bar' is modified after the "CREATE DATABASE"
command, things will be more complicated.

If you have not modified system catalogs in template1, using template0 in dump should cause no problem.

Regrads,
William ZHANG



---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org