How to drop a template database from PostgreSQL?

Question

postgres=# DROP DATABASE template_postgis;
ERROR:  cannot drop a template database

http://www.postgresql.org/docs/9.1/static/manage-ag-templatedbs.html makes it seem like if I set template_postgis.datistemplate = false, I'll be able to drop it, but I don't know how to set that.

score 88 · Accepted Answer · answered Jul 09 '12 at 03:33

88

postgres=# UPDATE pg_database SET datistemplate='false' WHERE datname='template_postgis';
UPDATE 1
postgres=# DROP DATABASE template_postgis;
DROP DATABASE
postgres=#

answered Jul 09 '12 at 03:33

dbkaplun

3,407
2
26
33

I looked in the script I used that created the template. There was the line `psql -d postgres -c "UPDATE pg_database SET datistemplate='true' WHERE datname='template_postgis';"`. – dbkaplun Jul 09 '12 at 03:35
3

This causes further problems. I made an upgrade from 9.1 -> 9.2 on ubuntu. This created template1 implicitly. I then executed your command and it seems to work. However, now I installed phpPgAdmin and I cannot login, it says : 'FATAL: database "template1" does not exist' – hek2mgl Jan 13 '14 at 11:47
1

To re-create template1: `create database template1 template template0;` `UPDATE pg_database SET datistemplate='true' WHERE datname='template1';` See also: http://pgsql.inb4.se/2009/april/rebuild-template1.html – Sam Watkins Jul 30 '15 at 06:00

VynlJunkie · Answer 2 · 2017-04-04T08:44:32.210

47

You can use the alter database command. Much simpler and safer than upsetting metadata.

postgres=# create database tempDB is_template true;
CREATE DATABASE
postgres=# drop database tempDB;
ERROR:  cannot drop a template database
postgres=# alter database tempDB is_template false;
ALTER DATABASE
postgres=# drop database tempDB;
DROP DATABASE
postgres=#

Documentation

edited Apr 04 '17 at 08:44

answered Aug 09 '16 at 20:05

VynlJunkie

1,953
22
26

2

This solution works and is far less complicated than the selected solution. – Brad Oct 04 '16 at 16:26
@amoe Great spot. Edited to add documentation link – VynlJunkie Apr 04 '17 at 08:45
1

Thank god you exist man. All the other solutions didn't work. Thanks! – koullislp Apr 26 '18 at 16:09
2

This works, but make sure to add single quotes around `'false'` *POSTGRES v10* – Luis Villavicencio May 20 '18 at 00:17

score 0 · Answer 3 · answered Aug 11 '23 at 14:27

In addition to other answers, you can drop template0 and template1 databases which PostgreSQL has by default with the SQLs below:

ALTER DATABASE template0 IS_TEMPLATE FALSE;
DROP DATABASE template0;

ALTER DATABASE template1 IS_TEMPLATE FALSE;
DROP DATABASE template1;

score -1 · Answer 4 · edited Aug 29 '19 at 16:10

-1

update pg_database set datistemplate=false where datname='template0';
update pg_database set datistemplate=false where datname='template1';

....

Creating script to analyze new cluster                      ok
Creating script to delete old cluster                       ok
Checking for hash indexes                                   ok

Upgrade Complete
----------------

edited Aug 29 '19 at 16:10

DieterDP

4,039
2
29
38

answered Aug 29 '19 at 11:20

dba

1

How to drop a template database from PostgreSQL?

4 Answers4

Linked