Hello, as a suggestion from a friend, I wrote this document. It's sort of a
how-to about how to recreate the template1 database. I was thinking it might
be a good thing to add it somewhere in the official PostgreSQL docs,
specifically I was thinking it would fit perfectly either just after the
section 18.3 (Template Databases) or as a part of it, maybe after the
notice.
At any rate, here is the document I did in the hope that it can be helpful
for someone else. Please, dont hesitate to make some suggestions or
criticisms and feel free to ask for further details.
Sincerelly,
Kronuz.
_
FREE pop-up blocking with the new MSN Toolbar - get it now!
http://toolbar.msn.com/
It is advisable to leave template1 alone and as clean as possible. But some
times it might be that you just added unwanted operators, data types or even
tables to the template1, up to a point where your template1 is a mess and
you are in the need to do some cleaning. In those cases, it's a good thing
to know we have a backup of template1. That's right; tmplate0 is all you
need to recreate the basic needed stuff to be able to run PostgreSQL. Note,
however, that template0 does not hold any data added by contrib modules, and
it holds only the most basic form of a template. If you need to have
contributed modules added to the recreated template1 you'll have to add them
yourself.
Now, for those who want to learn how to recreate template1 from template0
you need to follow the next steps:
1) You can't make these changes if you are not a super user, so first thing,
you need to connect to the database as postgres super user to the template1:
psql template1 postgres
2) To recreate template1, you need to copy all data from the backup template
(i.e. template0), but it's not as easy as it sounds. You can't just simply
connect to the template0 database and make a copy of it. In order to help
you protect yourself, it has been set by default to deny all connections to
it. So first, you have to override this option. To set the template0
database so it can accept connections you need to do the following:
update pg_catalog.pg_database set datallowconn = true where datname =
'template0';
That simply modifies the datallowconn (database allow connection) property
for 'template0' in the PostgreSQL shared catalog, which is always located in
pg_database table, inside the pg_catalog schema. We modified the property
setting it to true, so that it accepts connections. Be warned that at this
point you can do very harmful damage to PostgreSQL if you are not careful,
since the template0 backup is now unprotected and susceptible to damages.
3) The next step is to stop using template1 so it can be deleted. That's
right, we don't need it anymore, and we'll recreate it as a copy of the
template0 backup:
\c tamplate0
4) At this point we are connected to the template0 database and it's now
safe to start the process of recreating template1. As I said, we need to
delete the template1 database, but the template1 database, being a template,
has its datistemplate (database is template) property set in the PostgreSQL
shared catalog. You can't drop template databases, so we need change that
property in order to be able to continue:
update pg_catalog.pg_database set datistemplate = false where datname =
'template1';
and then we can drop the template1 database:
drop database template1;
5) Next we need to copy the backup template0 to template1. Just as we did in
step 3 for our temporary database, now we create a template1 database:
create database template1 with template template0;
6) Note that the template1 database you just created is not a yet a
template. For the final step in the recreation of template1, you need to set
your newly created template1 to really be a template. This is the opposite
to what we did in step 4:
update pg_catalog.pg_database set datistemplate = true where datname =
'template1';
7) Last but not least, it is very important to protect again template0 as
soon as possible, so in this step we are doing exactly the opposite to what
we did in step 2, but first we disconnect from template0 and connect to
template1 (this is optional, but makes sense since you no longer should
access template0):
\c tamplate1
and then, to make template0 inaccessible to new connections, we update the
catalog:
update pg_catalog.pg_database set datallowconn = false where datname =
'template0';
That's it, now you have a fresh template1, a clean virgin template1 ready to
receive new stuff and to be filled with junk.
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
