PostgreSQL数据库导出命令pg_dump详解

PostgreSQL数据库导出命令pg_dump详解

http://huangro.javaeye.com/blog/366038

pg_dump 是一个将 PostgreSQL 数据库保存到一个脚本或者归档文件中的工具．这个脚本文件的格式是纯文本，它包含许多 SQL 命令， 这些 SQL 命令可以用于重建该数据库并将之恢复到保存成脚本的时候的状态．要恢复这些脚本，使用 psql。 它们甚至可以用于在其它机器甚至是其它硬件体系的机器上 重建该数据库，通过对脚本进行一些修改，甚至可以在其它 SQL 数据库产品 上重建该数据库． 另外，还有候选的归档文件格式可以和 pg_restore 一起使用重建数据库， 并且它们也允许 pg_restore 对恢复什么东西进行选择，或者甚至是在恢复之前对需要恢复的条目进行重新排序． 归档文件也是设计成可以跨平台移植的． pg_dump 将保存用于重建所有用户定义的类型，函数，表，索引聚集和操作符所必须的信息．另外， 所有数据是用文本格式拷贝出来的，因而也可以很容易地拷贝回去， 也很容易用工具编辑． 如 果一种候选文件格式和 pg_restore 结合，那么pg_dump就能提供一种灵活的归档和传输机制． pg_dump 可以用于备份整个数据库， 然后就可以使用 pg_restore 检查 这个归档和/或选择要恢复的数据库部分． 最灵活等输出文件格式是"custom(客户化)"格式 (-Fc)．

它允许对归档元素进行选取和重新排列，并且缺省时是压缩的．tar 格式 (-Ft) 不是压缩的并且我们在装载等时候不可能重排列，不过它也很灵活；还有，它可以用其它工具，比如 tar 处理． 在运行 pg_dump 的时候，我们应该检查 输出，看看是否有任何警告存在(在标准错误上打印)， 特别是下面列出的限制． 即使数据库在并行使用着的时候，pg_dump 也制作出连贯的备份．pg_dump 并不阻塞其它 用户对数据库的访问(读或写)． 选项下面的命令行参数用于控制输出格式。

 dbname 声明将要转储的数据库名． 如果没有声明这个参数，那么使用环境变量 PGDATABASE。 如果那个环境变量也没声明，那么用发起连接的用户名。

 -a --data-only 只输出数据，不输出结构（表定义）． 这个选项只是对纯文本格式有意义．对于其它格式，你可以在调用 pg_restore 的时候声明选项．

 -b --blobs 转储数据和 BLOB 数据．

 -c --clean 输出在创建数据库创建命令之前先清理(删除) 该数据库对象的命令．这个选项只是对纯文本格式有意义．对于其它格式，你可以在调用pg_restore 的时候声明选项．

 -C --create 以一条创建该数据库本身并且与这个数据库联接等命令开头进行输出． (如果是这种形式的脚本，那么你在运行脚本之前和哪个数据库联接就不重要了．) 这个选项只对纯文本格式有意义．对于其它格式，你可以在调用 pg_restore 的时候声明该选项．

 -d --inserts 将数据输出为的INSERT命令 (而不是 COPY)． 这样会导致恢复非常缓慢．但却令归档更容易移植到其它 SQL 数据库．

-D --column-inserts --attribute-inserts 把数据转储为带有明确字段名的 INSERT 命令． 这样会导致恢复非常缓慢， 但是如果你想重新排列字段的顺序，那么它是必须的．

-f file --file=file 把输出发往指定的文件．如果忽略这些，则使用标准输出． -F format --format=format 选择输出的格式． format可以是下列之一： p输出纯文本SQL脚本文件（缺省） t 输出适合输入到 pg_restore 里的tar归档文件．使用这个归档允许在恢复数据库时重新排序和/或把表结构排除在外． 同时也可能可以在恢复的时候限制对哪些数据进行恢复． c 输出适于给 pg_restore 用的客户化归档． 这是最灵活的格式，它允许对装载的数据和纲要元素进行重新排列．这个格式缺省的时候是压缩的．

-i --ignore-version 忽略在 pg_dump 和数据库服务器之间的版本差别． pg_dump 可以处理来自以前版本的PostgreSQL 的数据库，但是太老的版本则不被支持了（目前是支持到 7.0）。 如果你需要跨越版本检查时才使用这个选项（ 而且如 pg_dump 失效，别说我没警告你）．

-o --oids 为每个表都输出对象标识（OID）． 如果你的应用在某种程度上引用了OID字段的话，(比如，在外键约束中 用到)． 那么使用这个选项． 否则，不应该使用这个选项．

-O --no-owner 不 把对象的所有权设置为对应源数据库． 通常， pg_dump 发出 (psql特有的) \connect 语句以设置纲要元素的所有权． 又见 -R 和 -X use-set-session-authorization 选项． 请注意 -O 并不防止所有对数据库的重新联接，只是防止那些为调整权限进行的排它联接． 这个选项只是对纯文本格式有意义．对于其它格式，在你调用 pg_restore 的时候你可以声明该选项．

-R --no-reconnect 禁止 pg_dump 发出任何 \connect 语句． 在 纯文本输出模式下，禁止 pg_dump 输出那种需要在恢复数据库的过程中重新联接数据库的脚本．一般的恢复脚本通常需要以不同的用户与服务器联接多次以设置 该对象最初的所有权．这个选项是一个相当粗糙的指令，因为 它令 pg_dump 失去了这个权限信息， 除非你使用 -X use-set-session-authorization 选项．我们不想在恢复的过程中重新联接的一个可能原因是也许对 数据库的访问需要手工干涉(也就是说，口令)． 这个选项只是对纯文本格式有意义．对于其它格式， 你在调用 pg_restore 的时候可以声明选项．

-s --schema-only 只输出表纲要（定义），不输出数据．

-S username --superuser=username 在某些场合，pg_dump 创建的脚本或者归档需要有 超级用户访问的权限，比如在关闭触发器或者为大纲元素甚至所有属性时． 这个选项声明在这些场合时使用的用户名．

-t table --table=table 只输出表 table的数据．

-v --verbose 声明冗余模式。 这样将令 pg_dump 在标准错误上打印 进度信息。 -x --no-privileges --no-acl 避免输出 ACL（赋予/撤消 命令）和表的所有者关系信息．

-X use-set-session-authorization --use-set-session-authorization 通 常，如果一个 pg_dump 生成的(纯文本模式)脚本 必须更改当前的数据库用户(比如，设置正确的对象所有权)的时候， 它使用 psql \connect 命令． 这条命令实际上打开一个新的联接，这个时候可能需要收工干涉 (比如，输入口令)．如果你使用 -X use-set-session-authorization，那么 pg_dump 则会输出 SET SESSION AUTHORIZATION 命令．这么做效果相同， 但是它要求使用生成的脚本做数据库恢复的用户是数据库超级用户．这个选项有效地覆盖了 -R 选项． 因为 SET SESSION AUTHORIZATION 是一个 标准的 SQL 命令，而 \connect 只能用于psql，因此这个选项同时还在理论上增加 了输出脚本的可移植性． 这个选项只对纯文本格式有意义．对于其它格式，你可以在你调用 pg_restore 的时候声明该选项．

 -Z 0..9 --compress=0..9 声明在那些支持压缩的格式中使用的压缩级别． （目前只有客户化格式支持压缩）． 下面的命令行参数控制数据库为联接参数。

-h host --host=host 声明运行服务器 的机器的主机名．缺省是使用本地Unix主控套接字，而不是一个 IP 联接． 如果主机名以斜扛开头，则它被用做到Unix 域套接字的路径．

-p port --port=port 声明服务器 正在侦听并等待联接的TCP/IP 端口或本地 Unix 主控套接字文件句柄． 缺省的端口号是5432，或者环境变量 PGPORT 的值（如果存在）．

-U username 以给出用户身分联接．

-W 强制口令提示．如果服务器需要口令认证，那么这个动作应该自动发生． 只有在一些平台上才有长选项形式。

环境 PGDATABASE PGHOST PGPORT PGUSER 缺省连接参数 诊断 Connection to database 'template1' failed. connectDBStart() -- connect() failed: No such file or directory        Is the postmaster running locally        and accepting connections on Unix socket '/tmp/.s.PGSQL.5432'? pg_dump 无法与指定主机和端口上的 PostgreSQL 服务器 相联．如果看到这条信息，确认 服务器 正在给定的主机上你所声明的端口上运行．

dumpSequence(table): SELECT failed 你没有读取数据库的权限． 和你的 PostgreSQL 节点管理员联系． 注意: pg_dump 在内部使用 SELECT 语句．如果你运行 pg_dump 时碰到问题，确认你能够使用象 psql 这样的程序从数据 库选择． 注意如 果你的安装给 template1 数据库增加了任何你自己的东西，那么请注意把 pg_dump 的输出恢复到一个 真正空的数据库中；否则你可能会收到因为重复定义所追加的对象 而造成的错误信息．要制作一个没有任何本地附属物的数据库，可以从 template0 而不是 template1 拷贝，比如∶ CREATE DATABASE foo WITH TEMPLATE template0;

pg_dump 有几个限制∶ 在转储一个表或者作为纯文本转储时，pg_dump 无法操作 大对象．大对象必须整体地使用二进制归档格式中的一种进行转储． 在进行纯数据转储时，pg_dump 发出一些查询先关闭在用户 表上面的触发器，然后插入数据，数据插入完成后再发出查询打开触发器． 如果恢复动作在中间停止，那么系统表可能就会处于一种错误状态． tar 归档的成员的大小限制于 8 GB。（这个限制是 tar 文件格式 的固有限制。）因此这个格式无法用于那些一个表的大小超过这个 尺寸的原文表现。tar 归档和任何其它输出格式的总大小是不受 限制的，只是可能会又操作系统的限制。

例子

转储一个数据库∶ $ pg_dump mydb > db.out

重载这个数据库∶ $ psql -d database -f db.out

输出一个叫 mydb 的包含BLOB 的数据库到一个 tar 文件： $ pg_dump -Ft -b mydb > db.tar

把这个数据库（连同BLOB） 一起恢复到一个现有的叫 newdb 的数据库： $ pg_restore -d newdb db.tar

历史 pg_dump 工具最早出现在 Postgres95 版本0.02． 非纯文本输出格式在 PostgreSQL 版本 7.1 时引入．