Re: Cleaning up template 1

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