Re: cleaning out template1, and what is template0?

Louis-David Mitterrand <cunctator@apartia.ch> writes:
> It sometimes happens that template1 gets accidentally littered with
> tables and sequences intended for other databases (forget to \connect,
> wrong arg to psql, etc.).

> Is it safe to DROP/CREATE template1 for a radical cleanout or should I
> merely remove cruft by the item?

This is possible in 7.1, but it will *NOT* work in prior releases.
template1 is the template from which CREATE DATABASE works, so once
you drop template1 there is no way to do CREATE DATABASE.  So you'd
better clean out the cruft by hand.

> In Pg-7.1 there is a new template0 db. What is it used for?

template0 is a duplicate of the original template1, which is supposed to
remain ever virgin (warranty void if seal broken ;-)).  7.1's CREATE
DATABASE command has a new option that says which database to clone,
rather than being hardwired to clone template1.  So, if you say

    CREATE DATABASE foo WITH TEMPLATE template0

then you get a virgin database with none of the installation-local stuff
that may have been added to template1.  In particular, you could drop
template1 and then recreate a clean copy this way.

pg_dumpall now knows to use exactly this method when dumping/restoring
databases.  This means that as of 7.1, it's actually reasonable to keep
installation-local stuff in template1 for propagation into new databases.
You won't end up making two copies of such stuff when you dump and
reload, as was likely to happen before.

            regards, tom lane