Thread: Create database bug in 8.1.3 ?

Create database bug in 8.1.3 ?

From
"Milen Kulev"
Date:

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



Re: Create database bug in 8.1.3 ?

From
"William ZHANG"
Date:
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



Re: Create database bug in 8.1.3 ? -> solved

From
"Milen Kulev"
Date:
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


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


Re: Create database bug in 8.1.3 ? -> solved

From
Douglas McNaught
Date:
"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

Re: Create database bug in 8.1.3 ? -> solved

From
"Milen Kulev"
Date:
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


Re: Create database bug in 8.1.3 ? -> solved

From
Tom Lane
Date:
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

"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

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


"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

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


"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

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


Re: Create database bug in 8.1.3 ?

From
"Florian G. Pflug"
Date:
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