如何使用pg_dump备份单个数据库 - 云原生数据库 PolarDB

pg_dump是PolarDB PostgreSQL版（兼容Oracle）提供的一种逻辑备份工具，用于将集群中的单个数据库备份为脚本文件或其他存档文件。

简介

pg_dump用于备份单个数据库。即使当前数据库正在被访问，也会对正在访问的数据进行一致的备份，并且在执行备份时不会阻止其他用户访问数据库（读取或写入）。更多详情请参见pg_dump官网。

说明

PolarTools中的pg_dump与社区版本的pg_dump存在差异，PolarTools中的pg_dump针对PolarDB PostgreSQL版（兼容Oracle）进行了适配。使用社区版本的pg_dump可能会出现未知异常或备份的对象数据不完整等情况。

备份文件格式

脚本文件：脚本文件为纯文本文件，其中包含将数据库重建到备份时状态的SQL命令。
存档文件：存档文件必须和pg_restore配合使用来重建数据库。
输出文件格式主要包括自定义格式-Fc、目录格式-Fd和tar格式的存档文件-Ft。其中-Fc和-Fd允许对所有存档项目进行选择和重新排序，且默认为压缩格式的文件。-Ft不是压缩文件，并且我们在恢复数据时不支持重新排序。
说明
目录格式是唯一支持并行备份的格式。
当与其中一种存档文件结合使用时，pg_dump可用于备份整个数据库。pg_restore可用于检查存档或选择哪些部分需要被恢复。

语法

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

表 1. 参数说明

参数	描述
connection-option	控制数据库连接参数的命令行选项。详情请参见connection-option（控制数据库连接参数命令行选项）。
option	控制输出内容和格式的命令行选项。详情请参见option（控制输出内容和格式的命令行选项）。
dbname	需要备份的数据库名称。

表 2. connection-option（控制数据库连接参数命令行选项）

命令行选项	描述
-d dbname即--dbname=dbname	指定要连接的数据库的名称。
-h host即--host=host	指定运行服务器的计算机的主机名。如果该值以斜杠开头，则将其用作Unix域套接字的目录，默认值为PGHOST环境变量。
-p port即--port=port	指定服务器正在侦听连接的TCP端口或本地Unix域套接字文件扩展名。默认放在PGPORT环境变量中，否则使用程序中的默认值。
-U username即--username=username	需要连接的用户名。
-w即--no-password	表示pg_dump在连接数据库时不提示输入密码。
-W即--password	表示pg_dump在连接数据库时提示输入密码。说明此选项为非必选项。
--role=rolename	指定用于创建备份的角色名。

表 3. option（控制输出内容和格式的命令行选项）

命令行选项	描述
dbname	需要备份的数据库的名称。如果未指定，则使用环境变量PGDATABASE。
-a即--data-only	只备份数据，不备份结构。说明此选项备份表数据、大型对象和序列值。
-b即--blobs	备份时默认包含大对象。当指定了--schema、-table或--schema only时，备份将不包含大对象。重要 blob被视为数据，因此在备份时使用--data only将包含数据，使用--schema only则不包含数据。
-B即--no-blobs	排除备份中的大对象。说明当同时出现-b和-B时，备份将包含大对象。
-c即--clean	在执行创建数据库对象命令之前删除数据库对象。为避免在恢复数据库时产生错误信息，建议在恢复数据库时指定--if exists。说明此选项仅适用于脚本文件。对于存档文件，可以在调用pg_restore时指定该选项。
-C即--create	创建数据库并重新连接到新创建的数据库。如果指定了--clean，则脚本将删除并重新创建目标数据库，然后再重新连接到该数据库。如果指定了--create命令行，且未指定--no-acl。备份数据时将包含数据库的注释、数据库的配置信息以及数据库本身的访问权限信息。说明此选项仅适用于脚本文件。对于存档文件，可以在调用pg_restore时指定该选项。
-E encoding即--encoding=encoding	以指定的字符集编码创建备份。默认情况下，以当前需要备份的数据库编码创建备份，您也可以将环境变量PGCLIENTENCODING的值设置为想要的备份编码。
-F format即--format=format	备份格式。可选以下任一格式： p即plain：输出脚本文件（默认）。 c即custom：输出适合作为pg_restore输入的自定义格式存档文件。默认情况下，此格式的存档文件是压缩文件。 d即directory：输出适合作为pg_restore输入的目录格式的存档文件。默认情况下，此格式的存档文件是压缩文件，并且支持并行备份。 t即tar：输出适合作为pg_restore输入的tar格式的存档文件。tar格式的存档文件不支持压缩。另外，当使用tar格式时，在恢复数据期间无法更改表数据项的相对顺序。
-f file即--file=file	将备份数据发送到指定的文件。基于目录备份格式时，此参数必填，且指定的是目标目录而不是文件。基于其他文件备份格式时，此参数可省略，默认使用标准备份文件格式。
-j njobs即--jobs=njobs	通过同时备份njobs个表来并行运行备份。需要将此选项与目录格式结合使用。说明并行备份之前，需要停止修改数据库的进程（DDL和DML）。
-n pattern即--schema=pattern	仅备份与pattern匹配的模式。如果未指定此选项，将备份目标数据库中的所有非系统模式。说明当指定-n时，pg_dump不会备份所选模式可能依赖的任何其他数据库对象。因此，无法保证备份特定模式的数据后，能够成功地将数据恢复到空数据库中。当指定-n时，pg_dump不会备份非模式对象（如Blob）的数据。如果需要备份这部分数据，可以在备份时添加 --blobs命令行选项。
-N pattern即--exclude-schema=pattern	备份除pattern之外的其他模式。说明如果-n和-N同时出现，备份时至少匹配一个-n模式而不匹配-N模式，如果只出现-N，备份时将排除与-N匹配的模式。
-o即--oids	备份对象标识符（OID）作为每个表数据的一部分。如果您的应用程序以某种方式引用OID列（例如在一个外键约束中引用OID列），请使用此选项。否则，不使用此选项。
-O即--no-owner	匹配原始数据库时，不需要设置对象的所有权。说明此选项仅适用于脚本文件。对于存档文件，可以在调用pg_restore时指定该选项。
-s即--schema-only	只备份对象模式，不备份数据。
-S username即--superuser=username	禁用触发器时使用的超级用户名。仅在使用--disable-triggers时才使用该选项。
-t pattern即--table=pattern	只备份与pattern匹配的表。可以通过编写多个-t来选择多张表，也可以通过在模式中写入通配符来选择多张表。说明指定-t时，pg_dump不会尝试备份所选表可能依赖的任何其他数据库对象。因此，不能保证备份特定表后能够成功地将数据恢复到空数据库中。
-T pattern即--exclude-table=pattern	不备份匹配pattern的表。可以多次指定-T来排除与多个模式匹配的表。说明当同时给出-t和-T时，备份时至少匹配一个-t而不匹配-T的表。如果只出现-T，备份时将排除与-T匹配的表。
-v即--verbose	指定详细模式。
-V即--version	打印pg_dump版本并退出。
-x即--no-privileges或--no-acl	取消备份访问权限（grant或revoke命令）。
-Z 0..9即--compress=0..9	指定需要使用的压缩级别。0表示未压缩。说明对于自定义存档文件，则指定单个表数据段压缩，且默认为中等级别的压缩。对于脚本文件，默认不压缩。当设置非零压缩级别时，会导致整个输出文件被压缩。
--column-inserts和--attribute-inserts	将数据备份为具有显式列名的`INSERT`命令。 `INSERT INTO table (column, ...) VALUES ...`
--disable-dollar-quoting	禁止对函数体使用美元引号。
--disable-triggers	临时禁用目标表上的触发器。此选项仅在创建数据备份时有效。使用此选项时，需要通过-S指定一个超级用户。说明此选项仅适用于脚本文件。对于存档文件，可以在调用pg_restore时指定该选项。
--enable-row-security	允许备份您有权限访问的表的部分内容。此选项仅在备份具有行安全性的表的内容时才有效。重要如果使用此选项，则可能还需要使用INSERT 进行备份，因为恢复数据期间的COPY FROM不支持行安全性。
--exclude-table-data=pattern	不备份与pattern匹配的表数据。可以多次指定--exclude-table-data来排除与多个模式匹配的表。说明如果需要排除数据库中所有表的数据，请参见--schema only。
--if-exists	使用条件命令（例如，添加IF EXISTS子句）清理数据库对象。使用时需要同时指定--clean，否则此选项无效。
--inserts	将数据备份为INSERT命令。重要使用此选项后，如果在恢复数据时对数据进行重新排序可能会执行失败。建议使用--column-inserts 。
--load-via-partition-root	备份表分区的数据时，使用COPY或INSERT命令。说明当恢复使用此选项创建的存档文件时，建议谨慎使用并行恢复。
--lock-wait-timeout=timeout	指定获取共享锁的等待时间。
--no-comments	表示不备份注释。
--no-publications	表示不备份出版物。
--no-security-labels	表示不备份安全标签。
--no-subscriptions	表示不备份订阅。
--no-sync	表示不需要等待所有文件均安全写入磁盘即返回数据。
--no-synchronized-snapshots	表示在服务器上可以运行pg_dump -j。
--no-tablespaces	表示在默认的表空间中创建所有对象。说明此选项仅适用于脚本文件。对于存档文件，可以在调用pg_restore时指定该选项。
--no-unlogged-table-data	表示不备份未标记表的内容。
--quote-all-identifiers	表示强制引用所有标识符。
--rows-per-insert=nrows	控制数据库备份为INSERT命令时的最大行数。
--section=sectionname	表示只备份指定的section。section的名称可以是pre-data、data或post-data，可以多次指定此选项以选择多个section，默认备份所有section。说明 data项包含实际的表数据、大型对象内容和序列值。 post-data项包括索引、触发器、规则和约束的定义，而不是已验证的检查约束。 pre-data项包括所有其他数据定义项。
--serializable-deferrable	表示对备份使用一个可序列化事务。说明如果使用pg_dump时，读写事务处于活动状态，则可能会延迟一段不确定的时间才开始执行备份操作。如果使用pg_dump时，读写事务没有处于活动状态，则此选项不会产生任何影响。
--snapshot=snapshotname	表示在备份数据库时使用指定的同步快照。
--strict-names	表示每个模式（-n/ --schema）和表（-t/ --table）限定符至少要匹配备份的数据库中的一个模式和表。说明如果模式和表限定符没有找到匹配项，pg_dump即使没有--strict-names，也会生成错误。此选项对-N/--exclude schema、-T/--exclude table或--exclude table data无效。无法匹配任何对象的排除模式不会被当作错误。
--use-set-session-authorization	表示输出SQL标准的SET SESSION AUTHORIZATION命令而不是ALTER OWNER命令。
-?即--help	显示关于pg_dump命令行的帮助信息。

注意事项

当选择了仅用作数据备份且使用了--disable triggers选项的表时，pg_dump会在插入数据之前发出禁用用户表触发器的命令，插入数据后发出重新启用触发器的命令。如果恢复中途被停止，系统目录可能会停留在一种错误状态。
备份文件恢复后，可运行ANALYZE确保最佳性能。
当备份逻辑复制订阅时，pg_dump将生成使用connect=false选项的CREATE SUBSCRIPTION命令。如果涉及的主机已更改，则可能需要更改连接信息，在启动新的完整表拷贝之前需截断目标表。
pg_dump在内部执行SELECT语句时，如果运行pg_dump遇到问题，需确保能够使用psql等工具从数据库中查询信息。此外，确保libpq前端库使用的任何默认连接设置和环境变量都能正常使用。
pg_dump的数据库活动通常由统计收集器收集，如果不需要收集，可以通过PGOPTIONS或ALTER USER命令将参数track_counts设置为false。

示例

执行以下命令，将名称为mydb的数据库备份到SQL脚本文件中：
```
pg_dump mydb > db.sql
```
执行以下命令，将SQL脚本重新加载到名称为newdb的（新创建的）数据库中：
```
psql -d newdb -f db.sql
```
执行以下命令，将数据库备份到自定义格式的存档文件中：
```
pg_dump -Fc mydb > db.dump
```
执行以下命令，将数据库备份到目录格式的存档文件中：
```
pg_dump -Fd mydb -f dumpdir
```
执行以下命令，将数据库与5个辅助作业并行备份到目录格式的存档文件中：
```
pg_dump -Fd mydb -j 5 -f dumpdir
```
执行以下命令，将存档文件重新加载到名称为newdb的（新创建的）数据库中：
```
pg_restore -d newdb db.dump
```
执行以下命令，将存档文件重新加载到备份文件的同一数据库中，并清除该数据库的当前内容：
```
pg_restore -d postgres --clean --create db.dump
```
执行以下命令，备份名称为mytab的单个表：
```
pg_dump -t mytab mydb > db.sql
```
执行以下命令，备份detroit模式中名称以emp开头的所有表（名称为employee_log的表除外）：
```
pg_dump -t 'detroit.emp*' -T detroit.employee_log mydb > db.sql
```
执行以下命令，备份名称以east或west开头并以gsm结尾的所有模式（不包括名称中含test的任何模式）：
```
pg_dump -n 'east*gsm' -n 'west*gsm' -N '*test*' mydb > db.sql
```
执行以下命令，使用正则表达式表示法来合并开关：
```
pg_dump -n '(east|west)*gsm' -N '*test*' mydb > db.sql
```
执行以下命令，备份除名称以ts_开头的表以外的所有数据库对象：
```
pg_dump -T 'ts_*' mydb > db.sql
```
如果需要在-t和相关开关中指定大写或混合大小写的名称，您需要通过双引号引用该名称，否则它将被折叠为小写。但是双引号对于Shell命令来说是特殊的，因此必须反过来引用它们。因此，如果要转储大小写混合名称的单个表，请执行以下命令：
```
pg_dump -t "\"MixedCaseName\"" mydb > mytab.sql
```