一文搞懂 PostgreSQL 中的 template1、template0 和 postgres 系統數據庫
- 系統數據庫 ========
細心的你一定發現，當使用 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/post
gres}
13579 | template0 | 10 | 6 | en_US.UTF-8 | en_US.UTF-8 | t | f | -1 | 13579 | 478 | 1 | 1663 | {=c/postgres,postgres=CTc/post
gres}
(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 13
create 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 TABLE
template1=# \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 1
template1=# INSERT INTO TEST(name) VALUES ('2');
INSERT 0 1
template1=# INSERT INTO TEST(name) VALUES ('3');
INSERT 0 1
template1=# INSERT INTO TEST(name) VALUES ('4');
INSERT 0 1
template1=# 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 DATABASE
template1=# \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刪除示意圖如下圖：
若將字段 datistemplate 修改爲 f，則可以進行刪除。
本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/ctHVl4twt2W7D0nc4mpqOw