Thread: How I changed the encoding of template1 after the fact

I wanted to change the encoding of the template1 database, but when I
tried to drop template1, I get the message, "ERROR:  cannot drop a
template database".

The docs
(http://www.postgresql.org/docs/8.1/interactive/manage-ag-templatedbs.html)
say that this is possible, but a user comment on that page says you can't.

Actually, you *can* drop a template database, if you first convert it
into a non-template database, as per
http://techdocs.postgresql.org/techdocs/pgsqladventuresep1.php

Here's a condensed example, in which template1 is recreated to change
its default encoding:

-- Connect as the postgres superuser,
e.g.:

-- psql -U postgres
template1

-- Then
run:


UPDATE pg_database SET datallowconn = TRUE where datname = 'template0';
\c template0
UPDATE pg_database SET datistemplate = FALSE where datname = 'template1';
drop database template1;
create database template1 with template = template0 encoding = 'UNICODE';
UPDATE pg_database SET datistemplate = TRUE where datname = 'template1';
\c template1
UPDATE pg_database SET datallowconn = FALSE where datname = 'template0';

If you just wanted to clone template0, you would leave out the "encoding
= 'UNICODE'" clause.

Gurus, any corrections or safety advisories you care to make?

Kevin Murphy

Kevin Murphy <murphy@genome.chop.edu> writes:
> Here's a condensed example, in which template1 is recreated to change
> its default encoding:

If you're willing to fool around at that level, why not just
"UPDATE pg_database SET datencoding = ..."

The restriction against dropping template databases is specifically
intended to make it difficult for those who don't know what they are
doing to shoot themselves in the foot.  So I'm disinclined to make the
manual include easy-to-follow directions for bypassing the safety
interlock --- if you can't be bothered to find out how, you shouldn't
be doing it.

            regards, tom lane