PostgreSQL删除与创建模板数据库

作者：瀚高PG实验室 （Highgo PG Lab）- 徐云鹤
首先查看目前存在的数据库。

highgo=# \l
                          List of databases
   Name    | Owner  | Encoding | Collate | Ctype | Access privileges 
-----------+--------+----------+---------+-------+-------------------
 highgo    | highgo | UTF8     | C       | C     | 
 template0 | highgo | UTF8     | C       | C     | =c/highgo        +
           |        |          |         |       | highgo=CTc/highgo
 template1 | highgo | UTF8     | C       | C     | =c/highgo        +
           |        |          |         |       | highgo=CTc/highgo
(3 rows)

highgo=

尝试删除数据库。

highgo=# drop database template1; 
ERROR:  42809: cannot drop a template database

提示禁止删除一个模板数据库。
查询该数据库的datistemplate项。

highgo=# select datname ,datistemplate from pg_database where datname='template1';
  datname  | datistemplate 
-----------+---------------
 template1 | t
(1 row)

更新该值为假。

highgo=# UPDATE pg_database SET datistemplate=false where datname='template1';
UPDATE 1

再次查询。

highgo=# select datname ,datistemplate from pg_database where datname='template1';
  datname  | datistemplate 
-----------+---------------
 template1 | f
(1 row)

删除数据库。

highgo=# drop database template1; 
DROP DATABASE

以highgo数据库为模板创建数据库tempbak。

highgo=# create database tempbak template highgo;      
CREATE DATABASE

查询该数据库的datistemplate项。

highgo=# select datname ,datistemplate from pg_database where datname='tempbak';
 datname | datistemplate 
---------+---------------
 tempbak | f
(1 row)

更新该列。

highgo=# UPDATE pg_database SET datistemplate=true WHERE datname='tempbak';
UPDATE 1
highgo=#  select datname ,datistemplate from pg_database where datname='tempbak';
 datname | datistemplate 
---------+---------------
 tempbak | t
(1 row)
highgo=# \l
                          List of databases
   Name    | Owner  | Encoding | Collate | Ctype | Access privileges 
-----------+--------+----------+---------+-------+-------------------
 highgo    | highgo | UTF8     | C       | C     | 
 tempbak   | highgo | UTF8     | C       | C     | 
 template0 | highgo | UTF8     | C       | C     | =c/highgo        +
           |        |          |         |       | highgo=CTc/highgo
(3 rows)

尝试删除该数据库。

highgo=# drop database tempbak;
ERROR:  42809: cannot drop a template database
highgo=#