PostgreSQL之template0、template1和postgres库

PostgreSQL运维技术 2021-07-30

5043

1、什么是template0、template1和postgres？

pg安装后会默认附带三个库，分别是postgres、template0、template1。其中，

template0、template1是pg的模板数据库。模板数据库就是创建新database时，pg会基于模板数据库制作一份副本，其中会包含所有的数据库设置和数据库对象。

而postgres是客户端默认连接的数据库。

2、怎么理解模板数据库？

上面我提到创建新database时，pg会基于模板数据库制作一份副本，其中会包含所有的数据库设置和数据库对象。

a.切换到template1, 在其中创建表a_test。

postgres=# \c template1
You are now connected to database "template1" as user "postgres".
template1=# create table a_test ( a int );
CREATE TABLE
template1=# \d
         List of relations
 Schema |  Name  | Type  |  Owner
--------+--------+-------+----------
 public | a_test | table | postgres
(1 row)

如果上述语句成立，那么我接下来的创建的新数据库都应该包含这张表。

验证一下：

template1=# create database testdb;
CREATE DATABASE
template1=# \c testdb
You are now connected to database "testdb" as user "postgres".
testdb=# \d
         List of relations
 Schema |  Name  | Type  |  Owner
--------+--------+-------+----------
 public | a_test | table | postgres
(1 row)

可以发现，新创建的testdb库，默认有a_test这张表。你可能会注意到，我在创建数据库时，用到的语句是“create database testdb”，并没有指定模板数据库，但是新的库，默认继承了template1的数据库对象。

所以，我们可以推断如果不指定template属性，create database默认使用的是template1模板库。

那么既然有了template1，为什么还要有template0呢？两者的区别是什么？

3、template0和template1的区别？

a.两个库在开始时是一样的，但是template0是不允许连接的。如果你想切换到template0会报错。

testdb=# \c template1
You are now connected to database "template1" as user "postgres".
template1=# \c template0
FATAL:  database "template0" is not currently accepting connections
Previous connection kept

因为不能连接，所以也就不可以对它进行修改。所以这样就有个好处，这个模板是最原始最干净的模板，如果template1被破坏了，那么基于template0再做一个副本即可。关于这一点，可以在pg_database的datallowconn字段中看出来，我们发现template0的datallowconn为false，表示不允许连接。

b.使用template1模板库建库时不可指定新的字符集编码和排序规则，但是templat0可以。

template1=# create database testdb1 TEMPLATE template0 ENCODING 'SQL_ASCII';
CREATE DATABASE
template1=# create database testdb1 TEMPLATE template1 ENCODING 'SQL_ASCII';
ERROR:  new encoding (SQL_ASCII) is incompatible with the encoding of the template database (UTF8)
HINT:  Use the same encoding as in the template database, or use template0 as template.

4、可以自定义模板数据库吗？

可以。

create database tmpdb with template template0 lc_collate 'zh_CN.UTF8' lc_ctype 'zh_CN.UTF8' is_template=true;

再查看下pg_database，可以发现tmpdb的datistemplate是true。

testdb2=# select datname,datistemplate,datallowconn from pg_database;
  datname  | datistemplate | datallowconn
-----------+---------------+--------------
 postgres  | f             | t
 template1 | t             | t
 template0 | t             | f
 testdb    | f             | t
 testdb1   | f             | t
 testdb2   | f             | t
 tmpdb     | t             | t

5、普通数据库可以作为模板数据库吗？即实现数据库的复制？

可以。比如如下的用法：

template1=# create database testdb2 TEMPLATE testdb ;
CREATE DATABASE
template1=# \c testdb2
You are now connected to database "testdb2" as user "postgres".
testdb2=# \d
         List of relations
 Schema |  Name  | Type  |  Owner
--------+--------+-------+----------
 public | a_test | table | postgres
(1 row)

可以看到我们可以基于testdb创建testdb2，testdb2中默认继承了testdb中的表a_test。需要注意的在做该操作时源库不可以有连接，复制过程中也不允许连接源库。

6、我们能删除这三个库吗？

(1) 我们能删除postgres吗？

可以。

a.连接数据库：

psql -U postgres -h 127.0.01 -p 5432 -d postgres

b.删除postgres

postgres=# drop database postgres;
ERROR:  cannot drop the currently open database

报错：cannot drop the currently
open
database

原因是我们不能删除用户当前连接的数据库。所以我们可以连接到template1，然后再删除postgres。

c.连接到template1。

psql -U postgres -h 127.0.01 -p 5432 -d template1

d.删除postgres库。

template1=# drop database postgres;
DROP DATABASE

发现可以删除成功。

所以postgres是可以被删除的，但是，这样会有一个问题。客户端默认会连接到postgres库，删除它，将会影响默认客户端连接。

比如psql如果不值得“-d”选项，将会默认连接到postgres库，此时将会提示postgres不存在。

[ops@text ~]$ psql -U postgres -h 127.0.01 -p 5432
Password for user postgres:
psql: error: FATAL:  database "postgres" does not exist

postgres并不是必需的，但是因为很多工具都是默认连接到，所以我们最好不要删它，如果你删了它，可能你用的许多工具都需要进行调整。

我们也可以重新创建postgres库：

template1=# create database postgres;
CREATE DATABASE

(2) 我们能删除template1吗？

试一下：

testdb2=# drop database template1;
ERROR:  cannot drop a template database

报错：ERROR: cannot drop a template database。

那么有没有办法去删除呢？

有。设置pg_database的datistemplate为false。

testdb2=# update pg_database set datistemplate = false where datname = 'template1';
UPDATE 1
testdb2=# drop database template1;
DROP DATABAS

或者：

testdb2=# alter database template1 is_template false;
ALTER DATABASE
testdb2=# drop database template1;
DROP DATABASE

(3) 我们能删除template0吗？

可以。

同样的方法也适用于template0。

testdb2=# update pg_database set datistemplate = false where datname = 'template0';
UPDATE 1
testdb2=# drop database template0;
DROP DATABASE
testdb2=# select datname,datistemplate,datallowconn from pg_database;
  datname  | datistemplate | datallowconn
-----------+---------------+--------------
 postgres  | f             | t
 testdb    | f             | t
 testdb1   | f             | t
 testdb2   | f             | t
 tmpdb     | t             | t
 template1 | t             | t

postgresql

文章转载自PostgreSQL运维技术，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

PostgreSQL之template0、template1和postgres库

评论