18.postgreSQL的备份恢复pg_dump和pg_restore

postgreSQL的备份恢复

目录

数据库备份有多种方式，如逻辑备份和物理备份。物理备份的方式有PostgreSQL本身提供的WAL日志之上的热备份功能和物理存储快照。本节只介绍物理存储快照备份方式，前者将在Standby数据库的相关章节中介绍。DBA可以根据需要选择合适的备份方式。

逻辑备份

PostgreSQL中提供了pg_dump、pg_dumpall命令进行数据库的逻辑备份。pg_dump与pg_dumpall命令的功能差不多，只是pg_dumpall是将一个PostgreSQL数据库集群全部转储到一个脚本文件中，而pg_dump命令可以选择一个数据库或部分表进行备份。这里不介绍pg_dumpall的使用方法。

使用pg_dump命令甚至可以在数据库处于使用状态时进行完整一致的备份，它并不阻塞其他用户对数据库的访问（读或写）。

pg_dump生成的备份文件可以是一个SQL脚本文件，也可以是一个归档文件。

  • SQL脚本文件是纯文本格式的文件，它包含许多SQL命令，执行这些SQL命令可以重建该数据库并将之恢复到保存成脚本时的状态。
  • 归档格式的备份文件必须与pg_restore一起使用来重建数据库，这种格式允许pg_restore选择恢复哪些数据，甚至可以在恢复之前对需要恢复的条目重新排序。归档格式的备份文件也可以设计成能够跨平台移植的。

pg_dump生成归档格式的备份文件，然后与pg_restore配合使用，能提供一种灵活的备份和恢复机制。
pg_dump可以将整个数据库备份到一个归档格式的备份文件中，而pg_restore则可以从这个归档格式的备份文件中选择性地恢复部分表或数据库对象，而不必恢复所有的数据。归档格式的备份文件又分为两种，最灵活的输出文件格式是“custom”自定义格式（使用命令项参数“-Fc”来指定），它允许对归档元素进行选取和重新排列，并且默认是压缩的；另一种是tar格式（使用命令项参数“-Ft”来指定），这种格式的文件不是压缩的，并且加载时不能重新排序，但是它也很灵活，可以用标准UNIX下的tar工具进行处理。custom自定义格式比较常用。

pg_dump命令

pg_dump命令的语法格式如下：

pg_dump [connection-option...] [option...] [dbname]

pg_dump连接选项参数如下。

·-h host或--host=host：指定运行服务器的主机名。如果以斜杠开头，则被用作到UNIX域套接字的路径。默认情况下，如果设置了$PGGHOST环境变量则从此环境变量中获取，否则尝试一个UNIX域套接字连接。
·-p port或--port=port：指定服务器正在侦听的TCP端口或本地UNIX域套接字文件的扩展。默认情况下，如果设置了$PGPORT环境变量则从此环境变量中获取，否则取默认端口5432（编译时可以修改此默认端口）。
·-U username或--username=username：指定要连接的用户名。
·-w或--no-password：从不提示密码。如果服务器请求密码身份认证，而且密码不能通过其他方式（如.pgpass文件）来获得，则此命令会导致连接失败。该选项常常用于后台脚本，因为后台脚本是无法输入密码的。
·-W或--password：强制pg_dump在连接到一个数据库之前提示密码。该选项通常是不重要的，因为如果服务器请求密码身份认证，pg_dump将自动提示一个密码。然而，当不提供“-W”选择时，pg_dump将会浪费一个连接并试图找出服务器是否需要密码。在某些情况下，输入“-W”可以避免额外的连接尝试。
·--role=rolename：该选项会导致pg_dump在连接到数据库之前发布一个SET ROLE rolename命令。这相当于切换到另一个角色。当已验证用户缺少pg_dump需要的权限，但是可以切换到一个拥有相应权限的角色时，可以使用该功能。
·dbname：指定连接的数据库名，实际上也是要备份的数据库名。如果没有使用该参数，则使用环境变量“$PGDATABASE”。如果$PGDATABASE也未声明，那么使用发起连接的用户名。



下面的参数是pg_dump命令专有的，用来控制备份哪些表的数据以及输出数据的格式。
1）-j,--jobs=NUM：指定并行导出的并行度。
2）-a或--data-only：该选项只对纯文本格式有意义。只输出数据，不输出数据定义的SQL语句。
3）-b或--blobs：在转储中是否包含大对象。除非指定了选择性转储的选项“--schema” “--table”“--schema-only”开关，否则默认会转储大对象。此选项仅用于选择性转储时控制是否转储大对象。
4）-c或--clean：该选项只对纯文本格式有意义。用于控制输出的脚本中是否生成清理该数据库对象的语句（如drop table命令）。
5）-C或--create：该选项只对纯文本格式有意义。指定脚本中是否输出一条create database语句和连接到该数据库的语句。一般在备份的源数据库与恢复的目标数据库的名称一致时才指定该参数。
6）-E encoding或--encoding=encoding：以指定的字符集编码创建转储。默认转储是依据数据库编码创建的。如果不指定此参数，可以通过设置环境变量“$PGCLIENTENCODING”达到相同的目的。
7）-f file或--file=file：输出到指定的文件。如果没有指定此参数，则输出到标准输出。
8）-F format或--format=format：选择输出的格式。“format”可以是p、c或t。
  ·“p”是“plain”的意思，纯文本SQL脚本文件的格式，这是默认值。
  ·“c”是“custom”的意思，输出一个适合pg_restore使用的自定义格式存档。这是最灵活的输出格式，该格式允许手动查询并且可以在pg_restore恢复时重排归档项的顺序。该格式默认是压缩的。
  ·“t”是“tar”的意思，输出一个适合输入pg_restore的tar格式的归档。该输出格式允许手动选择并且可以在恢复时重排归档项的顺序，但是这个重排序是有限制的，表数据项的相关顺序在恢复时不能更改。同时，tar格式不支持压缩，且对独立表的大小限制为8GB。

9）-n schema或--schema=schema：只转储匹配schema模式的内容，包括模式本身及其包含的对象。如果没有声明此选项，所有目标数据库中的非系统模式都会被转储。可以使用多个-n选项指定多个模式。同样，schema参数将按照psql中的\d命令的规则（参见Patterns）被解释为匹配模式，因此可以使用通配符匹配多个模式。在使用通配符时，最好用引号进行界定，以防Shell对通配符进行扩展。


注意：如果指定了-n，那么pg_dump将不会转储模式所依赖的其他数据库对象，因此无法保证转储的内容一定能够在另一个干净的数据库中成功恢复。非模式对象，比如大对象，不会在指定-n时被转储。可以使用--blobs明确要求转储大对象。

10）-N schema或--exclude-schema=schema：不转储任何匹配schema模式的内容。匹配规则与“-n”完全相同，可以指定多个“-N”以排除多种匹配的模式。如果同时指定了-n和-N，那么将只转储匹配-n但不匹配-N的模式。如果指定-N但是不指定-n，那么匹配-N的模式将不会被转储。
11）-o或--oids：是否为每个表都输出对象标识（OID）。如果应用中需要OID字段（比如用于外键约束）则使用该选项，否则不应使用该选项。
12）-O或--no-owner：该选项只对纯文本格式有意义，不把对象的所有权设置为对应源数据库中的owner。pg_dump默认发出ALTEROWNER或SET SESSION AUTHORIZATION语句以设置创建的数据库对象的所有者。如果这些脚本将来没有被超级用户（或者拥有脚本中全部对象的用户）运行，会导致恢复失败，设置-O选项就是为了让该脚本可以被任何用户使用。
13）-s或--schema-only：只输出对象定义（模式），不输出数据。该选项在备份表结构或在另一个数据库上创建相同结构的表时比较有用。
14）-S username或--superuser=username：指定关闭触发器时需要用到的超级用户名。它只有在使用了--disable-triggers时才有影响。一般情况下，最好不要输入该参数，而是用超级用户启动生成的脚本。
15）-t table或--table=table：只转储匹配table的表、视图、序列。可以使用多个-t选项匹配多个表。同样table参数将按照psql中\d命令的规则被解释为匹配模式，因此可以使用通配符匹配多个模式。在使用通配符时，最好用引号进行界定，以防Shell对通配符进行扩展。使用了-t之后，-n和-N选项就失效了，因为被-t选中的表将无视 -n和-N选项而被转储，同时除了表之外的其他对象也不会被转储。

注意：如果指定了-t，那么pg_dump将不会转储选中的表依赖的所有其他数据库对象，因此无法保证转储出来的表能在一个干净的数据库中成功恢复。
-t选项与PostgreSQL8.2之前的版本不兼容。8.2之前的-t tab将转储所有名为“tab”的表，但是8.2以上的版本只转储在默认搜索路径中可见的表。写成“-t '*.tab'”将等价于之前版本的行为。同样，必须用“-t sch.tab”而不是之前版本的“-n sch -t tab”选择特定模式中的表。

16）-T table或--exclude-table=table：不转储任何匹配table模式的表。模式匹配规则与-t完全相同。可以指定多个-T以排除多种匹配的表。如果同时指定了-t和-T，那么将只转储匹配-t但不匹配-T的表。如果指定-T但是不指定-t，那么匹配-T的表将不会被转储。
17）-v或--verbose：执行过程中打印更详细的信息。使用此选项后，pg_dump将输出详细的对象评注及转储文件的启停时间和进度信息（输出到标准错误上）。
18）-V或--version：输出pg_dump版本并退出。
19）-x或--no-privileges或--no-acl：禁止转储访问权限（grant/revoke命令）。
20）-Z 0..9或--compress=0..9：指定要使用的压缩级别，“0”表示不压缩。对于自定义归档格式，该参数指定压缩的单个表数据段，并且默认用中等水平压缩。对于纯文本输出，设置一个非零的压缩级别会导致全部输出文件被压缩；默认不压缩。tar归档格式目前完全不支持压缩。
21）--binary-upgrade：该选项是专为升级工具准备的，其功能可能会在将来的版本中有所改变，因此不要将其用于其他目的。
22）--inserts：该选项像INSERT命令一样转储数据。默认使用COPY命令的格式转储数据，使用该选项将使恢复非常缓慢。该选项主要用于把数据加载到非PostgreSQL数据库。该选项为每一行生成一个单独的INSERT命令，如果在数据库恢复过程中遇到一行错误，仅会导致丢失一行数据而不是全部表内容。请注意，若目标表列的顺序与源表列的顺序不一样，恢复操作可能会完全失败，这时应该使用--column-inserts选项。
23）--column-inserts或--attribute-inserts：该选项像有显式列名的INSERT命令一样转储数据（INSERT INTO table（column,...）VALUES...），这将使恢复非常缓慢。主要用于可以加载到非PostgreSQL数据库的转储。
24）--disable-dollar-quoting：该选项关闭使用美元符界定函数体。强制用SQL标准的字符串语法的引号将函数体内容括起来。
25）--disable-triggers：该选项仅对纯文本格式有意义，只与创建仅有数据的转储相关。该选项指定pg_dump在恢复数据时，临时关闭目标表上触发器的命令。如果在表上有参照完整性检查或者其他触发器，恢复数据时不想重载它们，那么就应该使用此选项。目前，发出--disable-triggers命令的必须是超级用户，执行转储的脚本时应该用-S执行一个超级用户的名称。
26）--lock-wait-timeout=timeout：不要永远等待在开始转储时获取共享表锁。相反，如果不能在指定的timeout时间内锁住一个表，那么转储就会失败。在SET statement_timeout接受的任何格式中都可以声明超时。
27）--no-tablespaces：该选项只对纯文本格式有意义，不输出命令来选择表空间。该选项内，转储期间当表空间默认时，所有的对象都会创建表空间。
28）--use-set-session-authorization：输出符合SQL标准的SETSESSION AUTHORIZATION命令而不是ALTER OWNER命令。这样可以使转储更加符合标准，但是如果转储文件中对象的历史信息有问题，那么可能无法正确恢复。并且，使用SET SESSION AUTHORIZATION的转储必须有数据库超级用户的权限，而ALTER OWNER需要的权限则低得多。

pg_dump常用导出命令

1.导出整个库，custom 格式（推荐，后续可用 TOC 做选择性恢复）

pg_dump -h 127.0.0.1 -p 5432 -U postgres -d mydb -F c -f mydb.dump

2.导出单个表

pg_dump -h 127.0.0.1 -p 5432 -U postgres -d mydb -t public.emp -F c -f emp.dump

3.导出多个表（支持通配符）

pg_dump -h 127.0.0.1 -p 5432 -U postgres -d mydb -t 'public.emp*' -t public.dept -F c -f emp_dept.dump

输出日志格式对比

格式 参数 文件大小 恢复工具 典型场景
custom（归档） -F c 小 pg_restore 日常备份/选择性恢复
directory（目录） -F d 最小 pg_restore 并行导出(≥10)
tar -F t 中 pg_restore 兼容旧版本
plain SQL 默认 最大 psql 人工可读、可编辑

高频场景 10 例

场景 命令示例 备注
① 全库逻辑备份 pg_dump -h $IP -U postgres -d prod -F c -v -f prod_$(date +%F).dump 加 -v 看进度；cron 每日凌晨跑
② 仅结构，不要数据 pg_dump -s -F c -f schema.dump -s / --schema-only
③ 仅数据，不要结构 pg_dump -a -F c -f data.dump -a / --data-only
④ 排除大日志表 pg_dump -d mydb -T 'log_*' -F c -f mydb_no_log.dump -T 排除，支持通配
⑤ 只导函数/视图 pg_dump -d mydb -n public --section=pre-data -F c -f func_view.dump --section 分 pre-data/data/post-data
⑥ 并行加速（≥10） pg_dump -Fd -j 4 -f dumpdir 4 进程并行，目录格式
⑦ 压缩级最高 pg_dump -F c -Z 9 -f min.dump -Z 0-9，9 最慢最小
⑧ 远程 SSL 导出 pg_dump "sslmode=require host=db.x.com dbname=prod user=back" -F c -f prod.dump 连接串一行解决
⑨ 单表拆成 SQL pg_dump -t config --inserts -f config.sql --inserts 生成 INSERT，可跨版本
⑩ 带角色权限 pg_dumpall -g > globals.sql 角色、表空间、授权需 pg_dumpall -g 单独导出

容易踩的坑

  • 1.忘记加 -F c 默认导出 plain SQL，几百 GB 库直接炸屏。
  • 2.用 -t 筛选表时，关联序列（serial）不会自动带出，需手动加 -t 'seq_*'。
  • 3.9.6 以前并行 -j 只能 directory 格式；老版本别用 -j。
  • 4.大库并行导出前，先 VACUUM ANALYZE 可减少锁时间。
  • 5.导出时长事务/未决 2PC 会阻塞 -Fd 并行，先 SELECT pg_prepared_xact; 清掉。

一键检查备份完整性

# custom 格式
pg_restore -l mydb.dump >/dev/null && echo "OK" || echo "CORRUPT"

# directory 格式
pg_restore -l dumpdir >/dev/null && echo "OK" || echo "CORRUPT"

pg_restore命令

前面介绍了使用pg_dump的自定义备份或tar类型的备份需要使用pg_restore工具来恢复。下面介绍pg_restore命令的使用方法。

pg_restore命令的语法格式如下：

pg_restore [connection-option...] [option...] [filename]

pg_restore的连接参数与pg_dump基本相同：
·-h host或--host=host。
·-p port或--port=port。
·-U username或--username=username。
·-w或--no-password。
·-W或--password。
·--role=rolename。
只是pg_restore使用参数-d dbname或--dbname=dbname来连接指定的数据库。

而pg_dump命令连接到特定的数据库不是由以“-”或“--”开头的选项参数来指定的，而是直接由最后一个不带“-”或“--”的参数来指定pg_restore最后一个不带““-”或“--”的参数是一个转储文件名

pg_restore的参数说明如下。

1）filename：要恢复的备份文件的位置。如果未声明则使用标准输入。
2）-a或--data-only：只恢复数据，而不恢复表模式（数据定义）。
3）-c或--clean：创建数据库对象前先清理（删除）它们。
4）-C或--create：在恢复数据库之前先创建它。如果出现该选项，与-d在一起的数据库名只是用于发出最初的CREATE DATABASE命令，所有数据都恢复到名字出现在归档中的数据库中。
5）-d dbname或--dbname=dbname：与数据库“dbname”连接并且直接恢复到该数据库中。
6）-e或--exit-on-error：如果在向数据库发送SQL命令时遇到错误，则退出。默认是继续执行，并且在恢复结束时显示一个错误计数。
7）-f filename或--file=filename：指定生成脚本的输出文件，或者出现-l选项时用于列表的文件，默认是标准输出。
8）-F format或--format=format：指定备份文件的格式。pg_restore可自动判断格式，如果一定要指定，值可以是t或c之一。
  ·“t”表示“tar”，表示备份文件是一个tar文件。
  ·“c”表示“custom”，备份的格式是来自pg_dump的自定义格式。这是最灵活的备份格式，因为它允许对数据重新排序，也允许重载表模式元素。默认该格式是压缩的。
9）-I index或--index=index：只恢复命名的索引。
10）-j number-of-jobs或--jobs=number-of-jobs：运行pg_restore中最耗时部分如加载数据、创建索引或创建约束时，使用多个并发工作来完成。该选项可以显著缩短恢复时间。每个并发工作是一个进程或一个线程，且使用一个单独数据库连接。通常选择一个大的并发数能加快恢复性能，但过高的并发数也会因为抖动而降低恢复性能。pg_dump的自定义格式才支持该选项。输入文件不能是一个管道，必须是一个常规文件，同时，并发作业不能与--single￾transaction选项同时使用。
11）-l或--list：列出归档文件的内容。该操作的输出可以用作输入-L选项。请注意如果过滤选项（如-n或-t）与-l一同使用，将限制列出的项。
12）-L list-file或--use-list=list-file：仅恢复list-file中列出的归档元素，并按它们在文件中出现的顺序进行恢复。请注意，如果像-n或-t这样的过滤开关与-L一起使用，将进一步限制恢复哪些对象。通常会通过运行“pg_restore–l”命令创建一个初始的列表文件，然后编辑该文件（删除或移动文件中的行，或者在行前加一个分号注释掉该行）。
13）-n namespace或--schema=schema：只恢复指定名字模式中的定义和/或数据。该选项可以与-t选项一起使用，只恢复一个表的数据。
14）-O或--no-owner：不输出设置与最初数据库对象权限匹配的命令。默认情况下，pg_restore发出ALTER OWNER或SET SESSIONAUTHORIZATION语句，设置所创建的模式元素的所有者权限。如果最初的数据库连接不是由超级用户（或者是拥有所有创建出来的对象的同一个用户）发起的，那么这些语句将执行失败。如果使用-O，那么任何用户都可以用于初始连接，并且该用户将拥有创建出来的所有对象。
15）--no-tablespaces：不输出命令来选择表空间。使用该选项，恢复数据时，所有对象被创建在默认表空间中，而不是对象原先的表空间。
16）-P function-name(argtype [,...])或--function=function-name(argtype [,...])：只恢复指定的命名函数。请注意仔细拼写函数名及其参数，应该与备份内容列表完全一致。
17）-s或--schema-only：只恢复表结构（数据定义），不恢复数据（数据表中的内容)，序列的当前值也不会得到恢复。请不要与--schema选项混淆。
18）-S username或--superuser=username：设置关闭触发器时声明超级用户的用户名。只有在设置了--disable-triggers时才有效。
19）-t table或--table=table：只恢复指定的表的定义和/或数据。可以与-n参数（指定schema）联合使用。
20）-T trigger或--trigger=trigger：只恢复指定的触发器。
21）-v或--verbose：声明详细模式。
22）-V或--version：输出pg_restore版本并退出。
23）-x或--no-privileges或--no-acl：禁止恢复访问权限(grant/revoke命令)。
24）--disable-triggers：该选项只有在仅恢复数据时才相关。该选项指示pg_restore在加载数据时执行一些命令临时关闭在目标表上的触发器。如果表上有完整性检查或者其他触发器，而又不希望在加载数据时激活它们，那么可以使用该选项。目前，只有超级用户才能为--disable-triggers发出命令，因此，应该用-S声明一个超级用户名，最好是以超级用户身份运行pg_restore。
25）--use-set-session-authorization：输出SQL标准的SETSESSION AUTHORIZATION命令，而不是ALTER OWNER命令。这样可以令转储与标准兼容得更好，但是根据转储中对象的历史，该转储可能无法正确恢复。
26）--no-data-for-failed-tables：默认情况下，即使创建表的命令因为该表已经存在而执行失败，表中的数据仍将被恢复。使用此选项之后，这些表的数据将跳过恢复操作。当目标数据库可能已经包含所需恢复的某些表的内容时，该选项就很有用处了。比如，用于PostgreSQL扩展的辅助表（如PostGIS）就可能已经在目标数据库中恢复了，使用该选项就可以防止多次恢复以致重复或覆盖已经恢复的数据。该选项仅在直接向一个数据库中恢复时有效，在生成SQL脚本输出时无效。
27）-1或--single-transaction：把恢复操作放到一个单独的事务中来执行（也就是把恢复操作都放在一个BEGIN/COMMIT事务块中封装发射命令），这就确保了要么所有的命令都成功完成，要么没有恢复任何数据，不会出现只恢复了一部分数据的情况。该选项包含--exit-on-error选项，即只要发生一个错误，所有的操作都会回滚。

pg_dump和pg_restore应用示例

当连接一个本地的数据库且不需要密码时，如对osdba数据库进行备份，备份文件的格式是脚本文件格式，可以使用如下命令：

pg_dump osdba >osdba.sql

使用pg_dump也可以备份一个远程的数据库

pg_dump -h 192.168.122.1 -Uosdba osdba >osdba.sql

如果想要让生成的备份文件为自定义格式

pg_dump -Fc -h 192.168.122.1 -Uosdba osdba >osdba.dump

把上述备份文件恢复到另一个数据库“osdba2”中：

createdb osdba2
pg_restore -d osdba2 osdba.dump

如果只想备份表“testtab”，则可以使用如下命令：

pg_dump -t testtab >testtab.sql

如果想备份sche1模式中所有以“job”开头的表，但是不包括job_log表，可使用如下命令：

pg_dump -t 'sche1.emp*' -T schema1.job_log osdba > schema1.emp.sql

转储所有数据库对象，但是不包括名字以“log”结尾的表，可使用如下命令：

pg_dump -T '*_log' osdba > log.sql

如先从192.168.122.1备份数据库“osdba”，然后恢复到192.168.122.2机器上，可使用如下命令

pg_dump -h 192.168.122.1 -Uosdba osdba –Fc >osdba.dump
pg_restore -h 192.168.122.2 -Uosdba -C -d postgres osdba.dump

在pg_restore命令中，-d中指定的数据库可以是192.168.122.2机器上实例中的任意数据库，pg_restore仅用该数据库名称进行连接，先执行CREATE DATABASE命令创建osdba数据库，然后再重新连接到osdba数据库，最后把备份的表和其他对象转储到osdba数据库中。

将备份出来的数据重新加载到一个不是新建的且名称不同的数据库“osdba2”中，可使用如下命令：

createdb -T template0 osdba2
pg_restore -d osdba2 osdba.dump

注意，上面的命令从template0而不是template1来创建新数据库，这可以确保数据库内容最少。这里没有使用-C选项，而是直接连接到要恢复的数据库上。

TOC 部分表选择性恢复

导出全库
pg_dump -Fc test >test.dump
只想恢复某张表，可先用 grep 把对应行筛出来再生成最小 TOC：
pg_restore -l test.dump | grep employees > employees.toc
pg_restore -d newdb -L employees.toc test.dump

物理备份

最简单的物理备份就是冷备份，也就是把数据库停下来，然后拷贝数据库的PGDATA目录。PostgreSQL把与数据库实例有关的配置文件和数据文件都存放在PGDATA目录下，所以PostgreSQL做冷备份很简单，这里不再赘述。

还有一种物理备份的方法是在不停止数据库的前提下完成数据库的备份，称之为热备份或在线备份。在PostgreSQL中通常的热备份方法有以下两种。

·第一种方法：使用数据库的PITR方法进行热备份。
·第二种方法：使用文件系统或块设备级别的快照功能完成备份。使用快照也能让备份出来的数据库与原数据库一致。
posted @ 2026-05-18 15:24  数据库小白（专注）  阅读(95)  评论(0)    收藏  举报