Difference Between Template 1 And Template 0 In Postgresql

CREATE DATABASE actually works by copying an existing database. 
two defalut database when initializing cluster

1. TEMPLATE1
2. TEMPLATE0

TEMPLATE1:

• By default, it copies the standard system database named template1.
• If you add objects to template1, these objects will be copied into subsequently created user databases.
• if you install the procedural language PL/Perl in template1, it will automatically be available in user databases without any extra action being taken when those databases are created.
• template1 might contain encoding-specific or locale-specific data

TEMPLATE0:

• TEMPLATE1 contains the same data as the initial contents of template1.
• template0 should never be changed after the database cluster has been initialized becouse pg_database.datistemplate = false.
• template0 might not contain encoding-specific or locale-specific data.

Note: template1 and template0 do not have any special status beyond the fact that the name template1 is the default source database name for CREATE DATABASE. For example, one could drop template1 and recreate it from template0 without any ill effects. This course of action might be advisable if one has carelessly added a bunch of junk in template1.(To delete template1, it must have pg_database.datistemplate = false.) 
The postgres database is also created when a database cluster is initialized. This database is meant as a default database for users and applications to connect to. It is simply a copy of template1 and can be dropped and recreated if necessary.
PRACTICAL FOR TEMPLATE0:
--If you try connect template0 database it will throw error because you cannot modify template0 database by default

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

--To create a database by copying template0, use from the SQL environment:

postgres=# create database temp0 template template0;
CREATE DATABASE

--From linux command line mode

[postgres@r1 bin]$ ./createdb -T template0 temp0_1
Password: 

--list the total database

[postgres@r1 bin]$ ./psql -l
Password: 
                                     List of databases
     Name      |  Owner   | Encoding |    Collate     |    Ctype    |   Access privileges   
---------------+----------+----------+----------------+-------------+-----------------------
 account       | u1       | UTF8     | en_US.UTF-8    | en_US.UTF-8 | 
 postgres      | postgres | UTF8     | en_US.UTF-8    | en_US.UTF-8 | 
 postgresclone | clone    | UTF8     | en_US.UTF-   8 | en_US.UTF-8 | 
 temp0         | u2       | UTF8     | en_US.UTF-8    | en_US.UTF-8 | 
 temp0_1       | postgres | UTF8     | en_US.UTF-8    | en_US.UTF-8 | 
 temp0copy     | postgres | UTF8     | en_US.UTF-8    | en_US.UTF-8 | 
 temp0copy2    | postgres | UTF8     | en_US.UTF-8    | en_US.UTF-8 | 
 template0     | postgres | UTF8     | en_US.UTF-8    | en_US.UTF-8 | =c/postgres          +
               |          |          |                |             | postgres=CTc/postgres
 template1     | postgres | UTF8     | en_US.UTF-8    | en_US.UTF-8 | =c/postgres          +
               |          |          |                |             | postgres=CTc/postgres
(9 rows)

PRACTICAL FOR  TEMPLATE1:
--YOU can connect template1 database and you can modify or create  any objects but these objects will be copied into subsequently created user databases

[postgres@r1 bin]$ ./psql -d template1 -U postgres
Password for user postgres: 
psql.bin (9.3.14)
Type "help" for help.

No entry for terminal type "xterm";
using dumb terminal settings.
template1=# dt

--Checking any tables in template1 database

template1-# \dt   
No relations found.

--Now creating objects in template1 database

template1=# create table temp1table(id int);
CREATE TABLE

--then create a new database

template1=# create database temp1db;
CREATE DATABASE

--Now connect the temp1db and check the already created table of "temp1table" on temp1db

temp0db=# create database temp1db;
CREATE DATABASE

temp0db=# \c temp1db
You are now connected to database "temp1db" as user "postgres".

temp1db=# \dt
           List of relations
 Schema |    Name    | Type  |  Owner   
--------+------------+-------+----------
 public | temp1table | table | postgres
(1 row)

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 objests

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

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