PostgreSQL TEMPLATE

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 objects
template1=# \c template0
You are now connected to database "template0" as user "postgres".

template0=# create table ti(id int);
CREATE TABLE
SCENARIO FOR TEMPLATE  Database
It is possible to create additional template databases, and indeed one can copy any database in a cluster by specifying its name as the template for CREATE DATABASE. It is important to understand, however, that this is not (yet) intended as a general-purpose "COPY DATABASE" facility. The principal limitation is that no other sessions can be connected to the source database while it is being copied. CREATE DATABASE will fail if any other connection exists when it starts; during the copy operation, new connections to the source database are prevented.
postgres=# create database temp1 template template1;
ERROR:  source database "template1" is being accessed by other users
DETAIL:  There is 1 other session using the database.
solution:
To clone an existing database with postgres you can do that
--KILL ALL EXISTING CONNECTION FROM ORIGINAL DB (sourcedb)
Syntax:
SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = 'SOURCE_DB' AND pid <> pg_backend_pid();
postgres=# SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = 'template1' AND pid <> pg_backend_pid();
 pg_terminate_backend 
----------------------
 t
(1 row)
--CLONE DATABASE TO NEW ONE(TARGET_DB) 
Syntax:
CREATE DATABASE TARGET_DB WITH TEMPLATE SOURCE_DB OWNER USER_DB;
IT will kill all the connection to the source db avoiding the error
CREATE TABLE employees OF employee_type (
    PRIMARY KEY (name),
    salary WITH OPTIONS DEFAULT 1000
Basic Error:
[root@richards bin]# ./createdb -T template0 temp0
Password: 
Password: 
createdb: could not connect to database template1: FATAL:  password authentication failed for user "root"
Solution:
 you run this commnad as root user that is why you are facing this error so you need to run this above command as postgres user
[root@richards bin]# su postgres
bash-3.2$ cd /opt/PostgreSQL/9.4/bin/

bash-3.2$  ./createdb -T template0 temp0
createdb: database creation failed: ERROR:  database "temp0" already exists
bash-3.2$  ./createdb -T template0 temp01

bash-3.2$ ./psql -l
                                   List of databases
    Name    |  Owner   | Encoding |   Collate   |    Ctype     |   Access privil
eges   
------------+----------+----------+-------------+--------------+----------------
-------
 db_to_drop | postgres | UTF8     | en_US.UTF-8 | en_US.UTF -8 | 
 dinesh     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8  | 
 postgres   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8  | 
 richards   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8  | 
 temp0      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8  | 
 temp01     | 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/po
stgres
 template1  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8  | =c/postgres    
      +
            |          |          |             |              | postgres=CTc/po
stgres
(8 rows)



No comments:

Post a Comment

Subscribe to: Posts (Atom)