一文搞懂PostgreSQL中的template1、template0和postgres系统数据库

开源软件联盟PostgreSQL分会 2021-05-19

4807

文章转载自公众号：君子黎

作者：圣手书生

1. 系统数据库

细心的你一定发现，当使用initdb命令初始化数据库集簇并且pg_ctl启动服务之后，该PostgreSQL中默认就会存在3个数据库，它们分别是：template1、template0和postgres. 这三个数据库在PostgreSQL中也被称为“系统数据库”。如下图所示：

这三个数据库之间有一定区别，也有一定联系。让我们继续往下看。

1.1 template1，template0和postgres

为了更加直观、形象地说明3个默认数据库之间的异同，让我们先从一个数据库本身具有哪些属性（字段）、以及各自代表意义说起。PostgreSQL提供了一个名为pg_database的表，它可在所有数据库之间共享。通过查询该表，可以得到一个数据库的完整属性信息。如下：

该表中各字段所代表的含义参考下图：

该表结构是在schemapg.h文件中进行声明的，它由genbki.pl文件相关函数API生成，并最终由relcache.c文件使用。

#define Schema_pg_database \{ 1262, {"oid"}, 26, -1, 4, 1, 0, -1, -1, true, 'p', 'i', true, false, false, '\0', '\0', false, true, 0, 0 }, \{ 1262, {"datname"}, 19, -1, NAMEDATALEN, 2, 0, -1, -1, false, 'p', 'c', true, false, false, '\0', '\0', false, true, 0, 950 }, \{ 1262, {"datdba"}, 26, -1, 4, 3, 0, -1, -1, true, 'p', 'i', true, false, false, '\0', '\0', false, true, 0, 0 }, \{ 1262, {"encoding"}, 23, -1, 4, 4, 0, -1, -1, true, 'p', 'i', true, false, false, '\0', '\0', false, true, 0, 0 }, \{ 1262, {"datcollate"}, 19, -1, NAMEDATALEN, 5, 0, -1, -1, false, 'p', 'c', true, false, false, '\0', '\0', false, true, 0, 950 }, \{ 1262, {"datctype"}, 19, -1, NAMEDATALEN, 6, 0, -1, -1, false, 'p', 'c', true, false, false, '\0', '\0', false, true, 0, 950 }, \{ 1262, {"datistemplate"}, 16, -1, 1, 7, 0, -1, -1, true, 'p', 'c', true, false, false, '\0', '\0', false, true, 0, 0 }, \{ 1262, {"datallowconn"}, 16, -1, 1, 8, 0, -1, -1, true, 'p', 'c', true, false, false, '\0', '\0', false, true, 0, 0 }, \{ 1262, {"datconnlimit"}, 23, -1, 4, 9, 0, -1, -1, true, 'p', 'i', true, false, false, '\0', '\0', false, true, 0, 0 }, \{ 1262, {"datlastsysoid"}, 26, -1, 4, 10, 0, -1, -1, true, 'p', 'i', true, false, false, '\0', '\0', false, true, 0, 0 }, \{ 1262, {"datfrozenxid"}, 28, -1, 4, 11, 0, -1, -1, true, 'p', 'i', true, false, false, '\0', '\0', false, true, 0, 0 }, \{ 1262, {"datminmxid"}, 28, -1, 4, 12, 0, -1, -1, true, 'p', 'i', true, false, false, '\0', '\0', false, true, 0, 0 }, \{ 1262, {"dattablespace"}, 26, -1, 4, 13, 0, -1, -1, true, 'p', 'i', true, false, false, '\0', '\0', false, true, 0, 0 }, \{ 1262, {"datacl"}, 1034, -1, -1, 14, 1, -1, -1, false, 'x', 'i', false, false, false, '\0', '\0', false, true, 0, 0 }

三个文件之间的调用示意图如下：

在这里，我们重点关注pg_database中的三个字段：datname、datistemplate和datallowconn. 其中datname指定数据库名，datistemplate指明当前数据库是否为模板数据库（t-是，f-否）；datallowconn表示当前数据库是否允许用户连接登录。

对于template0、template1和postgres三个系统数据库，它们具有以下几个特点：

template0和template1数据库的datistemplate字段值是t，而postgres数据库的datistemplate字段值是f。表明template[0，1]这两个数据库是模板数据库，而postgres非模板数据库。
postgres和template1数据库的datallowconn字段为t，而template0数据库f。表明数据库postgres和template1是允许用户（包括psql）连接，而template0不允许连接。

比如当我们尝试连接template0数据库时候，会报错：

[root@Thor postgresql-13.2]# psql -p 9998 -U postgres -d template0;psql: error: FATAL:  database "template0" is not currently accepting connections

连接示意图如下：

postgres数据库是应用程序连接的默认数据库。它只是模板数据库template1的一个副本，如有必要，可以将其删除并重新创建。
CREATE DATABASE语句创建数据库时，实际上是通过复制template1模板数据库得到。

1.1.1 使template0可连接

默认情况下，template0模板数据库不接受用户连接。但是，我们可以通过修改（UPDATE）其字段（datallowconn）值为t，使其接收用户连接。如下：

template1=# select *from pg_database;  oid  |  datname  | datdba | encoding | datcollate  |  datctype   | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfrozenxid | datminmxid | dattablespace |               datacl-------+-----------+--------+----------+-------------+-------------+---------------+--------------+--------------+---------------+--------------+------------+---------------+------------------------------------- 13580 | postgres  |     10 |        6 | en_US.UTF-8 | en_US.UTF-8 | f             | t            |           -1 |         13579 |          478 |          1 |          1663 |     1 | template1 |     10 |        6 | en_US.UTF-8 | en_US.UTF-8 | t             | t            |           -1 |         13579 |          478 |          1 |          1663 | {=c/postgres,postgres=CTc/postgres} 13579 | template0 |     10 |        6 | en_US.UTF-8 | en_US.UTF-8 | t             | f            |           -1 |         13579 |          478 |          1 |          1663 | {=c/postgres,postgres=CTc/postgres}(3 rows)template1=# update  pg_database set datallowconn = 't' where oid = 13579;UPDATE 1[root@Thor postgresql-13.2]# psql -p 9998 -U postgres -d template0;psql (13.2)Type "help" for help.

当修改了datallowconn字段为t之后，现在psql命令（用户）可以登录template0数据库。但是不建议这么做，因为你不能保证永不会对该模板数据库中的数据信息作出修改。

注意：不能删除用户当前连接到的数据库。

1.2 系统表的定义

除了上面用于查看数据库的pg_database表之外，还有pg_class（查看表结构体）、pg_type（查看数据基本类型）、pg_proc（存储关于函数信息）、pg_attribute（存储关于表字段信息）等等。这些系统表的生成过程大致是：

所有系统表定义通过Catalog.pm来转为perl中的数据结构，最后通过genbki.pl脚本转换为postgres.bki文件。而bki文件则是用于初始化PostgreSQL目标数据库。

转换示意图如下：

其中postgres.bki文件位于源码安装时所指定（—prefix参数）目录路径的share目录执行。如下图所：

对于postgres.bki中的文件，其内容格式如下：

# PostgreSQL 13create pg_proc 1255 bootstrap rowtype_oid 81 ( oid = oid , proname = name , . . .  //省略若干 proacl = _aclitem ). . .  //省略若干insert ( 33 charout 11 10 12 1 0 0 0 f f f t f i s 1 0 2275 18 _null_ _null_ _null_ _null_ _null_ charout _null_ _null_ _null_ )

当使用initdb来初始化数据库集簇时候，有相应函数API来读取该文件并进行执行创建template1模板数据库。其整个创建流程图大致如下：

对于模板数据库tempate1创建过程的更多细节，阅读initdb.c文件，主要由函数bootstrap_template1()完成。

1.3 template1作为默认模板

当用户创建数据库时，在不特意指定（createdb -T 模板数据库名、 CREATE DATABASE WITH 模板数据库名）模板数据库源的情况下，默认是从template1模板数据库中进行拷贝。因此，template1模板数据库中的原有的所有数据表、表、索引和函数等等都会被新创建的数据库给继承。

（1）将当前数据库切换到template1中。

postgres=# \c template1;You are now connected to database "template1" as user "postgres".

（2）在template1中创建名为TEST的数据表。

template1=# CREATE TABLE TEST(id SERIAL PRIMARY KEY, name VARCHAR(20));CREATE TABLEtemplate1=# \d+                                  List of relations Schema |    Name     |   Type   |  Owner   | Persistence |    Size    | Description--------+-------------+----------+----------+-------------+------------+------------- public | test        | table    | postgres | permanent   | 0 bytes    | public | test_id_seq | sequence | postgres | permanent   | 8192 bytes | (2 rows)

（3）向表中插入5条记录。

template1=# INSERT INTO TEST(name) VALUES ('1');INSERT 0 1template1=# INSERT INTO TEST(name) VALUES ('2');INSERT 0 1template1=# INSERT INTO TEST(name) VALUES ('3');INSERT 0 1template1=# INSERT INTO TEST(name) VALUES ('4');INSERT 0 1template1=# INSERT INTO TEST(name) VALUES ('5');INSERT 0 1

（4）查看TEST表信息。

template1=# SELECT *FROM TEST; id | name----+------  1 | 1  2 | 2  3 | 3  4 | 4  5 | 5(5 rows)

（5）现在重新创建一个名为db_test的数据库，然后切换到db_test数据库中，实用\d+命令查看当前数据库下的表信息时，可看到有一个名为test的数据表。该表是从template1目模板数据库中继承过来，包括里面的数据。正如前面所言，CREATE DATABASE（createdb原理都一样）创建数据库时候，默认情况下会继承template1中的所有数据信息。

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=# CREATE DATABASE db_test;CREATE DATABASEtemplate1=# \c db_test ;You are now connected to database "db_test" as user "postgres".db_test=# \d+                                  List of relations Schema |    Name     |   Type   |  Owner   | Persistence |    Size    | Description--------+-------------+----------+----------+-------------+------------+------------- public | test        | table    | postgres | permanent   | 8192 bytes | public | test_id_seq | sequence | postgres | permanent   | 8192 bytes |(2 rows)db_test=# SELECT *FROM test; id | name----+------  1 | 1  2 | 2  3 | 3  4 | 4  5 | 5(5 rows)

模板数据库template0除了不是创建数据库默认的源数据库模板之外，tempate1和template0没有任何其他的特殊状态。比如我们可以删除template1并从template0中重新创建它，而不会产生任何其他不良的影响。会不会有读者会疑惑？template0是不是显得有些多余。其实不然，因为template1默认支持用户连接，那么就有可能会面临着其数据信息被不小心篡改的风险。因此，PostgreSQL为了满足能够给用户一个干净（也就是最原始）的数据库需求，当需要时候可用指定从template0模板数据库中去继承。

1.4 删除模板

PostgreSQL中这三个系统数据库都是可删除的，但是若该数据库为模板数据库，则不支持删除（除非手动修改字段datistemplate的值为f）。在datistemplate字段为t时，若尝试删除该模板数据库，则会报错。

template1=# select  datistemplate from pg_database where datname = 'template1'; datistemplate--------------- t(1 row)template1=# DROP DATABASE template1;
ERROR:  cannot drop a template database

删除示意图如下图：