Difference Between Template 1 And Template 0 In Postgresql
CREATE DATABASE actually works by copying an existing database.
two defalut database when initializing cluster
- TEMPLATE1
- TEMPLATE0
- 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
- 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.
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)
--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 databasetemplate1-# \dt
No relations found.
--Now creating objects in template1 databasetemplate1=# create table temp1table(id int);
CREATE TABLE
--then create a new databasetemplate1=# create database temp1db;
CREATE DATABASE
--Now connect the temp1db and check the already created table of "temp1table" on temp1dbtemp0db=# 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
- datistemplate
- datallowconn
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
postgres=# drop database template1;
ERROR: cannot drop a template database
--changing datistemplate valuespostgres=# 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 databasepostgres=# drop database template1;
DROP DATABASE
Recreating template1 database from template0DATALLOWCONN=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=truetemplate1=# \c template0
FATAL: database "template0" is not currently accepting connections
Previous connection kept
--Changing datallowconn to truepostgres=# UPDATE pg_database SET datallowconn='true' WHERE datname='template0';
UPDATE 1
--Now check the datallowconn valuepostgres=# 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 objeststemplate1=# \c template0
You are now connected to database "template0" as user "postgres".
template0=# create table ti(id int);
CREATE TABLE
Comments
Post a Comment