pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Arguile <arguile@lucentstudios.com>
To: pgsql-sql@postgresql.org
Cc: Josh Berkus <josh@agliodbs.com>
Subject: Re: Cleaning up template 1
Date: Tue, 26 Mar 2002 08:51:21 -0500
Message-ID: <LLENKEMIODLDJNHBEFBOOEHLEHAA.arguile@lucentstudios.com> (raw)
In-Reply-To: <3C9F93AA.9010405@olemiss.edu>

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





view thread (7+ messages)  latest in thread

Message-ID: <LLENKEMIODLDJNHBEFBOOEHLEHAA.arguile@lucentstudios.com>
Permalink:  ../LLENKEMIODLDJNHBEFBOOEHLEHAA.arguile@lucentstudios.com/
Also on:    postgresql.org/message-id/LLENKEMIODLDJNHBEFBOOEHLEHAA.arguile@lucentstudios.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: arguile@lucentstudios.com, josh@agliodbs.com
  Subject: Re: Cleaning up template 1
  In-Reply-To: <LLENKEMIODLDJNHBEFBOOEHLEHAA.arguile@lucentstudios.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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