Re: restoring template1 - Mailing list pgsql-admin

On Wednesday 13 February 2002 16:03, Tom Lane wrote:
> Roy Cabaniss <rcaban@cabanisspc.uamont.edu> writes:
> > So how can I get the original template1 back from template0

Just to help others who find themself in a similar situation to me.

To recap.  I accidently overwrote my template1 when doing a restore and now
it is full of things I do NOT want in a template.  Non-unique sequences.
Indexes, and tables.

Tom Lane gave the following instructions.:

> DROP DATABASE; CREATE DATABASE.   Note the comments in

Ok.. I went psql template1 (because you have to be in a database to use the
postgres commands and got the following error.

template1=# DROP DATABASE template1;
ERROR:  DROP DATABASE: cannot be executed on the currently open database

Silly me... that makes sense... I can't drop a database I am in the middle
of.  Quit and go into another database called southern.

southern=# DROP DATABASE template1;
ERROR:  DROP DATABASE: database is marked as a template

ok, things are a little more serious.  I still have the corrupted template1.

http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/managing-databases.html#MANAGE-AG-TEMPLATEDBS
does not seem to address the problems I am having.  It assumes that you can
drop the database in the first place.

Let's try another tack.  open phpPgAdmin.  using both 2.4-beta.1 and 2.3.1 In
the much older versions it used to show the template database.  It no longer
does.  New and improved.  Darn.

Open Webmin.  Aha.  It does show template one.  Drop the database.  Nope it
does not allow that.  Ok.  I Can see the database however.  Drop every table
that I accidently put into template1.  That it allows.  Better.  But webmin
does not show sequences, indexes or that other things I filled template1 with
by accident.  So I cannot delete them.  Grrrr.

back into psql template1

delete every sequence manually
delete everything else I added manually.

at last a pristine template.

That is what I ended up having to do.  I am sure that the drop database /
create database sequence will work.  But the manual does not say how to
unmark something as a template so it can be dropped.  Not that I found.


--
Dr. Roy F. Cabaniss
Associate Professor of Business
University of Arkansas Monticello
http://cabanisspc.uamont.edu/~rcaban