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

* Cleaning up template 1
@ 2002-03-25 20:05  Josh Berkus <josh@agliodbs.com>
  0 siblings, 2 replies; 7+ messages in thread

From: Josh Berkus @ 2002-03-25 20:05 UTC (permalink / raw)
  To: pgsql-sql

Folks,

Due to an accident with a script, I created about 200 objects in the
 template1 database instead of the database for which they were
 intended.  
I am now faced with either deleting each of these object individually,
 or dumping my whole postgresql installation and re-installing each
 user database, one at a time.

Can anyone suggest any shortcuts for fixing this problem?

-Josh



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

* Re: Cleaning up template 1
@ 2002-03-25 20:52  Thomas Swan <tswan@olemiss.edu>
  parent: Josh Berkus <josh@agliodbs.com>
  1 sibling, 1 reply; 7+ messages in thread

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

Josh Berkus wrote:

>Folks,
>
>Due to an accident with a script, I created about 200 objects in the
> template1 database instead of the database for which they were
> intended.  
>I am now faced with either deleting each of these object individually,
> or dumping my whole postgresql installation and re-installing each
> user database, one at a time.
>
If this is 7.2 you should be able to drop template1 and do create 
database template1 from template0.  

DROP DATABASE template1;
CREATE DATABASE template1 WITH TEMPLATE=template0;

Check first with another installation as I'm not too sure about the 
ramifications.

>
>
>Can anyone suggest any shortcuts for fixing this problem?
>
>-Josh
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/users-lounge/docs/faq.html
>






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

* Re: Cleaning up template 1
@ 2002-03-25 20:58  Thomas Good <tomg@admin.nrnet.org>
  parent: Josh Berkus <josh@agliodbs.com>
  1 sibling, 0 replies; 7+ messages in thread

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

On Mon, 25 Mar 2002, Josh Berkus wrote:

> Folks,
> 
> Due to an accident with a script, I created about 200 objects in the
>  template1 database instead of the database for which they were
>  intended.  
> I am now faced with either deleting each of these object individually,
>  or dumping my whole postgresql installation and re-installing each
>  user database, one at a time.
> 
> Can anyone suggest any shortcuts for fixing this problem?

Josh,

Isn't template0 around for events like this?  (maybe if template1 
goes missing, etc...)

I read somewhere that you can drop template1 and recreate it from 
template0...

Tom

------------------------------------------------------------------------
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] 7+ messages in thread

* Re: Cleaning up template 1
@ 2002-03-25 21:16  Thomas Swan <tswan@olemiss.edu>
  parent: Thomas Swan <tswan@olemiss.edu>
  0 siblings, 1 reply; 7+ messages in thread

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

Thomas Swan wrote:

> Josh Berkus wrote:
>
>> Folks,
>>
>> Due to an accident with a script, I created about 200 objects in the
>> template1 database instead of the database for which they were
>> intended.  I am now faced with either deleting each of these object 
>> individually,
>> or dumping my whole postgresql installation and re-installing each
>> user database, one at a time.
>>
> If this is 7.2 you should be able to drop template1 and do create 
> database template1 from template0. 
> DROP DATABASE template1;
> CREATE DATABASE template1 WITH TEMPLATE=template0;
>
> Check first with another installation as I'm not too sure about the 
> ramifications. 

If you installed languages on template1 for defaults you will need to 
recreate them for the new template1 database.  I forgot template0 was 
completely empty.  

>
>
>>
>>
>> Can anyone suggest any shortcuts for fixing this problem?
>>
>> -Josh
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 5: Have you checked our extensive FAQ?
>>
>> http://www.postgresql.org/users-lounge/docs/faq.html
>>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org







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

* Re: Cleaning up template 1
@ 2002-03-26 13:51  Arguile <arguile@lucentstudios.com>
  parent: Thomas Swan <tswan@olemiss.edu>
  0 siblings, 2 replies; 7+ messages in thread

From: Arguile @ 2002-03-26 13:51 UTC (permalink / raw)
  To: pgsql-sql; +Cc: Josh Berkus <josh@agliodbs.com>

> Thomas Swan wrote:
>
> > Josh Berkus wrote:
> >
> >> Folks,
> >>
> >> Due to an accident with a script, I created about 200 objects in the
> >> template1 database instead of the database for which they were
> >> intended.  I am now faced with either deleting each of these object
> >> individually,
> >> or dumping my whole postgresql installation and re-installing each
> >> user database, one at a time.
> >>
> > If this is 7.2 you should be able to drop template1 and do create
> > database template1 from template0.
> > DROP DATABASE template1;
> > CREATE DATABASE template1 WITH TEMPLATE=template0;
> >
> > Check first with another installation as I'm not too sure about the
> > ramifications.
>
> If you installed languages on template1 for defaults you will need to
> recreate them for the new template1 database.  I forgot template0 was
> completely empty.

I often have many extra languages and procs loaded into template1 so would
be loathe to create it from template0 again. If by 'objects' you're refering
to user tables, views, and sequences:

CREATE OR REPLACE FUNCTION clean() RETURNS BOOL AS '
DECLARE
  obj RECORD;
BEGIN
  FOR obj IN SELECT relname AS name,
                    (CASE WHEN relkind = ''v'' THEN ''VIEW''
                          WHEN relkind = ''r'' THEN ''TABLE''
                          ELSE                      ''SEQUENCE''
                     END) AS type
             FROM pg_class
             WHERE relkind IN (''v'',''r'',''S'')
               AND relname !~ ''pg_''
  LOOP
    EXECUTE ''DROP '' || obj.type || '' '' || obj.name;
    RAISE NOTICE ''DROP % %'', obj.type, obj.name;
  END LOOP;
  RETURN true;
END;
' LANGUAGE 'plpgsql';

Something like this will do. Just a quick function, it'll drop all the
aforementioned user objects and echo the statements back to you (the return
value is meaningless).

If it's lots of other things added like procs, etc. it may just be faster to
do as Tom S. advised.





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

* Re: Cleaning up template 1
@ 2002-03-26 14:16  Arguile <arguile@lucentstudios.com>
  parent: Arguile <arguile@lucentstudios.com>
  1 sibling, 0 replies; 7+ messages in thread

From: Arguile @ 2002-03-26 14:16 UTC (permalink / raw)
  To: pgsql-sql

Arguile wrote:
>     EXECUTE ''DROP '' || obj.type || '' '' || obj.name;

This should be:

EXECUTE ''DROP '' || obj.type || '' "'' || obj.name || ''"'';

As the first one experiences case folding. Sorry about that.





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

* Re: Cleaning up template 1
@ 2002-03-26 16:45  Josh Berkus <josh@agliodbs.com>
  parent: Arguile <arguile@lucentstudios.com>
  1 sibling, 0 replies; 7+ messages in thread

From: Josh Berkus @ 2002-03-26 16:45 UTC (permalink / raw)
  To: pgsql-sql

Folks,

Thanks so much for all the advice!  This will save me a *lot* of time.

-Josh



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


end of thread, other threads:[~2002-03-26 16:45 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-03-25 20:05 Cleaning up template 1 Josh Berkus <josh@agliodbs.com>
2002-03-25 20:52 ` Thomas Swan <tswan@olemiss.edu>
2002-03-25 21:16   ` Thomas Swan <tswan@olemiss.edu>
2002-03-26 13:51     ` Arguile <arguile@lucentstudios.com>
2002-03-26 14:16       ` Arguile <arguile@lucentstudios.com>
2002-03-26 16:45       ` Josh Berkus <josh@agliodbs.com>
2002-03-25 20:58 ` Thomas Good <tomg@admin.nrnet.org>

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