Thread: dropping template1

dropping template1

From
"Thomas T. Thai"
Date:
I'm using 7.3 and accidently created unnecessary functions and such in
template1. Originally it had nothing added beyond the initial install
(initdb). I would like to return it to the clean state.

drop database template1;
ERROR:  DROP DATABASE: database is marked as a template

Is there another way?



Re: dropping template1

From
"Shridhar Daithankar"
Date:
On 9 Jan 2003 at 1:14, Thomas T. Thai wrote:

> I'm using 7.3 and accidently created unnecessary functions and such in
> template1. Originally it had nothing added beyond the initial install
> (initdb). I would like to return it to the clean state.

How about drop function?

Bye
 Shridhar

--
Telephone, n.:    An invention of the devil which abrogates some of the advantages
of making a disagreeable person keep his distance.        -- Ambrose Bierce


Re: dropping template1

From
"Thomas T. Thai"
Date:
> On 9 Jan 2003 at 1:14, Thomas T. Thai wrote:
>
>> I'm using 7.3 and accidently created unnecessary functions and such in
>> template1. Originally it had nothing added beyond the initial install
>> (initdb). I would like to return it to the clean state.
>
> How about drop function?

there are lots of functions and datatypes etc. is there an easier way?



Re: dropping template1

From
"Shridhar Daithankar"
Date:
On 9 Jan 2003 at 1:40, Thomas T. Thai wrote:

> > On 9 Jan 2003 at 1:14, Thomas T. Thai wrote:
> >
> >> I'm using 7.3 and accidently created unnecessary functions and such in
> >> template1. Originally it had nothing added beyond the initial install
> >> (initdb). I would like to return it to the clean state.
> >
> > How about drop function?
>
> there are lots of functions and datatypes etc. is there an easier way?

I meant just drop the functions you created.

If you absolutely instist, shutdown postmaster, go to database dir, do a rm -rf
there and doa initdb once again. Clean as slate..

Bye
 Shridhar

--
Beam me up, Scotty, there's no intelligent life down here!


Re: dropping template1

From
Dan Langille
Date:
On Thu, 9 Jan 2003, Shridhar Daithankar wrote:

> On 9 Jan 2003 at 1:40, Thomas T. Thai wrote:
>
> > > On 9 Jan 2003 at 1:14, Thomas T. Thai wrote:
> > >
> > >> I'm using 7.3 and accidently created unnecessary functions and such in
> > >> template1. Originally it had nothing added beyond the initial install
> > >> (initdb). I would like to return it to the clean state.
> > >
> > > How about drop function?
> >
> > there are lots of functions and datatypes etc. is there an easier way?
>
> I meant just drop the functions you created.
>
> If you absolutely instist, shutdown postmaster, go to database dir, do a rm -rf
> there and doa initdb once again. Clean as slate..

My initial concerns about the above advice where related to users and
groups and the potential for loss of that data.  After reading
http://www.us.postgresql.org/users-lounge/docs/7.2/postgres/managing-databases.html
I'm not as worried, but it got me wondering...  Where are the user and
group data held?


Re: dropping template1

From
"Shridhar Daithankar"
Date:
On 9 Jan 2003 at 7:05, Dan Langille wrote:
> > If you absolutely instist, shutdown postmaster, go to database dir, do a rm -rf
> > there and doa initdb once again. Clean as slate..
>
> My initial concerns about the above advice where related to users and
> groups and the potential for loss of that data.  After reading
> http://www.us.postgresql.org/users-lounge/docs/7.2/postgres/managing-databases.html
> I'm not as worried, but it got me wondering...  Where are the user and
> group data held?

Of course in the same place. But if you are feeling lazy to drop each function
by hand, what is the alternative left?

You decide which one is labour intensive? Dropping functions or recreating
users..

Bye
 Shridhar

--
Ingrate, n.:    A man who bites the hand that feeds him, and then complains of
indigestion.


Re: dropping template1

From
Neil Conway
Date:
On Thu, 2003-01-09 at 02:40, Thomas T. Thai wrote:
> there are lots of functions and datatypes etc. is there an easier way?

Try this:

(1) UPDATE pg_database SET datistemplate = false WHERE datname =
'template1';

(2) DROP DATABASE template1;

(3) CREATE DATABASE template1 WITH template template0;

(4) UPDATE pg_database SET datistemplate = true WHERE datname =
'template1';

Cheers,

Neil
--
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC




Re: dropping template1

From
Jochem van Dieten
Date:
Thomas T. Thai wrote:
 >Shridhar Daithankar wrote:
 >>On 9 Jan 2003 at 1:14, Thomas T. Thai wrote:
 >>
 >>>I'm using 7.3 and accidently created unnecessary functions and such i
 >>>template1. Originally it had nothing added beyond the initial install
 >>>(initdb). I would like to return it to the clean state.
 >>
 >>How about drop function?
 >
 > there are lots of functions and datatypes etc. is there an easier way?

Unless you did weird things, all functions and datatypes will be in
non-system schema's. Dropping and recreating the schema's will
implicitly drop the functions and datatypes as well. Don't forget to
vacuum freeze after that.

Jochem


Re: dropping template1

From
"Tom"
Date:
> On Thu, 2003-01-09 at 02:40, Thomas T. Thai wrote:
>> there are lots of functions and datatypes etc. is there an easier way?
>
> Try this:
>
> (1) UPDATE pg_database SET datistemplate = false WHERE datname =
> 'template1';
[...]

That was exactly what I was looking for. Thank you.