Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

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

Comments

Popular posts from this blog

PostgreSQL Pgbadger Installation On Linux

PostgreSQL Sequence

How to configure Replication Manager (repmgr) ?

Postgresql maximum size

PostgreSQL pgBadger