Thread: Cleaning up template 1
Folks, Due to an accident with a script, I created about 200 objects in thetemplate1 database instead of the database for whichthey wereintended. I am now faced with either deleting each of these object individually,or dumping my whole postgresql installation and re-installingeachuser database, one at a time. Can anyone suggest any shortcuts for fixing this problem? -Josh
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 >
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 ------------------------------------------------------------------------
Josh - from the pg docs: Note: template1 and template0 do not have any special status beyond the fact that the nametemplate1 is the default source database name for CREATE DATABASE and the default database-to-connect-to for variousscripts such as createdb. For example, one could drop template1 and recreate it from template0 without any illeffects. This course of action might be advisable if one has carelessly added a bunch of junk in template1. ------------------------------------------------------------------------ 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 ------------------------------------------------------------------------
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
> 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.
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.
Folks, Thanks so much for all the advice! This will save me a *lot* of time. -Josh
Thomas, folks, > Note: template1 and template0 do not have any special status > beyond > the fact that the name template1 is the default source database > name for CREATE DATABASE and the default database-to-connect-to > for > various scripts such as createdb. For example, one could drop > template1 and recreate it from template0 without any ill > effects. > This course of action might be advisable if one has carelessly > added a bunch of junk in template1. Help! The above documentation seems to be incorrect. If I try to connect to Template0: psql: FATAL 1: Database "template0" is not currently acceptingconnections If I try to drop template1 from psql: ERROR: DROP DATABASE: database is marked as a template If I try "dropdb template1": ERROR: DROP DATABASE: cannot be executed on the currently opendatabase dropdb: database removal failed Trying to remove template1 to restore it using an undamaged template0seems to be a catch-22 with no way out. Help! -Josh Berkus
Folks, > Help! The above documentation seems to be incorrect. Sorry about the panic, here. It turns out that it is possible to do assuggested to restore template1, provided that oneis prepared tomanipulate the datallowconn and datistemplate flags in template0 andtemplate1. Template1 is restored. E-mail me if any of you ever have to do thesame. In fact, I'll write it up ... -Josh Berkus
"Josh Berkus" <josh@agliodbs.com> writes:
> Trying to remove template1 to restore it using an undamaged template0
> seems to be a catch-22 with no way out. Help!
There are defenses in place that are intended to prevent you from
accidentally shooting yourself in the foot. Quoting from the source:
* Disallow dropping a DB that is marked istemplate. This is just to * prevent people from accidentally dropping
template0or template1; * they can do so if they're really determined ...
Offhand I think you have to (a) make sure you have a spare database
available to issue the DROP DATABASE from, and (b) clear the
datistemplate flag from template1's pg_database row.
("Ensign, engage primary destructor beam" ... "Engaged" ... "Fire!")
After you recreate a fresh template1 you should of course set
datistemplate for it, else non-superusers will have difficulty
creating new databases.
regards, tom lane