PostgreSQL使用手冊

julley

PostgreSQL使用手冊


　　PostgreSQL是以加州大学伯克利分校计算机系开发的POSTGRE版本4.2为基础的对象关系型数据库管理系统（ORDBMS）。 POSTGRES
领先的许多概念只是在非常迟的时候才出现在商业数据库中。它支持大部分 SQL
标准并且提供了许多其他现代特性。并且，因为许可证的灵活，任何人都可以以任何目的免费使用，修改和分发 PostgreSQL，
不管是私用，商用，还是学术研究使用。
安装
在你想开始使用 PostgreSQL
之前，你必须安装它。PostgreSQL
很有可能已经安装到你的节点上了，因为它包含在你的操作系统的发布里，或者是系统管理员已经安装了它。如果是这样的话，那么你应该从操作系统的文档或者你的系统管理员那里获取如何访问
PostgreSQL 的信息。如果你不清楚 PostgreSQL
是否已经安装，或者不知道你能否用它（已经安装的）做自己的实验，那么你就可以自己安装。PostgreSQL 可以由任何非特权用户安装，并不需要超级用户
(root)
的权限。
创建一个数据库
看看你能否访问数据库服务器的第一个例子就是试着创建一个数据库。一台运行着PostgreSQL
服务器可以管理许多数据库。通常我们会为每个项目和每个用户单独使用一个数据库。你的节点管理员可能已经为你创建了可以使用的数据库。他应该已经告诉你这个数据库的名字。如果这样你就可以省略这一步，并且跳到下一节。
要创建一个新的数据库，在我们这个例子里叫
mydb，你可以使用下面的命令：
$ createdb mydb 它应该生成下面这样的响应：
CREATE DATABASE
如果这样，那么这一步就成功了，你就可以忽略本节余下的部分了。
如果你看到类似下面这样的信息
createdb: command not found
那么就是PostgreSQL没有安装好。要么是就根本没装上， 要么是你的搜索路径没有设置正确。尝试用绝对路径调用该命令试试：
$
/usr/local/pgsql/bin/createdb mydb
在你的节点上这个路径可能不一样。和你的管理员联系或者看看安装指导获取正确的位置。
另外一种响应可能是这样：
createdb: could not
connect to database postgres: could not connect to server: No such file or
directory

      Is the server running locally and accepting
connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
　　这意味着该服务器没有启动，或者没有在 createdb
预期的地方启动。同样，你也要检查安装指导或者找管理员。
另外一个响应可能是这样：
createdb: could not connect to
database postgres: FATAL: user “joe” does not exist
在这里提到了你自己的登陆名。如果管理员没有为你创建 PostgreSQL 用户帐号，就会发生这些现象。（PostgreSQL
用户帐号和操作系统用户帐号是不同的） 如果你是管理员，参阅 Chapter 18 获取创建用户帐号的帮助。你需要变成安装 PostgreSQL
的操作系统用户的身份（通常是 postgres）才能创建第一个用户帐号。 也有可能是赋予你的 PostgreSQL
用户名和你的操作系统用户名不同；这种情况下，你需要使用 -U 开关或者使用 PGUSER 环境变量声明你的 PostgreSQL
用户名。
如果你有个数据库用户帐号，但是没有创建数据库所需要的权限，那么你会看到下面的东西：
createdb: database creation
failed: ERROR: permission denied to create database
并非所有用户都经过了创建新数据库的授权。如果
PostgreSQL 拒绝为你创建数据库，那么你需要让节点管理员赋予你创建数据库的权限。出现这种情况时请咨询你的节点管理员。如果你自己安装了
PostgreSQL， 那么你应该以你启动数据库服务器的用户身份登陆然后参考手册完成权限的赋予工作。
你还可以用其它名字创建数据库。PostgreSQL
允许你在一个节点上创建任意数量的数据库。数据库名必须是以字母开头并且小于 63
个字符长。一个方便的做法是创建和你当前用户名同名的数据库。许多工具假设该数据库名为缺省数据库名，所以这样可以节省你的敲键。要创建这样的数据库，只需要键入
$ createdb
如果你再也不想使用你的数据库了，那么你可以删除它。比如，如果你是数据库 mydb
的所有人（创建人），那么你就可以用下面的命令删除它：
$ dropdb mydb
（对于这条命令而言，数据库名不是缺省的用户名。这样你就必须声明它）这个动作物理上将所有与该数据库相关的文件都删除并且不可取消，因此做这件事之前一定要想清楚。
访问数据库
一旦你创建了数据库，你就可以访问它：
运行
PostgreSQL 交互的终端程序， 叫 psql， 它允许你交互地输入，编辑，和执行 SQL 命令。
你可能需要启动 psql，试验本教程中的例子。
你可以用下面的命令为 mydb 数据库激活它：
$ psql mydb
如果你省略了数据库名字，那么它缺省就是你的用户账号名字。在 psql
里，你会看到下面的欢迎信息：
Welcome to psql 8.1, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms

     \h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
　　mydb⇒
最后一行也可能是
mydb=#
这个提示符意味着你是数据库超级用户，最可能出现在你自己安装了 PostgreSQL
的情况下。作为超级用户意味着你不受访问控制的限制。如果你启动 psql 时碰到了问题，那么回到前面的小节。 诊断 createdb 的方法和诊断 psql
的方法很类似， 如果前者能运行那么后者也应该能运行。 psql 打印出的最后一行是提示符，它表示 psql 正听着你说话，这个时候你就可以敲入 SQL 查询到一个 psql
维护的工作区中。试验一下下面的命令：
mydb⇒ SELECT version();

                          version
　　—————————————————————-
PostgreSQL 8.1 on i586-pc-linux-gnu, compiled by
GCC 2.96
(1 row)
　　psql 程序有一些不属于 SQL
命令的内部命令。 它们以反斜杠开头，”\”。 有些这种命令在欢迎信息中列出。比如，你可以用下面的命令获取各种PostgreSQL SQL 命令的帮助语法： mydb⇒ \h
要退出
psql，键入
mydb⇒ \q
然后 psql 就会退出并且给你返回到命令行 shell。（要获取更多有关内部命令的信息，你可以在 psql
提示符上键入\?）psql的完整功能在 Part VI 中有文档。如果 PostgreSQL 安装正确，那么你还可以在操作系统的shell提示符上键入 man
psql 来阅读该文档。
和任何包含珍贵数据的东西一样，PostgreSQL 数据库也应该经常备份。尽管这个过程相当简单，
但是我们还是应该理解做这件事所用的一些技巧和假设。
备份 PostgreSQL 数据有三种完全不同的方法：
SQL
转储
文件系统级别备份
在线备份
每种备份都有自己的优点和缺点，下面著重讲解SQL转储。
SQL转储
SQL 转储的方法采用的主意是创建一个文本文件，这个文本里面都是
SQL
命令，当把这个文件回馈给服务器时，将重建与转储时状态一样的数据库。 PostgreSQL 为这个用途提供了应用工具 pg_dump。这条命令的基本用法是：
pg_dump dbname > outfile
正如你所见，pg_dump 把结果输出到标准输出。 我们下面就可以看到这样做有什么好处。
pg_dump 是一个普通的 PostgreSQL 客户端应用（尽管是个相当聪明的东西。）这就意味着你可以从任何可以访问该数据库的远端主机上面进行备份工作。
但是请记住 pg_dump 不会以任何特殊权限运行。具体说来，就是它必须要有你想备份的表的读权限，因此，实际上你几乎总是要成为数据库超级用户
要声明
pg_dump 应该以哪个用户身份进行联接，使用命令行选项 -h host 和 -p port。
缺省主机是本地主机或你的环境变量PGHOST声明的值。类似，缺省端口是环境变量PGPORT或（如果它不存在的话）编译好了的缺省值。（服务器通常有相同的缺省，所以还算方便）
和任何其他
PostgreSQL 客户端应用一样， pg_dump 缺省时用与当前操作系统用户名同名的数据库用户名进行联接。 要覆盖这个名字，要么声明 -U 选项，
要么设置环境变量PGUSER。 请注意 pg_dump 的联接也和普通客户应用一样要通过客户认证机制（在 Chapter 20）里描述。由 pg_dump
创建的备份在内部是一致的，也就是说，在pg_dump运行的时候对数据库的更新将不会被转储。 pg_dump 工作的时候并不阻塞其他的对数据库的操作。
（但是会阻塞那些需要排它锁的操作，比如 VACUUM FULL。）
Important: 如果你的数据库结构依赖于 OID
（比如说用做外键），那么你必须告诉 pg_dump 把 OID 也导出来。 要导OID，可以使用 -o
命令行选项。
从转储中恢复
pg_dump 生成的文本文件可以由 psql 程序读取。
从转储中恢复的常用命令是
psql dbname < infile
这里的 infile 就是你给pg_dump命令的
outfile参数。这条命令不会创建数据库 dbname，你必须在执行psql 前自己从template0创建（也就是说，用命令 createdb -T
template0 dbname）。 psql 支持类似 pg_dump 的选项用以控制数据库服务器位置和用户名。 参阅 psql
的手册获取更多信息。
在开始运行恢复之前，目标库和所有在转储出来的库中拥有对象的用户，以及曾经在某些对象上被赋予权限的用户都必须已经存在。
如果这些不存在，那么恢复将失败，因为恢复过程无法把这些对象恢复成原有的所有权和/或权限。一旦完成恢复，在每个数据库上运行 ANALYZE 是明智的举动，
这样优化器就有有用的统计数据了。你总是可以运行 vacuumdb -a -z 来 VACUUM ANALYZE 所有数据库；这个等效于手工运行 VACUUM
ANALYZE。
pg_dump 和 psql 可以通过管道读写， 这样我们就可能从一台主机上将数据库目录转储到另一台主机上，比如
pg_dump
-h host1 dbname | psql -h host2 dbname
Important:
pg_dump生成的转储输出是相对于template0的。这就意味着任何 加入到template1的语言，过程等都会经由 pg_dump
转储。结果是，在恢复的时候，如果你使用的是客户化的template1， 那么你必须从template0中创建空的数据库，就象我们上面的例子那样。有关如何有效地向
PostgreSQL 里装载大量数据的建议， 请参考 Section 13.4。
使用 pg_dumpall
上面的方法在备份整个数据库集群的时候比较麻烦而且不方便。因此我们提供了 pg_dumpall 程序。 pg_dumpall
备份一个给出的集群中的每个数据库，同时还确保保留象用户和组这样的全局数据状态。 这个命令的基本用法是：
pg_dumpall > outfile
生成的转储可以用 psql 恢复：
psql -f infile postgres
（实际上，你可以声明任意现有的数据库进行连接，但是如果你是向一个空的数据库集群装载， 那么 postgres 应该是比较好的选择。）
恢复pg_dumpall的转储的时候通常需要数据库超级用户权限，因为我们需要它来恢复用户和组信息。
处理大数据库
因为 PostgreSQL 允许表的大小大于你的系统允许的最大文件大小，
可能把表转储到一个文件会有问题，因为生成的文件很可能比你的系统允许的最大文件大。 因为 pg_dump 输出到标准输出，你可以用标准的 Unix
工具绕开这个问题：
使用压缩的转储。使用你熟悉的压缩程序，比如说 gzip。
pg_dump dbname | gzip >
filename.gz
用下面命令恢复：
createdb dbname
gunzip -c filename.gz |
psql
dbname
或者
cat filename.gz | gunzip | psql dbname
使用
split。split 命令允许你用下面的方法把输出分解成操作系统可以接受的大小。比如，让每个块大小为 1 兆字节：
pg_dump dbname |
split -b 1m – filename
用下面命令恢复：
createdb dbname
cat filename* | psql
dbname
使用客户化转储格式。如果PostgreSQL是在一个安装了zlib 压缩库的系统上制作的，
那么客户化转储格式将在写入输出文件的时候压缩数据。 它会生成和使用 gzip 类似大小的转储文件， 但是还附加了一个优点：你可以有选择地恢复库中的表。
下面的命令用客户化转储格式转储一个数据库：
pg_dump -Fc dbname > filename
客户化格式的转储不是脚本，不能用于
psql， 而是需要使用 pg_restore 转储。 请参考 pg_dump 和 pg_restore 的手册获取细节。