Postgresql 中文操作指南

23.3. Template Databases #

CREATE DATABASE 实际上，是通过复制现有数据库来实现的。默认情况下，它复制名为 template1 的标准系统数据库。因此，该数据库是制作新数据库的“模板”。如果将对象添加到 template1 中，则这些对象将复制到随后创建的用户数据库中。这种行为允许对数据库中标准对象集进行站点本地修改。例如，如果在 template1 中安装 PL/Perl 过程语言，则在创建这些数据库时，无需采取任何额外操作，就可以自动在用户数据库中使用它。

CREATE DATABASE actually works by copying an existing database. By default, it copies the standard system database named template1. Thus that database is the “template” from which new databases are made. If you add objects to template1, these objects will be copied into subsequently created user databases. This behavior allows site-local modifications to the standard set of objects in databases. For example, if you install the procedural language PL/Perl in template1, it will automatically be available in user databases without any extra action being taken when those databases are created.

但是，CREATE DATABASE 不会复制附加到源数据库的数据库级 GRANT 权限。新数据库具有默认的数据库级权限。

However, CREATE DATABASE does not copy database-level GRANT permissions attached to the source database. The new database has default database-level permissions.

有一个名为 template0 的第二个标准系统数据库。此数据库包含与 template1 的初始内容相同的数据，即只包含 PostgreSQL 版本预定义的标准对象。在初始化数据库集群后，绝不应更改 template0。通过指示 CREATE DATABASE 复制 template0 而不是 template1，您可以创建一个“原始”用户数据库（其中不存在用户定义的对象，并且系统对象尚未更改），其中不包含 template1 中任何站点本地添加的内容。在还原 pg_dump 转储时，这特别方便：转储脚本应还原到原始数据库中，以确保重新创建转储数据库的正确内容，而不与稍后可能添加到 template1 中的对象冲突。

There is a second standard system database named template0. This database contains the same data as the initial contents of template1, that is, only the standard objects predefined by your version of PostgreSQL. template0 should never be changed after the database cluster has been initialized. By instructing CREATE DATABASE to copy template0 instead of template1, you can create a “pristine” user database (one where no user-defined objects exist and where the system objects have not been altered) that contains none of the site-local additions in template1. This is particularly handy when restoring a pg_dump dump: the dump script should be restored in a pristine database to ensure that one recreates the correct contents of the dumped database, without conflicting with objects that might have been added to template1 later on.

复制 template0 而不是 template1 的另一个常见原因是，在复制 template0 时可以指定新的编码和区域设置，而复制 template1 必须使用它所做的相同设置。这是因为 template1 可能包含特定于编码或特定于区域设置的数据，而 template0 已知不包含此类数据。

Another common reason for copying template0 instead of template1 is that new encoding and locale settings can be specified when copying template0, whereas a copy of template1 must use the same settings it does. This is because template1 might contain encoding-specific or locale-specific data, while template0 is known not to.

要通过复制 template0 创建数据库，请使用：

To create a database by copying template0, use:

CREATE DATABASE dbname TEMPLATE template0;

在 SQL 环境中，或者：

from the SQL environment, or:

createdb -T template0 dbname

来自外壳。

from the shell.

可以创建其他模板数据库，事实上，可以通过指定其名称作为 CREATE DATABASE 的模板来复制集群中的任何数据库。然而，重要的是要明白，这（尚未）被设计为一个通用“COPY DATABASE”工具。主要限制是不允许在复制源数据库时将任何其他会话连接到该数据库。如果在启动时有任何其他连接存在，CREATE DATABASE 将会失败；在复制操作期间，将阻止建立与源数据库的新连接。

It is possible to create additional template databases, and indeed one can copy any database in a cluster by specifying its name as the template for CREATE DATABASE. It is important to understand, however, that this is not (yet) intended as a general-purpose “COPY DATABASE” facility. The principal limitation is that no other sessions can be connected to the source database while it is being copied. CREATE DATABASE will fail if any other connection exists when it starts; during the copy operation, new connections to the source database are prevented.

每个数据库中 pg_database 中存在两个有用的标志：列 datistemplate 和 datallowconn。datistemplate 可以设置为表示一个数据库被用作 CREATE DATABASE 的模板。如果设置了此标志，则任何拥有 CREATEDB 权限的用户都可以克隆该数据库；如果没有设置此标志，则只有超级用户和数据库所有者可以克隆该数据库。如果 datallowconn 为假，则不允许建立到该数据库的新连接（但仅通过将标志设为假并不会终止现有会话）。template0 数据库通常标记为 datallowconn = false，以防止对其进行修改。template0 和 template1 都应始终用 datistemplate = true 标记。

Two useful flags exist in pg_database for each database: the columns datistemplate and datallowconn. datistemplate can be set to indicate that a database is intended as a template for CREATE DATABASE. If this flag is set, the database can be cloned by any user with CREATEDB privileges; if it is not set, only superusers and the owner of the database can clone it. If datallowconn is false, then no new connections to that database will be allowed (but existing sessions are not terminated simply by setting the flag false). The template0 database is normally marked datallowconn = false to prevent its modification. Both template0 and template1 should always be marked with datistemplate = true.

Note

template1 和 template0 没有任何特殊状态，除了名称 template1 是 CREATE DATABASE 的默认源数据库名称之外。例如，可以删除 template1，然后在 template0 中重新创建它，而不会产生任何不良影响。如果有人不小心在 template1 中添加了一堆垃圾，那么采取此操作可能比较明智。（要删除 template1，必须有 pg_database.datistemplate = false。）

template1 and template0 do not have any special status beyond the fact that the name template1 is the default source database name for CREATE DATABASE. For example, one could drop template1 and recreate it from template0 without any ill effects. This course of action might be advisable if one has carelessly added a bunch of junk in template1. (To delete template1, it must have pg_database.datistemplate = false.)

当初始化数据库集群时，postgres 数据库也会被创建。此数据库旨在作为用户和应用程序连接到的默认数据库。它只是 template1 的一个副本，必要时可以删除并重新创建它。

The postgres database is also created when a database cluster is initialized. This database is meant as a default database for users and applications to connect to. It is simply a copy of template1 and can be dropped and recreated if necessary.