Thread: Create database bug in 8.1.3 ?
Hi Listers, I have the following problem (OS= RHELU2 , PG version= 8.1.3) when I try to Create a database : postgres=# CREATE DATABASE world3 TEMPLATE=template1 ENCODING='UTF8' TABLESPACE=tbs1 ; CREATE DATABASE Then I am backup-ing the database (a small toy DB) with pg_dumpall: pg_dumpall > alldb.bak Less alldb.bak: -------------------- .... CREATE DATABASE world3 WITH TEMPLATE = template0 OWNER = pg ENCODING = 'UTF8' TABLESPACE = tbs1; .... -------------------- Why pg_dumpall is stating that my template db is template0 and not template1 ? Is there any way to check what db (template0 or template1 ) was actually used a template when creating world2 db ? I am having the same problem with all my databases I have compiled and installed PG 8.1.3 from sources Any hints are highly appreciated Regards. Milen
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
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
"Pg_restore -C" is not creating a database ( Was Create database bug in 8.1.3 ? )
From
"Milen Kulev"
Date:
Hi Listers,
My saga contunues ;(
I can not get pg_restore working as expected (as described in the documentation).
My case : I have a DB, that I have archived with
pg_dump -C -Fc -d testdb1 > tdb1b.bak
Then I am dropping the database testdb1 ( with DROP DATABASE testdb1; ) I want to recover it. When I issue the
following
command:
pg_restore -C -Fc tdb1b.bak
I am getting all the SQL statements executed, but the DB testdb1 is NOT created, although
I have explicitely specified it (-C option).
BUT if I create DB testdb1 manually and then issue:
pg_restore -Fc -d testdb1 < tdb1b.bak
I am getting the result what I want/expect.
What I am doinf wrong ?
Any hints, suggestions ?
Best Regards, Milen
-----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
"Milen Kulev" <makulev@gmx.net> writes: > Hi Wiliam, > Than you very much for your prompt reply. > > I can not understand why pg_dump, pg_dumpall have hard-coded "template0" ? The reason is this: any extra stuff that your database inherited from template1 (or whatever template you used) will be dumped out as part of your database. There is no way to for pg_dump to tell what parts came from template1 and what parts were added afterward, so it bases its dump on template0, which is a minimal database. If you based your restored database on template1, you would get collisions as the restore tried to add objects that were already there from template1. Make sense? -Doug
Hi Dough, Thank you for your answer. Now the reasons for using template0 DB are pretty clear (there a mini test-case Of this in the mail you have just answered;)). Thanks again. Regards. Milen -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Douglas McNaught Sent: Sunday, April 09, 2006 4:05 PM To: Milen Kulev Cc: 'William ZHANG'; pgsql-general@postgresql.org Subject: Re: [GENERAL] Create database bug in 8.1.3 ? -> solved "Milen Kulev" <makulev@gmx.net> writes: > Hi Wiliam, > Than you very much for your prompt reply. > > I can not understand why pg_dump, pg_dumpall have hard-coded > "template0" ? The reason is this: any extra stuff that your database inherited from template1 (or whatever template you used) will be dumped out as part of your database. There is no way to for pg_dump to tell what parts came from template1 and what parts were added afterward, so it bases its dump on template0, which is a minimal database. If you based your restored database on template1, you would get collisions as the restore tried to add objects that were already there from template1. Make sense? -Doug ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
Douglas McNaught <doug@mcnaught.org> writes:
> "Milen Kulev" <makulev@gmx.net> writes:
>> I can not understand why pg_dump, pg_dumpall have hard-coded "template0" ?
> The reason is this: any extra stuff that your database inherited from
> template1 (or whatever template you used) will be dumped out as part
> of your database. There is no way to for pg_dump to tell what parts
> came from template1 and what parts were added afterward, so it bases
> its dump on template0, which is a minimal database. If you based your
> restored database on template1, you would get collisions as the
> restore tried to add objects that were already there from template1.
Not only that. If you changed template1 after creating your database
from it, then a dump and restore of your database would be wrong if it
used template1: it would produce a database that did not match what was
dumped, but rather included those subsequent changes in template1.
(Which might in fact be what you'd wish for, but it's not pg_dump's
charter.) template0 is not only minimal but stable, so basing the
restore relative to it is more likely to produce a matching database
than using template1.
regards, tom lane
Re: "Pg_restore -C" is not creating a database ( Was Create database bug in 8.1.3 ? )
From
Tom Lane
Date:
"Milen Kulev" <makulev@gmx.net> writes:
> My case : I have a DB, that I have archived with
> pg_dump -C -Fc -d testdb1 > tdb1b.bak
> Then I am dropping the database testdb1 ( with DROP DATABASE testdb1; ) I want to recover it. When I issue the
following
> command:
> pg_restore -C -Fc tdb1b.bak
> I am getting all the SQL statements executed, but the DB testdb1 is NOT created, although
> I have explicitely specified it (-C option).
Works AFAICS --- I see this in pg_restore's output:
CREATE DATABASE testdb1 WITH TEMPLATE = template0 ENCODING = 'UTF8';
ALTER DATABASE testdb1 OWNER TO postgres;
\connect testdb1
Are you not getting those commands? Maybe they are failing for some
reason?
regards, tom lane
Re: "Pg_restore -C" is not creating a database ( Was Create database bug in 8.1.3 ? )
From
"Milen Kulev"
Date:
Hello Tom,
I am getting the commands you mention:
pg_restore -C -Fc tdb1b.bak 1> OUT
Less OUT:
CREATE DATABASE testdb1 WITH TEMPLATE = template0 ENCODING = 'UTF8' TABLESPACE = tbs1;
ALTER DATABASE testdb1 OWNER TO pg;
\connect testdb1
...
, but testdb1 is NOT created ! That is actually my problem.
According to the documentation, the DB should be created (-C option).
If I pre-create the database and then issue "pg_restore -C -Fc tdb1b.bak" all objects
get created as expected.
I am seeing some "DROP DATABASE" commend in the tdb1b.bak file (binary format)
pg@node5#[server1] /tmp]$ strings tdb1b.bak | less
PGDMP
testdb1
8.1.3
8.1.3
ENCODING
ENCODING
SET client_encoding = 'UTF8';
false
1262
41411
testdb1
DATABASE
CREATE DATABASE testdb1 WITH TEMPLATE = template0 ENCODING = 'UTF8' TABLESPACE = tbs1;
DROP DATABASE testdb1; <--!!!!!!!
false
2615
2200
public
SCHEMA
CREATE SCHEMA public;
DROP SCHEMA public;
Regards. Milen
-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Tom Lane
Sent: Sunday, April 09, 2006 6:01 PM
To: Milen Kulev
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] "Pg_restore -C" is not creating a database ( Was Create database bug in 8.1.3 ? )
"Milen Kulev" <makulev@gmx.net> writes:
> My case : I have a DB, that I have archived with
> pg_dump -C -Fc -d testdb1 > tdb1b.bak
> Then I am dropping the database testdb1 ( with DROP DATABASE testdb1;
> ) I want to recover it. When I issue the following
> command:
> pg_restore -C -Fc tdb1b.bak
> I am getting all the SQL statements executed, but the DB testdb1 is NOT created, although
> I have explicitely specified it (-C option).
Works AFAICS --- I see this in pg_restore's output:
CREATE DATABASE testdb1 WITH TEMPLATE = template0 ENCODING = 'UTF8';
ALTER DATABASE testdb1 OWNER TO postgres;
\connect testdb1
Are you not getting those commands? Maybe they are failing for some reason?
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
Re: "Pg_restore -C" is not creating a database ( Was Create database bug in 8.1.3 ? )
From
Tom Lane
Date:
"Milen Kulev" <makulev@gmx.net> writes:
> I am getting the commands you mention:
> pg_restore -C -Fc tdb1b.bak 1> OUT
> Less OUT:
> CREATE DATABASE testdb1 WITH TEMPLATE = template0 ENCODING = 'UTF8' TABLESPACE = tbs1;
> ALTER DATABASE testdb1 OWNER TO pg;
> \connect testdb1
> ...
> , but testdb1 is NOT created !
So why not? Try reading the error messages that are (probably) reported
when you run the script. Maybe you're trying to run the script as a
user that hasn't got CREATEDB privilege?
regards, tom lane
Re: "Pg_restore -C" is not creating a database ( Was Create database bug in 8.1.3 ? )
From
"Milen Kulev"
Date:
Hi Tom,
There are no error messages repored. Neiter in the logfile of the server, nor on
The tty , which I am issuing the commands from.
I am not switching the OS user (all commands are entered interactively, no cron jobs, shell scripts etc).
Regeards, Milen
-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Tom Lane
Sent: Sunday, April 09, 2006 8:24 PM
To: Milen Kulev
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] "Pg_restore -C" is not creating a database ( Was Create database bug in 8.1.3 ? )
"Milen Kulev" <makulev@gmx.net> writes:
> I am getting the commands you mention:
> pg_restore -C -Fc tdb1b.bak 1> OUT
> Less OUT:
> CREATE DATABASE testdb1 WITH TEMPLATE = template0 ENCODING = 'UTF8'
> TABLESPACE = tbs1; ALTER DATABASE testdb1 OWNER TO pg; \connect
> testdb1 ...
> , but testdb1 is NOT created !
So why not? Try reading the error messages that are (probably) reported when you run the script. Maybe you're trying
to run the script as a user that hasn't got CREATEDB privilege?
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
Re: "Pg_restore -C" is not creating a database ( Was Create database bug in 8.1.3 ? )
From
Tom Lane
Date:
"Milen Kulev" <makulev@gmx.net> writes:
>> I am getting the commands you mention:
>> pg_restore -C -Fc tdb1b.bak 1> OUT
>> Less OUT:
>> CREATE DATABASE testdb1 WITH TEMPLATE = template0 ENCODING = 'UTF8'
>> TABLESPACE = tbs1; ALTER DATABASE testdb1 OWNER TO pg; \connect
>> testdb1 ...
>> , but testdb1 is NOT created !
Um ... you do realize that in this mode pg_restore just prints the SQL
commands to its stdout? You're supposed to pipe them into psql if you
want anything to really happen. Or you can add a -d switch (usually
"-d template1" when using -C) if you want pg_restore to connect to a
database server and issue the commands directly.
regards, tom lane
Re: "Pg_restore -C" is not creating a database ( Was Create database bug in 8.1.3 ? )
From
"Milen Kulev"
Date:
Great hint Tom!
pg_restore -C -Fc -d template1 < tdb1b.bak
Did the job. Actually if I specify any other database I am getting the same result ;)
I wanted to use custom (-Fc) option to backup BLOB files.
Why should I use the output of pg_restore to restore my BLOBs (if it is possible at all)?
If it were the case I simply would have used the plain text backup (pg_dump -Fp and then psql)...
Regards. Milen
-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Tom Lane
Sent: Sunday, April 09, 2006 9:45 PM
To: Milen Kulev
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] "Pg_restore -C" is not creating a database ( Was Create database bug in 8.1.3 ? )
"Milen Kulev" <makulev@gmx.net> writes:
>> I am getting the commands you mention:
>> pg_restore -C -Fc tdb1b.bak 1> OUT
>> Less OUT:
>> CREATE DATABASE testdb1 WITH TEMPLATE = template0 ENCODING = 'UTF8'
>> TABLESPACE = tbs1; ALTER DATABASE testdb1 OWNER TO pg; \connect
>> testdb1 ...
>> , but testdb1 is NOT created !
Um ... you do realize that in this mode pg_restore just prints the SQL commands to its stdout? You're supposed to pipe
them into psql if you want anything to really happen. Or you can add a -d switch (usually "-d template1" when using
-C)
if you want pg_restore to connect to a database server and issue the commands directly.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Milen Kulev wrote: > Hi Listers, > I have the following problem (OS= RHELU2 , PG version= 8.1.3) when I try to Create a database : > > postgres=# CREATE DATABASE world3 TEMPLATE=template1 ENCODING='UTF8' TABLESPACE=tbs1 ; > CREATE DATABASE > > Then I am backup-ing the database (a small toy DB) with pg_dumpall: > > pg_dumpall > alldb.bak > > Less alldb.bak: > -------------------- > .... > CREATE DATABASE world3 WITH TEMPLATE = template0 OWNER = pg ENCODING = 'UTF8' TABLESPACE = tbs1; .... > -------------------- > > Why pg_dumpall is stating that my template db is template0 and not template1 ? Is there any way to check what db > (template0 or template1 ) was actually used a template when creating world2 db ? I am having the same problem with all > my databases Why exactly is this a problem? Everything that you might have manually added to template1 is now part of world3 anyway, and therfore included in the dump of world3. When recreating world3 from the dump, starting from an completly empty database (template0) makes sense for me - otherwise you'd get lots of "... already exists" error, because pg_dump would try to restore some objects which already exist in the newly created world3-version, because they were (again!) inherited from template1. greetings, Florian Pflug