PostgreSQL删除与创建模板数据库

模板数据库"/>

PostgreSQL删除与创建模板数据库

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

highgo=# \lList of databasesName    | Owner  | Encoding | Collate | Ctype | Access privileges 
-----------+--------+----------+---------+-------+-------------------highgo    | highgo | UTF8     | C       | C     | template0 | highgo | UTF8     | C       | C     | =c/highgo        +|        |          |         |       | highgo=CTc/highgotemplate1 | 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=# \lList of databasesName    | 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=#