pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Re: Cleaning up template 1 -> template0 cloning
4+ messages / 3 participants
[nested] [flat]

* Re: Cleaning up template 1 -> template0 cloning
@ 2002-03-25 21:00  Thomas Good <tomg@admin.nrnet.org>
  0 siblings, 1 reply; 4+ messages in thread

From: Thomas Good @ 2002-03-25 21:00 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql

Josh - from the pg docs:
   
     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 and the default database-to-connect-to for
     various scripts such as createdb. 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.
     

------------------------------------------------------------------------
Thomas Good                                         tomg@admin.nrnet.org
Programmer/Analyst                                  phone:  718-818-5528
Residential Services, Behavioral Health Services    fax:    718-818-5056
Saint Vincent Catholic Medical Centers              mobile: 917-282-7359
--                                                                    --
SQL Clinic - An Open Source Clinical Record            www.sqlclinic.net
------------------------------------------------------------------------




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Cleaning up template 1 -> template0 cloning
@ 2002-04-04 01:45  Josh Berkus <josh@agliodbs.com>
  parent: Thomas Good <tomg@admin.nrnet.org>
  0 siblings, 2 replies; 4+ messages in thread

From: Josh Berkus @ 2002-04-04 01:45 UTC (permalink / raw)
  To: Thomas Good <tomg@admin.nrnet.org>; Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql

Thomas, folks,

>      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 and the default database-to-connect-to
>  for
>      various scripts such as createdb. 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.

Help!  The above documentation seems to be incorrect.

If I try to connect to Template0:
psql: FATAL 1:  Database "template0" is not currently accepting
 connections

If I try to drop template1 from psql:
ERROR:  DROP DATABASE: database is marked as a template

If I try "dropdb template1":
ERROR:  DROP DATABASE: cannot be executed on the currently open
 database
dropdb: database removal failed

Trying to remove template1 to restore it using an undamaged template0
 seems to be a catch-22 with no way out.  Help!

-Josh Berkus



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Cleaning up template 1 -> template0 cloning
@ 2002-04-04 01:58  Josh Berkus <josh@agliodbs.com>
  parent: Josh Berkus <josh@agliodbs.com>
  1 sibling, 0 replies; 4+ messages in thread

From: Josh Berkus @ 2002-04-04 01:58 UTC (permalink / raw)
  To: Thomas Good <tomg@admin.nrnet.org>; Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql

Folks,

> Help!  The above documentation seems to be incorrect.

Sorry about the panic, here.  It turns out that it is possible to do as
 suggested to restore template1, provided that one is prepared to
 manipulate the datallowconn and datistemplate flags in template0 and
 template1.

Template1 is restored.  E-mail me if any of you ever have to do the
 same.  In fact, I'll write it up ...

-Josh Berkus



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Cleaning up template 1 -> template0 cloning
@ 2002-04-04 03:33  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Josh Berkus <josh@agliodbs.com>
  1 sibling, 0 replies; 4+ messages in thread

From: Tom Lane @ 2002-04-04 03:33 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; +Cc: Thomas Good <tomg@admin.nrnet.org>; pgsql-sql

"Josh Berkus" <josh@agliodbs.com> writes:
> Trying to remove template1 to restore it using an undamaged template0
>  seems to be a catch-22 with no way out.  Help!

There are defenses in place that are intended to prevent you from
accidentally shooting yourself in the foot.  Quoting from the source:

     * 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 ...

Offhand I think you have to (a) make sure you have a spare database
available to issue the DROP DATABASE from, and (b) clear the
datistemplate flag from template1's pg_database row.

("Ensign, engage primary destructor beam" ... "Engaged" ... "Fire!")

After you recreate a fresh template1 you should of course set
datistemplate for it, else non-superusers will have difficulty
creating new databases.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2002-04-04 03:33 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-03-25 21:00 Re: Cleaning up template 1 -> template0 cloning Thomas Good <tomg@admin.nrnet.org>
2002-04-04 01:45 ` Josh Berkus <josh@agliodbs.com>
2002-04-04 01:58   ` Josh Berkus <josh@agliodbs.com>
2002-04-04 03:33   ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox