PostgreSQL备份还原随手记

在项目中，数据库的备份还原与数据库表迁移并不少见。备份和迁移数据库是重要的任务，它可以确保数据的安全性并实现在不同环境中的无缝迁移。接下来就PostgreSQL 数据库的备份和迁移做了个笔记。postgresql提供了pg_dump和pg_dumpall命令进行数据库的备份，pg_dumpall是将整个pg集群转储到一个脚本文件中，而pg_dump命令可以选择一个数据库或者部分表进行备份。详细请查看 文档。

备份

备份之前先用管理登录看一下数据库的数据目录和安装目录。默认的数据目录位于bin目录的同级文件夹。

--查看PostgreSQL的数据目录
show data_directory;

pg_dump提供了一种灵活的归档和传输机制。pg_dump可以被用来备份整个数据库，然后pg_restore可以被用来检查归档并/或选择数据库的哪些部分要被恢复。最灵活的输出文件格式是"自定义"格式（-Fc）和"目录"格式（-Fd）。它们允许选择和重排序所有已归档项、支持并行恢复并且默认是压缩的。"目录"格式是唯一一种支持并行转储的格式。其使用方法如下。
pg_dump [connection-option…][选项]… [数据库名字]

参数

cmd到PostgreSQL安装目录bin下,在命令窗口中输入：pg_dump -?

一般选项:
 -f, --file=FILENAME          输出文件或目录名
 -F, --format=c|d|t|p         输出文件格式（c-自定义归档文件格式、d-目录方式归档、t-tar压缩文件格式、p-普通SQL文本）
 -j, --jobs=NUM               执行多个并行任务进行备份转储工作
 -v, --verbose                指定冗长模式。这将导致pg_dump向标准错误输出详细的对象注释以及转储文件的开始/停止时间，还有进度消息
 -V, --version                输出版本信息，然后退出
 -Z, --compress=0-9           被压缩格式的压缩级别
 --lock-wait-timeout=TIMEOUT  在等待表锁超时后操作失败
 --no-sync                    不用等待变化安全写入磁盘
 -?, --help                   显示此帮助, 然后退出

控制输出内容选项:
 -a, --data-only              只转储数据,不包括模式
 -b, --blobs                  在转储中包括大对象
 -B, --no-blobs               排除转储中的大型对象
 -c, --clean                  在重新创建之前，先清除（删除）数据库对象，如drop table。只对纯文本输出有意义
 -C, --create                 指定输出文件中是否生成create database语句,只对纯文本输出有意义。如果也指定了--clean，脚本会在重新连接到目标数据库之前先删除它然后再重建
 -E, --encoding=ENCODING      在默认情况下，该转储会以该数据库的编码创建（另一种得到相同结果的方式是将PGCLIENTENCODING环境变量设置成想要的转储编码）
 -n, --schema=SCHEMA          只转储匹配schema的模式，这会选择模式本身以及它所包含的所有对象。当没有指定这个选项时，目标数据库中所有非系统模式都将被转储。多个模式可以通过书写多个-n开关来选择
 -N, --exclude-schema=SCHEMA  不转储匹配schema模式的任何模式。该模式被根据-n所用的相同规则被解释。-N可以被给定多次来排除匹配几个模式中任意一个的模式。
 -o, --oids                   转储对象标识符（OID）作为每个表数据的一部分。如果你的应用以某种方式引用OID列（例如在一个外键约束中），应使用这个选项。
 -O, --no-owner               不输出设置对象拥有关系来匹配原始数据库的命令。默认情况下，pg_dump会发出ALTER OWNER或SET SESSION AUTHORIZATION语句来设置被创建的数据库对象的拥有关系。除非该脚本被一个超级用户（或是拥有脚本中所有对象的同一个用户）启动，这些语句都将会失败。要使一个脚本能够被任意用户恢复，但把所有对象的拥有关系都给这个用户，可指定-O。
 -s, --schema-only            只转储对象定义（模式），而非数据。这个选项是--data-only的逆选项
 -S, --superuser=NAME         指定要在禁用触发器时使用的超级用户的用户名。只有使用--disable-triggers时，这个选项才相关（通常，最好省去这个选项，而作为超级用户来启动结果脚本来取而代之）
 -t, --table=TABLE            指定导出的表、视图、序列，可以使用多个-t匹配多个表，使用-t之后，-n和-N就失效了.可以在该模式中书写通配字符来选择。
 -T, --exclude-table=TABLE    不转储指定名称的表
 -x, --no-privileges          不要转储权限 (grant/revoke)。防止转储访问特权
 --binary-upgrade             只能由升级工具使用
 --column-inserts             将数据转储为带有显式列名的INSERT命令（INSERT INTO table (column, ...) VALUES ...）。这将使得恢复过程非常慢，这主要用于使转储能够被载入到非PostgreSQL数据库中。
 --disable-dollar-quoting     这个选项禁止在函数体中使用美元符号引用，并且强制它们使用 SQL 标准字符串语法被引用
 --disable-triggers           在只恢复数据的过程中禁用触发器
 --enable-row-security        启用行安全性（只转储用户能够访问的内容）
 --exclude-table-data=TABLE   不转储指定名称的表中的数据
 --if-exists                  当删除对象时使用IF EXISTS
 --inserts                    将数据转储为INSERT命令（而不是COPY）。这将使得恢复非常慢，这主要用于使转储能够被载入到非PostgreSQL数据库中。
 --no-security-labels         不转储安全标签的分配
 --no-synchronized-snapshots  在并行工作集中不使用同步快照
 --no-tablespaces             不转储表空间分配信息
 --no-unlogged-table-data     不转储没有日志的表数据
 --quote-all-identifiers      所有标识符加引号，即使不是关键字
 --section=SECTION            恢复命名部分（包括三个部分：pre-data,data以及post-data。
                             data部分包含表记录数据、大对象数据以及序列的值；
                             post-data部分包含索引、触发器、规则和约束的定义；
                             pre-data部分包含除此外其他所有的对象定义）
 --serializable-deferrable    等到备份可以无异常运行
 --snapshot=SNAPSHOT          为转储使用给定的快照
 --strict-names               要求每个表和(或)schema包括模式以匹配至少一个实体
 --use-set-session-authorization 输出 SQL-标准的SET SESSION AUTHORIZATION命令取代ALTER OWNER命令来确定对象的所有关系。这让该转储更加兼容标准，但是取决于该转储中对象的历史，该转储可能无法正常恢复。而且，一个使用SET SESSION AUTHORIZATION的转储将一定会要求超级用户特权来正确地恢复，而ALTER OWNER要求更少的特权

联接选项:
 -d, --dbname=DBNAME      对数据库 DBNAME备份
 -h, --host=主机名        数据库服务器的主机名或套接字目录
 -p, --port=端口号        数据库服务器的端口号
 -U, --username=名字      以指定的数据库用户联接
 -w, --no-password        永远不提示输入口令
 -W, --password           强制口令提示 (自动)
 --role=ROLENAME          在转储前运行SET ROLE 参数用于指定生成备份文件时使用的角色名

备份案例

1、备份整个数据库：

pg_dump -F c -f D:\PG\PG.backup -v -b -h localhost -U test -p 5432 test1

2、只备份数据库用户中指定名称public的模式

pg_dump -Fc -f D:\PG\PG.backup -v -b -n public -h localhost -U test -p 5432 -d test1

3、只备份某些表

pg_dump -t ta3 -t *sys* -F c -f D:\PG\PGTables.backup -h localhost -U test -p 5432 -d test1

注意：
1、如果schema的名称和user名称不一样，可能会导致找不到表。
2、pg_dump中的-t可以使用通配符。pg_restore中的-t没有任何通配符匹配的规定。 -t csywname_* 参数用于选择所有表名以 “csywname_” 开头的表；*name参数用于选择所有表名以 “name” 结尾的表;name参数用于选择所有表名包含 “name” 的表。
3、当-t被指定时，pg_dump不会尝试转储所选表可能依赖的任何其他数据库对象。因此，无法保证一次指定表转储的结果能够仅凭其本身被成功地恢复到一个干净的数据库中。

4、配分为普通SQL文件

pg_dump -t ta3 -t *sys* -f D:\PG\PGTables.sql -h localhost -U test -p 5432 -d test1

pg_restore还原

对于pg_dump的自定义备份custom和tar类型的备份，需要使用pg_restore进行恢复。其使用方式如下：
pg_restore [connection-option…] [option…] [filename]

参数

cmd到PostgreSQL安装目录bin下,在命令窗口中输入：pg_restore -?

一般选项:
 filename                 指定要被恢复的归档文件（对于一个目录格式的归档则是目录）的位置。如果没有指定，则使用标准输入
 -d, --dbname=名字        连接到数据库dbname并且直接恢复到该数据库中
 -f, --file=FILENAME      为生成的脚本或列表（当使用-l时）指定输出文件。默认是标准输出
 -F, --format=c|d|t       指定归档的格式。并不一定要指定该格式，因为pg_restore将会自动决定格式。
 -v, --verbose            详细模式 备份数据库的sql地址
 -V, --version            输出版本信息, 然后退出
 -?, --help               显示此帮助, 然后退出

恢复控制选项:
 -a, --data-only              只恢复数据，不恢复模式（数据定义）。如果在归档中存在，表数据、大对象和序列值会被恢复
 -c, --clean                  在重新创建数据库对象之前清除它们（除非使用了--if-exists，如果有对象在目标数据库中不存在，这可能会生成一些无害的错误消息）
 -C, --create                 在恢复一个数据库之前先创建它。如果还指定了--clean，在连接到目标数据库之前丢弃并且重建它。--create选项，必须先连接数据库，才能创建数据库，所以指定--dbname=postgres
 -e, --exit-on-error          在发送 SQL 命令到该数据库期间如果碰到一个错误就退出。默认行为是继续并且在恢复结束时显示一个错误计数
 -I, --index=NAME             只恢复提及的索引的定义。可以通过写多个-I开关指定多个索引
 -j, --jobs=NUM               使用并发任务运行pg_restore中最耗时的部分 — 载入数据、创建索引或者创建约束。对于一个运行在多处理器机器上的服务器，这个选项能够大幅度减少恢复一个大型数据库的时间。
 -l, --list               列出归档的内容的表格。这个操作的输出能被用作-L选项的输入。注意如果把-n或-t这样的过滤开关与-l一起使用，它们将会限制列出的项
 -L, --use-list=FILENAME      只恢复在list-file中列出的归档元素，并且按照它们出现在该文件中的顺序进行恢复。注意如果把-n或-t这样的过滤开关与-L一起使用，它们将会进一步限制要恢复的项
 -n, --schema=NAME            只恢复在被提及的模式中的对象。可以用多个-n开关来指定多个模式。这可以与-t选项组合在一起只恢复一个指定的表
 -N, --exclude-schema=NAME    不恢复此模式中的对象
 -O, --no-owner               不要输出将对象的所有权设置为与原始数据库匹配的命令。默认情况下，pg_restore会发出ALTER OWNER或者SET SESSION AUTHORIZATION语句来设置已创建的模式对象的所有权。 如果最初的数据库连接不是由超级用户 (或者是拥有脚本中所有对象的同一个用户)发起的，那么这些语句将失败。 通过-O，任何用户名都可以被用于初始连接，并且这个用户将会拥有所有被创建的对象。
 -P, --function=NAME(args)    只恢复被提及的函数。要小心地拼写函数的名称和参数使它们正好就是出现在转储文件的内容表中的名称和参数。可以使用多个-P开关指定多个函数
 -s, --schema-only            只恢复模式, 不包括数据
 -S, --superuser=NAME         指定在禁用触发器时要用的超级用户名。只有使用--disable-triggers时这个选项才相关。
 -t, --table=NAME       恢复命名关系（表、视图等）这个标志的行为和pg_dump的-t标志不一样。在pg_restore中当前没有任何通配符匹配的规定，也不能在其-t选项中包括模式的名称
 -T, --trigger=NAME           恢复指定名字的触发器
 -x, --no-privileges          跳过处理权限的恢复 (grant/revoke)
 -1, --single-transaction     将恢复作为单一事务执行（即把发出的命令包裹在BEGIN/COMMIT中）。这可以确保要么所有命令完全成功，要么任何改变都不被应用。
 --disable-triggers           只有在执行一个只恢复数据的恢复时，这个选项才相关。它指示pg_restore在装载数据时执行命令临时禁用目标表上的触发器。
 --enable-row-security        启用行安全性
 --if-exists                  当删除对象时使用IF EXISTS。只有指定了–clean时，这个选项才有效
 --no-comments                不恢复注释
 --no-data-for-failed-tables  对那些无法创建的表不进行数据恢复
 --no-publications            不恢复发行
 --no-security-labels         不恢复安全标签信息
 --no-subscriptions           不恢复订阅
 --no-tablespaces             不恢复表空间的分配信息
 --section=SECTION            恢复命名节 (数据前、数据及数据后)
 --strict-names               要求每个表和(或)schema包括模式以匹配至少一个实体
 --use-set-session-authorization 输出 SQL 标准的SET SESSION AUTHORIZATION命令取代ALTER OWNER命令来决定对象拥有权。这会让转储更加兼容标准，但是依赖于转储中对象的历史，可能无法正确恢复

联接选项:
 -h, --host=主机名        数据库服务器的主机名或套接字目录
 -p, --port=端口号        数据库服务器的端口号
 -U, --username=名字      以指定的数据库用户联接
 -w, --no-password        永远不提示输入口令
 -W, --password           强制口令提示 (自动)
 --role=ROLENAME          在恢复前执行SET ROLE操作 参数用于指定执行还原操作时使用的角色名

还原案例

1、还原整个库

pg_restore -c -O -v -h localhost -p 5432 -U test -d test2 -F c D:\PG\PG.backup 2>D:\PG\PG.log

2、只还原数据库用户中指定名称public的模式

pg_restore -c -v -h localhost -p 5432 -U test -d test2 -n public -F c D:\PG\PG.backup 2>D:\PG\PG.log

3、只还原某些表
方式一：通过–table指定表

pg_restore -O -c -v -h localhost -U abcd -p 5432 -d test2 --table=ta3  -F c D:\PG\PGTables.backup 2>D:\PG\PG.log

方式二：通过list
第一步：使用 pg_restore -l 命令来查看备份文件中的表清单

pg_restore -l D:\PG\PGTables.backup > D:\PG\PGTables.txt

第二步：编辑清单文件PGTables.txt，只保留符合条件的表，删除其他表的行。再依据清单进行恢复

pg_restore -v -c -h localhost -U abcd -p 5432 -d test2 -L D:\PG\PGTables.txt -Fc D:\PG\PGTables.backup 2>D:\PG\PG.log

4、跳过已存在的表恢复

pg_restore -v -O --no-data-for-failed-tables -h localhost -U abcd -p 5432 -d test2 -Fc D:\PG\PGTables.backup 2>D:\PG\PG.log

参数说明：
1、 -O 参数：pg_restore 将跳过所有已存在的表，并继续进行还原操作。但是会追加数据
2、–no-data-for-failed-tables参数： 默认情况下，即便表的创建命令失败（例如因为表已经存在），表数据也会被恢复。通过这个选项，对这类表的数据会被跳过。

注意：当模式名与原模式名不一样时，需要在备份文件中进行全局替换模式名，例如，将 “A.table_name” 替换为 “B.table_name”。且替换时模式名的长度不一致会导致文件异常。

psql还原

是PostgreSQL的交互式终端，其使用方法如下：
psql [选项]… [数据库名称 [用户名称]]

参数

通用选项:
  -c,--command=命令        执行单一命令(SQL或内部指令)然后结束
  -d, --dbname=DBNAME      指定要连接的数据库 (默认："admin")
  -f, --file=文件名        从文件中执行命令然后退出
  -l, --list               列出所有可用的数据库,然后退出
  -v, --set=, --variable=NAME=VALUE
                           设置psql变量NAME为VALUE
                           (例如，-v ON_ERROR_STOP=1)
  -V, --version            输出版本信息, 然后退出
  -X, --no-psqlrc          不读取启动文档(~/.psqlrc)
  -1 ("one"), --single-transaction
                           作为一个单一事务来执行命令文件(如果是非交互型的)
  -?, --help[=options]     显示此帮助，然后退出
      --help=commands      列出反斜线命令，然后退出
      --help=variables     列出特殊变量，然后退出
  >              意思是导出到指定目录；
  <               还原指定目录文件

输入和输出选项：
  -a, --echo-all           显示所有来自于脚本的输入
  -b, --echo-errors        回显失败的命令
  -e, --echo-queries       显示发送给服务器的命令
  -E, --echo-hidden        显示内部命令产生的查询
  -L, --log-file=文件名    将会话日志写入文件
  -n, --no-readline        禁用增强命令行编辑功能(readline)
  -o, --output=FILENAME    将查询结果写入文件(或 |管道)
  -q, --quiet              以沉默模式运行(不显示消息，只有查询结果)
  -s, --single-step        单步模式 (确认每个查询)
  -S, --single-line        单行模式 (一行就是一条 SQL 命令)

输出格式选项 :
  -A, --no-align           使用非对齐表格输出模式
  -F, --field-separator=STRING
                           为字段设置分隔符,用于不整齐的输出(默认："|")
  -H, --html               HTML 表格输出模式
  -P, --pset=变量[=参数]   设置将变量打印到参数的选项(查阅 \pset 命令)
  -R, --record-separator=STRING
                           为不整齐的输出设置字录的分隔符(默认：换行符号)
  -t, --tuples-only        只打印记录i
  -T, --table-attr=文本    设定 HTML 表格标记属性（例如,宽度,边界)
  -x, --expanded           打开扩展表格输出
  -z, --field-separator-zero
                           为不整齐的输出设置字段分隔符为字节0
  -0, --record-separator-zero
                           为不整齐的输出设置记录分隔符为字节0

联接选项:
  -h, --host=主机名        数据库服务器主机或socket目录(默认："本地接口")
  -p, --port=端口          数据库服务器的端口(默认："5432")
  -U, --username=用户名    指定数据库用户名(默认："admin")
  -w, --no-password        永远不提示输入口令
  -W, --password           强制口令提示 (自动)

还原案例

对于普通的SQL文件

psql -h localhost -U test2 -p 5432 -d test2 < D:\PG\PGTables.sql 2>D:\PG\PG.log

注意：当模式(schema)名需要切换时，需要使用文本编辑器或其他工具打开备份文件 PGTables.sql。修改表定义中的模式名后再还原。例如 test.ta3 替换为 stest2.ta3。

飞流直下三千尺，疑是银河落九天。--李白《望庐山瀑布》