pg_dump

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、目录格式-Fdtar格式的存档文件-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

备份格式。可选以下任一格式:

  • pplain:输出脚本文件(默认)。

  • ccustom:输出适合作为pg_restore输入的自定义格式存档文件。默认情况下,此格式的存档文件是压缩文件。

  • ddirectory:输出适合作为pg_restore输入的目录格式的存档文件。默认情况下,此格式的存档文件是压缩文件,并且支持并行备份。

  • ttar:输出适合作为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

备份表分区的数据时,使用COPYINSERT命令。

说明

当恢复使用此选项创建的存档文件时,建议谨慎使用并行恢复。

--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

表示只备份指定的sectionsection的名称可以是pre-datadatapost-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的数据库活动通常由统计收集器收集,如果不需要收集,可以通过PGOPTIONSALTER 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
  • 执行以下命令,备份名称以eastwest开头并以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