Postgresql Pg_database - datistemplate,datallowconn

Two useful flags exist in pg_database for each database: 
the columns 

1. datistemplate
2. datallowconn

1.datistemplate:
datistemplate can be set to indicate that a database is intended as a template for CREATE DATABASE. If this flag is set, the database can be cloned by any user with CREATEDB privileges; if it is not set, only superusers and the owner of the database can clone it. 
Both template0 and template1 should always be marked with datistemplate = true.
To delete template1, it must have pg_database.datistemplate = false.
2.datallowconn:
If datallowconn  is false, then no new connections to that database will be allowed (but existing sessions are not terminated simply by setting the flag false). 
The template0 database is normally marked datallowconn = false to prevent its modification. 

PRACTICAL FOR PG_DATABASE(DATISTEMPLATE,DATAALLOWCONN):
DATISTEMPLATE=TRUE/FALSE:

postgres=# select datname,datistemplate,datallowconn from pg_database;
    datname    | datistemplate | datallowconn 
---------------+---------------+--------------
 template1     | t             | t
 template0     | t             | f
 postgres      | f             | t
 account       | f             | t
 temp0copy     | f             | t
 temp0copy2    | f             | t
 postgresclone | f             | t
 temp0         | f             | t
 temp0_1       | f             | t
 temp0db       | f             | t
 temp1db       | f             | t
 temp1         | f             | t
(12 rows)

• if we want to drop any database means you need to set datistemplate=false
• if you want to connect to template0 you need to set dataallowcomm=true

--when you drop template1 database you cannot drop becouse datistemplate=true

postgres=# drop database template1;
ERROR:  cannot drop a template database

--changing datistemplate values

postgres=# UPDATE pg_database SET datistemplate='false' WHERE datname='template1';
UPDATE 1

--Now check the datistemplate value 

postgres=# select datname,datistemplate,datallowconn from pg_database;          
    datname    | datistemplate | datallowconn 
---------------+---------------+--------------
 template0     | t             | f
 postgres      | f             | t
 account       | f             | t
 temp0copy2    | f             | t
 postgresclone | f             | t
 temp0         | f             | t
 temp0_1       | f             | t
 temp0db       | f             | t
 temp1db       | f             | t
 temp1         | f             | t
 template1     | f             | t
(11 rows)

--Now drop the template1 database

postgres=# drop database template1;
DROP DATABASE

Recreating template1 database from template0
DATALLOWCONN=TRUE/FALSE:
--Check the datistemplate value

postgres=# select datname,datistemplate,datallowconn from pg_database;          
    datname    | datistemplate | datallowconn 
---------------+---------------+--------------
 template0     | t             | f
 postgres      | f             | t
 account       | f             | t
 temp0copy2    | f             | t
 postgresclone | f             | t
 temp0         | f             | t
 temp0_1       | f             | t
 temp0db       | f             | t
 temp1db       | f             | t
 temp1         | f             | t
 template1     | f             | t
(11 rows)

--the above table datallow connection is false on template0 database so we cannot connect to template0 databse we already disscussed above example if we want to connect template0 database you need to set datallowconn=true

template1=# \c template0
FATAL:  database "template0" is not currently accepting connections
Previous connection kept

--Changing datallowconn to true

postgres=# UPDATE pg_database SET datallowconn='true' WHERE datname='template0';
UPDATE 1

--Now check the datallowconn value

postgres=# select datname,datistemplate,datallowconn from pg_database;          
    datname    | datistemplate | datallowconn 
---------------+---------------+--------------
 postgres      | f             | t
 account       | f             | t
 temp0copy2    | f             | t
 postgresclone | f             | t
 temp0         | f             | t
 temp0_1       | f             | t
 temp0db       | f             | t
 temp1db       | f             | t
 temp1         | f             | t
 template1     | f             | t
 template0     | t             | t
(11 rows)

--Now you can connect template0 database and also you can create database objects

template1=# \c template0
You are now connected to database "template0" as user "postgres".

template0=# create table ti(id int);
CREATE TABLE