pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCleaning 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