Horton
Menu

Thread

  1. dropping template1

    Thomas T. Thai <tom@minnesota.com> — 2003-01-09T07:14:47Z

    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?

  2. Re: dropping template1

    Shridhar Daithankar <shridhar_daithankar@persistent.co.in> — 2003-01-09T07:17:57Z

    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

  3. Re: dropping template1

    Thomas T. Thai <tom@minnesota.com> — 2003-01-09T07:40:01Z

    > 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?

  4. Re: dropping template1

    Shridhar Daithankar <shridhar_daithankar@persistent.co.in> — 2003-01-09T07:43:56Z

    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!

  5. Re: dropping template1

    Dan Langille <dan@langille.org> — 2003-01-09T12:05:18Z

    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?

  6. Re: dropping template1

    Shridhar Daithankar <shridhar_daithankar@persistent.co.in> — 2003-01-09T12:28:35Z

    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.

  7. Re: dropping template1

    Neil Conway <neilc@samurai.com> — 2003-01-09T13:06:06Z

    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

  8. Re: dropping template1

    Jochem van Dieten <jochemd@oli.tudelft.nl> — 2003-01-09T13:22:49Z

    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

  9. Re: dropping template1

    Thomas T. Thai <tom@minnesota.com> — 2003-01-09T16:21:09Z

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