Thread: documentation vs reality: template databases

running 8.1 on a fedora core 5 linux box, up to date so far as
i know.

this page:

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

says the following:

    Note:  template1 and template0 do not have any special status beyond
    the fact that the name template1 is the default source database name
    for CREATE DATABASE. For example, one could drop template1 and
    recreate it from template0 without any ill effects. This course of
    action might be advisable if one has carelessly added a bunch of junk
    in template1.

having just carelessly loaded a backup into template1 instead of the
place i wanted to load it, i had a chance to try to follow this advice:

foo=# drop database template1;
ERROR: cannnot drop a template database
foo=#

cheers,
   richard

Richard P. Welty wrote:
> running 8.1 on a fedora core 5 linux box, up to date so far as
> i know.
>
> this page:
>
> http://www.postgresql.org/docs/8.1/interactive/manage-ag-templatedbs.html
>
> says the following:
>
>    Note:  template1 and template0 do not have any special status beyond
>    the fact that the name template1 is the default source database name
>    for CREATE DATABASE. For example, one could drop template1 and
>    recreate it from template0 without any ill effects. This course of
>    action might be advisable if one has carelessly added a bunch of junk
>    in template1.
>
> having just carelessly loaded a backup into template1 instead of the
> place i wanted to load it, i had a chance to try to follow this advice:
>
> foo=# drop database template1;
> ERROR: cannnot drop a template database
> foo=#

This was written a while ago, but this gives you pretty detailed
instructions about how to fix this:

http://www.postgresql.org/docs/techdocs.22

--
Postgresql & php tutorials
http://www.designmagick.com/

Richard P. Welty wrote:
> running 8.1 on a fedora core 5 linux box, up to date so far as
> i know.
>
> this page:
>
> http://www.postgresql.org/docs/8.1/interactive/manage-ag-templatedbs.html
>
> says the following:
>
>     Note:  template1 and template0 do not have any special status beyond
>     the fact that the name template1 is the default source database name
>     for CREATE DATABASE. For example, one could drop template1 and
>     recreate it from template0 without any ill effects. This course of
>     action might be advisable if one has carelessly added a bunch of junk
>     in template1.
>
> having just carelessly loaded a backup into template1 instead of the
> place i wanted to load it, i had a chance to try to follow this advice:
>
> foo=# drop database template1;
> ERROR: cannnot drop a template database
> foo=#

The comment in the source code says:

    /*
     * Disallow dropping a DB that is marked istemplate.  This is just to
     * prevent people from accidentally dropping template0 or template1; they
     * can do so if they're really determined ...
     */

I have added a documentaiton mention that 'datistemplate' must be
changed to 'false' to allow deletion.  Backpatched to 8.2.X.

--
  Bruce Momjian   bruce@momjian.us
  EnterpriseDB    http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +
Index: doc/src/sgml/manage-ag.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/manage-ag.sgml,v
retrieving revision 2.49
diff -c -c -r2.49 manage-ag.sgml
*** doc/src/sgml/manage-ag.sgml    5 Nov 2006 22:42:07 -0000    2.49
--- doc/src/sgml/manage-ag.sgml    20 Jan 2007 15:37:09 -0000
***************
*** 257,263 ****
      For example, one could drop <literal>template1</> and recreate it from
      <literal>template0</> without any ill effects.  This course of action
      might be advisable if one has carelessly added a bunch of junk in
!     <literal>template1</>.
     </para>

     <para>
--- 257,264 ----
      For example, one could drop <literal>template1</> and recreate it from
      <literal>template0</> without any ill effects.  This course of action
      might be advisable if one has carelessly added a bunch of junk in
!     <literal>template1</>. (To delete <literal>template1</literal>,
!     it must have <literal>datistemplate = false</>.)
     </para>

     <para>

Bruce Momjian wrote:

>       For example, one could drop <literal>template1</> and recreate it from
>       <literal>template0</> without any ill effects.  This course of action
>       might be advisable if one has carelessly added a bunch of junk in
> !     <literal>template1</>. (To delete <literal>template1</literal>,
> !     it must have <literal>datistemplate = false</>.)
>      </para>

I don't find this particularly clear -- how does the user know where to
set the "datistemplate" it refers to?  I guess you should mention
pg_database, or be more explicit about what to do.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Alvaro Herrera wrote:
> Bruce Momjian wrote:
>
> >       For example, one could drop <literal>template1</> and recreate it from
> >       <literal>template0</> without any ill effects.  This course of action
> >       might be advisable if one has carelessly added a bunch of junk in
> > !     <literal>template1</>. (To delete <literal>template1</literal>,
> > !     it must have <literal>datistemplate = false</>.)
> >      </para>
>
> I don't find this particularly clear -- how does the user know where to
> set the "datistemplate" it refers to?  I guess you should mention
> pg_database, or be more explicit about what to do.

OK, it is now:

      <literal>template1</>. (To delete <literal>template1</literal>,
      it must have <literal>pg_database.datistemplate = false</>.)

--
  Bruce Momjian   bruce@momjian.us
  EnterpriseDB    http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +