Re: restoring template1

Hi.

How about this way:

pg_dump -csu <database> >clean_schema.sql

(replace <database> with the name of the database you accidently imported
into template1).

Now open the created file with your favourite text editor and delete
everything after the last "DROP ..." line and execute it in psql via

\i clean_schema.sql

This will drop all tables (including all triggers for that table), indizes,
sequences and functions which don't belong in template1.

Worked for me a few times.

Cheers,
Florian

> -----Original Message-----
> From: pgsql-admin-owner@postgresql.org
> [mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Roy Cabaniss
> Sent: Thursday, February 14, 2002 3:44 PM
> To: pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] restoring template1
>
>
> 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/managi
> ng-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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster