Postgresql 中文操作指南

23.6. Tablespaces #

PostgreSQL 中的表空间允许数据库管理员在文件系统中定义可以存储表示数据库对象的文件的位置。创建表空间后，在创建数据库对象时可以通过名称引用它。

Tablespaces in PostgreSQL allow database administrators to define locations in the file system where the files representing database objects can be stored. Once created, a tablespace can be referred to by name when creating database objects.

通过表空间，管理员可以控制 PostgreSQL 安装的磁盘布局。这样做在至少两个方面是有用的。首先，如果集群所在的分区或卷空间不足且无法扩展，那么可以在不同的分区上创建表空间，并在系统重新配置之前使用它。

By using tablespaces, an administrator can control the disk layout of a PostgreSQL installation. This is useful in at least two ways. First, if the partition or volume on which the cluster was initialized runs out of space and cannot be extended, a tablespace can be created on a different partition and used until the system can be reconfigured.

其次，表空间允许管理员基于数据库对象的用法模式来优化性能。例如，使用非常频繁的索引可以放在非常快速、高可用性的磁盘上，例如昂贵的固态设备。与此同时，存储几乎不使用或对性能不关键的存档数据的表可以存储在更便宜、更慢的磁盘系统中。

Second, tablespaces allow an administrator to use knowledge of the usage pattern of database objects to optimize performance. For example, an index which is very heavily used can be placed on a very fast, highly available disk, such as an expensive solid state device. At the same time a table storing archived data which is rarely used or not performance critical could be stored on a less expensive, slower disk system.

Warning

尽管位于 PostgreSQL 主数据目录之外，但表空间是数据库集群的组成部分，并且 cannot 可视为自治数据文件集合。它们依赖于主数据目录中包含的元数据，因此不能附加到其他数据库集群或单独备份。同样，如果您丢失了一个表空间（文件删除、磁盘故障等），数据库集群可能变得不可读或无法启动。将表空间放在临时文件系统中（例如 RAM 磁盘）会影响整个集群的可靠性。

Even though located outside the main PostgreSQL data directory, tablespaces are an integral part of the database cluster and cannot be treated as an autonomous collection of data files. They are dependent on metadata contained in the main data directory, and therefore cannot be attached to a different database cluster or backed up individually. Similarly, if you lose a tablespace (file deletion, disk failure, etc.), the database cluster might become unreadable or unable to start. Placing a tablespace on a temporary file system like a RAM disk risks the reliability of the entire cluster.

To define a tablespace, use the CREATE TABLESPACE command, for example

CREATE TABLESPACE fastspace LOCATION '/ssd1/postgresql/data';

位置必须是 PostgreSQL 操作系统用户拥有的现有空目录。随后在表空间内创建的所有对象都将存储在这个目录下的文件中。位置不能在可移除或临时存储上，因为如果表空间丢失或损坏，集群可能无法正常工作。

The location must be an existing, empty directory that is owned by the PostgreSQL operating system user. All objects subsequently created within the tablespace will be stored in files underneath this directory. The location must not be on removable or transient storage, as the cluster might fail to function if the tablespace is missing or lost.

Note

通常没有必要在每个逻辑文件系统上制作多个表空间，因为您无法控制逻辑文件系统内单个文件的位置。然而，PostgreSQL 不会强制执行任何此类限制，事实上，它并不直接了解您系统上的文件系统边界。它只是将文件存储在您告诉它使用的目录中。

There is usually not much point in making more than one tablespace per logical file system, since you cannot control the location of individual files within a logical file system. However, PostgreSQL does not enforce any such limitation, and indeed it is not directly aware of the file system boundaries on your system. It just stores files in the directories you tell it to use.

表空间本身的创建必须由数据库超级用户完成，但在创建完后，您可以允许普通数据库用户使用它。为此，请授予他们 CREATE 权限。

Creation of the tablespace itself must be done as a database superuser, but after that you can allow ordinary database users to use it. To do that, grant them the CREATE privilege on it.

表、索引和整个数据库都可以分配给特定的表空间。要做到这一点，拥有给定表空间的 CREATE 权限的用户必须将表空间名称作为参数传递给相关命令。例如，以下示例在 space1 表空间中创建一个表：

Tables, indexes, and entire databases can be assigned to particular tablespaces. To do so, a user with the CREATE privilege on a given tablespace must pass the tablespace name as a parameter to the relevant command. For example, the following creates a table in the tablespace space1:

CREATE TABLE foo(i int) TABLESPACE space1;

或者，使用 default_tablespace参数：

Alternatively, use the default_tablespace parameter:

SET default_tablespace = space1;
CREATE TABLE foo(i int);

当 default_tablespace 设置为除空字符串以外的任何内容时，它为没有显式 TABLESPACE 子句的 CREATE TABLE 和 CREATE INDEX 命令提供了一个隐式 TABLESPACE 子句。

When default_tablespace is set to anything but an empty string, it supplies an implicit TABLESPACE clause for CREATE TABLE and CREATE INDEX commands that do not have an explicit one.

还有一个 temp_tablespaces参数，它确定临时表和索引的位置，以及用于对大型数据集进行排序等目的的临时文件。这可能是一个表空间名称列表，而不仅仅是一个，以便可以将与临时对象关联的负载分散到多个表空间。每次创建临时对象时都会从列表中随机选取一个成员。

There is also a temp_tablespaces parameter, which determines the placement of temporary tables and indexes, as well as temporary files that are used for purposes such as sorting large data sets. This can be a list of tablespace names, rather than only one, so that the load associated with temporary objects can be spread over multiple tablespaces. A random member of the list is picked each time a temporary object is to be created.

与数据库关联的表空间用于存储该数据库的系统目录。此外，如果没有指定 TABLESPACE 子句，并且没有 default_tablespace 或 temp_tablespaces 指定其他选择（视情况而定），则它是用于在数据库中创建的表、索引和临时文件的默认表空间。如果创建数据库时没有指定表空间，它将使用从其复制的模板数据库的表空间。

The tablespace associated with a database is used to store the system catalogs of that database. Furthermore, it is the default tablespace used for tables, indexes, and temporary files created within the database, if no TABLESPACE clause is given and no other selection is specified by default_tablespace or temp_tablespaces (as appropriate). If a database is created without specifying a tablespace for it, it uses the same tablespace as the template database it is copied from.

在初始化数据库集群时会自动创建两个表空间。pg_global 表空间用于共享系统目录。pg_default 表空间是 template1 和 template0 数据库的默认表空间（因此，它也将是其他数据库的默认表空间，除非在 CREATE DATABASE 中的 TABLESPACE 子句中被覆盖）。

Two tablespaces are automatically created when the database cluster is initialized. The pg_global tablespace is used for shared system catalogs. The pg_default tablespace is the default tablespace of the template1 and template0 databases (and, therefore, will be the default tablespace for other databases as well, unless overridden by a TABLESPACE clause in CREATE DATABASE).

创建后，可以从任何数据库使用表空间，前提是请求的用户具有足够的权限。这意味着，在使用表空间的所有数据库中的所有对象被删除之前，表空间是无法删除的。

Once created, a tablespace can be used from any database, provided the requesting user has sufficient privilege. This means that a tablespace cannot be dropped until all objects in all databases using the tablespace have been removed.

要删除空表空间，请使用 DROP TABLESPACE 命令。

To remove an empty tablespace, use the DROP TABLESPACE command.

要确定现有表空间的集合，请检查 pg_tablespace 系统目录，例如

To determine the set of existing tablespaces, examine the pg_tablespace system catalog, for example

SELECT spcname FROM pg_tablespace;

psql 程序的 \db 元命令也适用于列出现有表空间。

The psql program’s \db meta-command is also useful for listing the existing tablespaces.

$PGDATA/pg_tblspc 目录包含指向集群中定义的每个非内置表空间的符号链接。虽然不建议这样做，但可以通过重新定义这些链接来手工调整表空间布局。在服务器运行时绝不要执行此操作。请注意，在 PostgreSQL 9.1 及更早版本中，您还需要使用新位置更新 pg_tablespace 目录。（如果您不这样做，pg_dump 将继续输出旧的表空间位置。）

The directory $PGDATA/pg_tblspc contains symbolic links that point to each of the non-built-in tablespaces defined in the cluster. Although not recommended, it is possible to adjust the tablespace layout by hand by redefining these links. Under no circumstances perform this operation while the server is running. Note that in PostgreSQL 9.1 and earlier you will also need to update the pg_tablespace catalog with the new locations. (If you do not, pg_dump will continue to output the old tablespace locations.)