PostgreSQL中的template0和template1库使用实战

postgres=# \l
         List of databases
 Name | Owner | Encoding | Collate | Ctype | Access privileges 
-----------+----------+----------+-------------+-------------+-----------------------
 postgres | postgres | UTF8  | en_US.UTF-8 | en_US.UTF-8 | =T/postgres   +
   |   |   |    |    | postgres=CTc/postgres
 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
(3 rows)
 
postgres=#

$ psql template1
psql (11.9)
Type "help" for help.
 
template1=# drop database postgres;
DROP DATABASE
template1=# \l
         List of databases
 Name | Owner | Encoding | Collate | Ctype | Access privileges 
-----------+----------+----------+-------------+-------------+-----------------------
 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
(2 rows)
 
template1=# create database postgres;
CREATE DATABASE
template1=# \l
         List of databases
 Name | Owner | Encoding | Collate | Ctype | Access privileges 
-----------+----------+----------+-------------+-------------+-----------------------
 postgres | 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
(3 rows)
 
template1=#

$ psql template1
psql (11.9)
Type "help" for help.
 
template1=# create table my_test_tab(a int);
CREATE TABLE
template1=# create extension hstore;
CREATE EXTENSION
template1=# \dx
       List of installed extensions
 Name | Version | Schema |     Description     
---------+---------+------------+--------------------------------------------------
 hstore | 1.5  | public  | data type for storing sets of (key, value) pairs
 plpgsql | 1.0  | pg_catalog | PL/pgSQL procedural language
(2 rows)
 
template1=#

template1=# create database db_test;
CREATE DATABASE
template1=# \c db_test
You are now connected to database "db_test" as user "postgres".
db_test=# \dx
       List of installed extensions
 Name | Version | Schema |     Description     
---------+---------+------------+--------------------------------------------------
 hstore | 1.5  | public  | data type for storing sets of (key, value) pairs
 plpgsql | 1.0  | pg_catalog | PL/pgSQL procedural language
(2 rows)
 
db_test=# \d
   List of relations
 Schema | Name  | Type | Owner 
--------+-------------+-------+----------
 public | my_test_tab | table | postgres
(1 row)
 
db_test=# 

db_test=# select datname,datallowconn,datistemplate from pg_database order by 3;
 datname | datallowconn | datistemplate
-----------+--------------+---------------
 postgres | t   | f
 db_test | t   | f
 template1 | t   | t
 template0 | f   | t
(4 rows)
 
db_test=#

db_test=# create database db_test_2 template db_test;
CREATE DATABASE
db_test=#

db_test=# create database db_test_2 template db_test;
ERROR: source database "db_test" is being accessed by other users
DETAIL: There is 1 other session using the database.
db_test=#

$ psql -U postgres postgres
postgres=# update pg_database set datistemplate = false where datname='template1';
UPDATE 1
postgres=# drop database template1;
DROP DATABASE
postgres=# create database template1 template=template0;
CREATE DATABASE
postgres=# update pg_database set datistemplate = true where datname='template1';
UPDATE 1
postgres=#