PostgreSQL手册之数库管理-服务器专区

PostgreSQL手册之数库管理

作者：网络　佚名编辑： IT168 2005-09-19 00:00

【IT168 服务器学院】数据库是一些SQL对象（"数据库对象"）的命名集合；通常每个数据库对象（表，函数等等）属于并且只属于一个数据库。（不过有几个系统表，比如 pg_database，属于整个集群并且可以在集群之内的每个数据库里访问。）更准确地说，一个数据库是一个模式的集合，而模式包含表，函数等等。因此完整的层次是这样的：服务器，数据库，模式，表（或者其他类型对象，比如函数）。

在与数据库服务器联接的时候，应用应该在它的联接请求里面带有它想与之联接的数据库名称。不允许在一次联接里面对多个数据库访问．（不过没有限制一个应用与同一个或者其他数据库可以建立的联接数量．）数据库是物理上相互隔离的，对它们的访问控制是在联接层次进行控制的。如果一个PostgreSQL 服务器实例用于承载那些应该分隔并且相互之间并不知晓的用户和项目，那么我们建议把它们放在不同的数据库里。如果项目或者用户是相互关联的，并且可以相互使用对方的资源，那么应该把它们放在同一个数据库里，但可能是不同的模式里。模式只是一个纯粹的逻辑结构，谁能访问某个模式由权限系统控制。有关管理模式的更多信息在 Section 5.8 里。

数据库是使用 CREATE DATABASE 命令创建的（参阅 Section 18.2），用 DROP DATABASE 命令删除（参阅 Section 18.5）。要判断现有数据库的集合，检查系统表 pg_database，比如

SELECT datname FROM pg_database;
psql 程序的 \l 元命令和 -l 命令行选项也可以用来列出现存数据库。

注意: SQL 标准把数据库称作"目录（catalog）"，不过这两个东西实际上没有什么区别。

为了创建和删除数据库，必须先运行PostgreSQL服务器

数据库是用 SQL 命令 CREATE DATABASE: 创建的：

CREATE DATABASE name;

这里的 name 遵循SQL标识符的一般规则。当前用户自动成为此新数据库的所有者。同时，以后删除这个数据库也是这个用户的特权（同时还会删除其中的所有对象，即使那些对象有不同的所有者也这样。）

创建数据库是一个有限制的操作。

因为你需要与数据库服务器联接才能执行命令CREATE DATABASE，那么还有一个问题是任意节点的第一个数据库是怎样创建的？第一个数据库总是由initdb命令在初始化数据存储区的时候创建的。这个数据库叫template1而且不能被删除。因此要创建第一个"真正"的数据库的时候你可以与template1联接。

template1的名字可不是随便取的，当创建一个新的数据库时，实际上就是克隆了（复制）了模板数据库。这就意味着你对template1做的任何修改都会传播到所有随后创建的数据库。这就意味着说你不能把模板数据库用于真正的工作中，但是如果明智地使用这个特性，那它可以带来许多方便。另外，为了方便，你还可以用一个可以在 shell 中执行的程序来创建新数据库，createdb。

　　createdb dbname

　　createdb 没变什么魔术，它和template1连接并执行 CREATE DATABASE 命令。 createdb 的手册页包含使用它的细节。尤其是不带任何参数调用 createdb 将以当前用户名为名称创建数据库，这可能是也可能不是你要的。

　　有时候你想为其他什么人创建一个数据库。那个用户应该成为新数据库的所有者，这样他就可以自己配置和管理这个数据库。要实现这个目标，使用下列命令中的某一条：

　　CREATE DATABASE dbname OWNER username;
上面的是在 SQL 环境中，或者是

　　createdb -O username dbname

　　要想为其他用户创建一个数据库，你自己必须是数据库的超级用户。

　　 CREATE DATABASE 实际上是通过拷贝一个现有的数据库进行工作的。缺省时，它拷贝名字叫 template1 的标准系统数据库。所以该数据库是创建新数据库的"模板"。如果你给 template1 增加对象，这些对象将被拷贝到随后创建的用户数据库中。这样的行为允许节点对数据库中的标准套件进行修改。比如，如果你把过程语言 PL/pgSQL 安装到 template1 里，那么你在创建用户数据库的时候它们就会自动可得，而不需要额外的动作。

系统里还有第二个标准的系统数据库，叫 template0。这个数据库包含和 template1 一开始时一样的数据内容，也就是说，只有你使用的版本的 PostgreSQL 标准的对象。在 initdb 之后，我们不应该对 template0 做任何修改。通过告诉 CREATE DATABASE 使用 template0 而不是 template1 进行拷贝，你可以创建一个"纯净"的用户数据库，它不会包含任何 template1 里节点所特有的东西。这一点在恢复 pg_dump 转储的时候是非常方便的：转储脚本应该在一个纯洁的数据库中恢复以确保我们创建了被转储出的数据库中的正确内容，而不和任何现在可能已经存在在 template1 中的附加物相冲突。

要通过拷贝 template0 的方法创建一个数据库，使用

CREATE DATABASE dbname TEMPLATE template0;
这条命令是在 SQL 环境里的，或者是在 shell 里

createdb -T template0 dbname

我们可以创建额外的模板数据库，而且实际上我们可以在一个集群中通过将 CREATE DATABASE 的模板声明为相应的数据库名拷贝任何数据库。不过，我们必需明白，这个功能并非一般性的"COPY DATABASE"工具。实际上，在拷贝操作的过程中，源数据库必需是空闲状态（没有正在处理的数据修改事务）。 CREATE DATABASE 在操作开始时将会检查确保没有会话（除它自己以外）与源数据库联接，但是这样并不能保证在拷贝过程中不会发生修改的事情，如果发生这些事情，那么会导致一个不一致的结果数据库。因此，我们建议那些用做模板的数据库应该当做只读库对待。

在 pg_database 里有两个有用的标志可以用于每个数据库：字段 datistemplate 和 datallowconn。 datistemplate 表示该数据库是准备用做 CREATE DATABASE 的模板的。如果设置了这个标志，那么该数据库可以由任何有 CREATEDB 权限的用户克隆；如果没有设置，那么只有超级用户和该数据库的所有者可以克隆它。如果 datallowconn 为假，那么将不允许与该数据库发生任何新的连接（不过现有的会话不会因为把该标志设置为假而被杀死）。 template0 数据库通常被标记为 datallowconn = false 以避免对它的修改。 template0 和 template1都应该总是标记为datistemplate = true。

完成模板数据库的准备之后，或者对某个数据库做了任何标记修改之后，在该数据库中执行一次 VACUUM FREEZE 是一个好主意。如果做这些的时候在同一个数据库中没有其它打开的事务，那么系统保证在数据库中的行是"冻结"的，并且不会受事务 ID 重叠的影响。这个动作对那些 datallowconn 设置为假的数据库特别重要，因为在这样的数据库上没有办法做日常维护性的VACUUM。

注意: template1 和 template0 没有任何特殊的状态，除了 template1 这个名字是 CREATE DATABASE 以及各种象 createdb 这样的程序的缺省源数据库名之外。比如，我们可以删除 template1，然后从 template0 中创建它而不会有任何不良效果。如果我们不小心在 template1 里加了一堆垃圾，那么我们就会建议做这样的操作。

我们知道PostgreSQL 服务器提供了大量的运行时配置变量。你可以为许多这样的变量设置数据库相关的缺省数值。

比如，如果由于某种原因，你想关闭某个数据库上的 GEQO 优化器，你就不得不要么在一开始就在所有数据库中关闭它，要么是保证每个连接过来的客户端都很小心地发出了 SET geqo TO off; 命令。要令这个设置在特定数据库里成为缺省，你可以执行下面的命令

ALTER DATABASE mydb SET geqo TO off;

这样将保存该设置（但不是立即设置它）。在随后的连接中它将表现出像在会话开始后马上调用了 SET geqo TO off;的性质。请注意用户仍然可以在该会话中更改这个设置；它只是缺省。要撤消这样的设置，使用 ALTER DATABASE dbname RESET varname;.

数据库是用命令 DROP DATABASE: 删除的：

DROP DATABASE name;
只有数据库的所有者（也就是说，创建数据库的用户），或者超级用户可以删除数据库。删除数据库会删除数据库中包括的所有对象。数据库的删除是不可恢复的。

你不能在与目标库联接的时候执行 DROP DATABASE 命令。不过，你可以和其他数据库联接，包括template1数据库， template1也是你删除一个集群上的最后一个库的唯一方法。

为了方便，有一个在shell上运行的删除数据库的程序dropdb：

dropdb dbname
（和createdb不一样，dropdb 没有缺省删除名称为当前用户名的数据库的设置。）

PostgreSQL 里的表空间允许数据库管理员在文件系统里定义那些代表数据库对象的文件存放的位置。一旦创建了表空间，那么就可以在创建数据库对象的时候引用它。

通过使用表空间，管理员可以控制一个 PostgreSQL 安装的磁盘布局。这么做至少有两个用处。首先，如果初始化集群所在的分区或者卷用光了空间，而又不能逻辑上扩展或者别的什么操作，那么表空间可以在一个不同的分区上创建和使用，直到系统可以重新配置。

第二，表空间允许管理员根据数据库对象的使用模式安排数据位置，从而优化性能。比如，一个很频繁使用的索引可以放在非常快的，并且非常可靠的磁盘上，比如一种非常贵的固态设备。而同时，一个存储归档的数据，很少使用的，或者对性能要求不高的表可以存储在一个没那么昂贵，比较慢的磁盘系统上。

要定义一个表空间，使用 CREATE TABLESPACE 命令，比如：

CREATE TABLESPACE fastspace LOCATION ''/mnt/sda1/postgresql/data'';

这个位置必须是一个现有的空目录，并且属于 PostgreSQL 系统用户。所有随后在该表空间创建的对象都将被存放在这个目录下的文件里。

注意: 通常在一个逻辑文件系统上建立多个表空间没有什么意义，因为你无法控制一个逻辑文件系统里的不同文件的位置。不过，PostgreSQL 并不做这方面的任何强制，并且它实际上并不知道你的系统上的文件系统边界。它只是在你告诉它使用的目录里存储文件。

创建表空间本身必须用数据库超级用户身份进行，但是在那之后，你就可以允许普通数据库用户利用它了。要做这件事情，在表空间上给这些用户授予 CREATE 权限。

表，索引和整个数据库都可以放在特定的表空间里。想要这么做的话，在给定表空间上有 CREATE 权限的用户必须把表空间的名字以一个参数的形式传递给相关的命令。比如，下面的命令在表空间 space1 上创建一个表：

CREATE TABLE foo(i int) TABLESPACE space1;

另外，还可以使用 default_tablespace 参数：

SET default_tablespace = space1;
CREATE TABLE foo(i int);
只要 default_tablespace 设置为不是空字串的任何其他东西，那么它就为没有明确使用 TABLESPACE 子句的 CREATE TABLE 和 CREATE INDEX 命令提供一个隐含的 TABLESPACE 子句。

与一个数据库相关联的表空间用于存储该数据库的系统表，以及任何使用该数据库的服务器进程创建的临时文件。另外，如果在创建数据库对象是没有给出 TABLESPACE 子句（不管是明确的还是通过 default_tablespace），那么这是在该数据库里创建这些任何对象使用的缺省表空间。如果创建数据库时没有给它声明一个表空间，那么它使用与它拷贝的模版数据库相同的表空间。

initdb 自动创建两个表空间。pg_global 表空间用于共享的系统表。 pg_default 是 template1 和 template0 数据库的缺省表空间（因此，这个表空间也将是任何其它数据库的缺省表空间，除非在 CREATE DATABASE 的时候使用了明确的 TABLESPACE 子句。）

创建了表空间之后，它就可以用于任何数据库，只要请求的用户有足够权限。这意味着除非我们把使用这个表空间的所有数据库里的所有对象抖删除掉，否则我们不能删除该表空间。

要删除一个空的表空间，使用 DROP TABLESPACE 命令。

要判断一套现有的表空间，检查系统表 pg_tablespace，比如

SELECT spcname FROM pg_tablespace;
psql 程序的 \db 元命令也可以用于列出现有表空间。

为了简化表空间的实现，PostgreSQL 使用了大量符号连接。这就意味着表空间只能在支持符号连接的系统上使用。

目录 $PGDATA/pg_tblspc 包含指向集群里定义的每个非内置的表空间的符号连接。尽管我们不建议，但是我们还是可能通过手工重定义这些连接来调整表空间的布局。两个警告：在 postmaster 运行的时候不要这么干；并且，在你重启 postmaster 之后，更新 pg_tablespace 表以显示新的位置。（如果你不这么做，pg_dump 将继续显示旧的表空间位置。）

关注我们