Postgresql 备份恢复

简述

备份（backup），通过某种方式（物理复制或者逻辑导出）将数据库的文件或结构和数据拷贝到其他位置进行存储。
还原（restore），一种不完全的恢复，使用备份的文件将数据库恢复到执行备份时的状态。备份时间点之后的数据变更无法通过还原进行恢复。
恢复（recovery），执行恢复时，通常是先使用物理备份文件进行还原，然后再应用备份时间点到故障点之间的日志文件（WAL），将数据库恢复到最新状态。

备份类型

物理备份与逻辑备份

物理备份（Physical Backup） 就是直接复制数据库相关的文件。通常来说，物理备份比逻辑备份更快，但是占用的空间也更大。PostgreSQL 支持在线和离线的物理备份。实际环境中应该以物理备份为主。

逻辑备份（Logical Backup） 就是将数据库的结构和数据导出为 SQL 文件，还原时通过文件中的 SQL 语句和命令重建数据库并恢复数据。逻辑备份通常需要更多的备份和还原时间。逻辑备份可以作为物理备份的补充，或者用于测试目的的数据导入导出。

在线备份与离线备份

在线备份（Online Backup） 是指 PostgreSQL 服务器处于启动状态时的备份，也称为热备份（Hot Backup）。由于逻辑备份需要连接到数据库进行操作，因此逻辑备份只能是在线备份。

离线备份（Offline Backup） 是指 PostgreSQL 服务器处于关闭状态时的备份，也称为冷备份（Cold Backup）。离线状态只能执行数据库的物理备份，即复制数据库文件。

全量备份与增量备份

全量备份（Full Backup） 就是备份所有的数据库文件，执行一次完整的 PostgreSQL 数据库集群备份。这种方式需要备份的内容较多，备份时较慢，但是恢复速度更快。

增量备份（Incremental Backup） 就是备份上一次备份（任何类型）之后改变的文件。
差异备份（Differential Backup） 是针对上一次完全备份后发生变化的所有文件进行备份。增量备份每次备份的数据量较小，但是恢复时需要基于全量备份，并依次恢复增量部分，时间较长。差异备份位于两者之间。

在 PostgreSQL 中是通过一个基准备份（Base Backup），加上不断备份的事务日志文件（WAL）来达到增量备份的效果。

在实际环境中可以结合全量备份和增量/差异备份，以平衡备份和恢复所需的存储空间和时间。

备份恢复工具

• pg_dump：逻辑备份工具，支持单个数据库（可以指定模式、表）的导出，可以选择导出的格式。
• pg_dumpall：逻辑备份工具，用于导出整个数据库集群，包括公用的全局对象。
• pg_basebackup：物理备份工具，为数据库集群创建一个基准备份。它也可以用于时间点恢复（point-in-time recovery）的基准备份，或者设置基于日志传输或流复制的从节点的初始化。
• psql：PostgreSQL 交互式命令行工具，也可以用于导入逻辑备份产生的 SQL 文件。
• pg_restore：逻辑还原工具，用于还原 pg_dump 导出的归档格式的备份文件。
• copy：PostgreSQL 专有的 SQL 语句，将表中的数据复制到文件，或者将文件中的数据复制到表中。

备份与恢复

逻辑备份与还原

执行逻辑备份时，PostgreSQL 服务器必须已经启动，备份工具（例如 pg_dump）通过建立数据库连接，从数据库中查询出相应的结构信息和数据，并生成备份文件。针对不同的备份格式，PostgreSQL 提供了配套的还原工具。

备份单个数据库

PostgreSQL 提供了备份单个数据库的工具 pg_dump。它支持三种文件格式：

• plain：文本格式，输出一个纯文本形式的 SQL 脚本，默认值。还原时直接使用 psql 导入。
• custom：自定义格式，输出一个自定义格式的归档文件，还原时使用 pg_restore 工具。与目录导出格式结合使用时，提供了最灵活的输出格式，它允许在恢复时手动选择和排序已归档的项。这种格式在默认情况还会进行文件的压缩。
• directory：目录格式，输出一个目录格式的归档，还原时使用 pg_restore 工具。这种格式将会创建一个目录，为每个导出的表和大对象都创建一个文件，另外再创建一个内容目录文件，该文件使用自定义格式存储关于导出对象的描述。这种格式在默认情况还会进行文件的压缩，并且支持并行导出。
• tar：打包格式，输出一个 tar 格式的归档，还原时使用 pg_restore 工具。这种格式与目录格式兼容，解压一个 tar 格式的归档将会产生一个目录格式的归档。但是，tar 格式不支持压缩。另外，在使用 tar 格式归档进行还原时，表数据项的相对顺序不能进行改动。

[postgres@192 ~]$ pwd
/home/postgres
[postgres@192 ~]$ pg_dump --help
pg_dump dumps a database as a text file or to other formats.

Usage:
  pg_dump [OPTION]... [DBNAME]

General options:
  -- 导出文件名
  -f, --file=FILENAME          output file or directory name
  -- 导出数据格式（text, 自定义转储格式，目录，tar）
  -F, --format=c|d|t|p         output file format (custom, directory, tar,
                               plain text (default))
  -- 导出并发数
  -j, --jobs=NUM               use this many parallel jobs to dump
  -v, --verbose                verbose mode
  -V, --version                output version information, then exit
  -- 使用gzip压缩
  -Z, --compress=0-9           compression level for compressed formats
  --lock-wait-timeout=TIMEOUT  fail after waiting TIMEOUT for a table lock
  --no-sync                    do not wait for changes to be written safely to disk
  -?, --help                   show this help, then exit

Options controlling the output content:
  -- 只输出数据，不输出模式(数据对象的定义)。这个选项只是对纯文本格式有意义。对于归档格式，可以在调用pg_restore时指定选项。
  -a, --data-only              dump only the data, not the schema
  -- 在输出中是否包含大对象。除非指定了选择性输出的选项 --schema、–table、–schema-only 开关，否则默认会输出大对象。
  -b, --blobs                  include large objects in dump
  -B, --no-blobs               exclude large objects in dump
  -- 在输出创建数据库对象的SQL命令之前，先输出删除该数据库对象的SQL命令。这个选项只是对纯文本格式有意义。对于归档格式，可以在调用 pg_restore时指定选项。
  -c, --clean                  clean (drop) database objects before recreating
  -- 先输出创建数据库的命令，之后再重新连接新创建的数据库。对于此种格式的脚本，在运行之前是和哪个数据库进行连接就不这么重要了。这个选项只是对纯文本格式有意义。对于归档格式，可以在调用pg_restore时指定选项。
  -C, --create                 include commands to create database in dump
  -e, --extension=PATTERN      dump the specified extension(s) only
  -- 以指定的字符集编码创建输出，默认为数据库字符集。
  -E, --encoding=ENCODING      dump the data in encoding ENCODING
  -- 只转储schema的内容。如果没有声明该选项，目标数据库中的所有非系统模式都会被转储。该选项也可以被多次指定，以指定不同pattern的模式。
  -n, --schema=PATTERN         dump the specified schema(s) only
  -- 不转储匹配schema的内容，其他规则和-n一致。
  -N, --exclude-schema=PATTERN do NOT dump the specified schema(s)
  -- 不输出设置对象所有权的SQL命令。
  -O, --no-owner               skip restoration of object ownership in
                               plain-text format
  -- 只导出对象定义不导出数据
  -s, --schema-only            dump only the schema, no data
  -- 指定超级用户
  -S, --superuser=NAME         superuser user name to use in plain-text format
  -- 只转储指定名称的表
  -t, --table=PATTERN          dump the specified table(s) only
  -- 不转储指定名称的表
  -T, --exclude-table=PATTERN  do NOT dump the specified table(s)
  -- 不转储权限 (grant/revoke)
  -x, --no-privileges          do not dump privileges (grant/revoke)
  -- 只能由升级工具使用
  --binary-upgrade             for use by upgrade utilities only
  -- 以带有列名的insert命令形式转储数据
  --column-inserts             dump data as INSERT commands with column names
  -- 取消美元 (符号) 引号, 使用 SQL 标准引号
  --disable-dollar-quoting     disable dollar quoting, use SQL standard quoting
  -- 禁用触发器
  --disable-triggers           disable triggers during data-only restore
  --enable-row-security        enable row security (dump only content user has
                               access to)
  -- 不转储指定表的数据                               
  --exclude-table-data=PATTERN do NOT dump data for the specified table(s)
  --extra-float-digits=NUM     override default setting for extra_float_digits
  --if-exists                  use IF EXISTS when dropping objects
  --include-foreign-data=PATTERN
                               include data of foreign tables on foreign
                               servers matching PATTERN
  -- 将数据转储为insert命令，而不是copy命令                               
  --inserts                    dump data as INSERT commands, rather than COPY
  --load-via-partition-root    load partitions via the root table
  --no-comments                do not dump comments
  --no-publications            do not dump publications
  -- 不分配安全标签进行转储
  --no-security-labels         do not dump security label assignments
  --no-subscriptions           do not dump subscriptions
  -- 不在并行任务中使用同步快照
  --no-synchronized-snapshots  do not use synchronized snapshots in parallel jobs
  -- 不转储表空间分配信息
  --no-tablespaces             do not dump tablespace assignments
  --no-toast-compression       do not dump TOAST compression methods
  -- 不转储未标记的表数据
  --no-unlogged-table-data     do not dump unlogged table data
  --on-conflict-do-nothing     add ON CONFLICT DO NOTHING to INSERT commands
  -- 引用所有标识符( 不是关键字 )
  --quote-all-identifiers      quote all identifiers, even if not key words
  --rows-per-insert=NROWS      number of rows per INSERT; implies --inserts
  -- 转储命名部分(pre-data, data, or post-data)
  --section=SECTION            dump named section (pre-data, data, or post-data)
  -- 等待没有异常的情况下进行转储
  --serializable-deferrable    wait until the dump can run without anomalies
  --snapshot=SNAPSHOT          use given snapshot for the dump
  --strict-names               require table and/or schema include patterns to
                               match at least one entity each
  -- 使用SET SESSION AUTHORIZATION命令而不是ALTER OWNER命令来设置所有权
  --use-set-session-authorization
                               use SET SESSION AUTHORIZATION commands instead of
                               ALTER OWNER commands to set ownership

Connection options:
  -- 导出的数据库名字
  -d, --dbname=DBNAME      database to dump
  -- 连接到指定数据库主机地址
  -h, --host=HOSTNAME      database server host or socket directory
  -- 连接到指定数据库端口
  -p, --port=PORT          database server port number
  -- 连接使用的用户名
  -U, --username=NAME      connect as specified database user
  -- 从不提示密码，该选项常用于后台脚本中
  -w, --no-password        never prompt for password
  -- 强制 pg_dump 在连接到一个数据库之前提示密码，某些情况下可避免额外的连接尝试。
  -W, --password           force password prompt (should happen automatically)
  -- 该选项会导致 pg_dump 在连接到数据库时发布一个 set role rolename 命令。相当于切换到另一个角色。当已验证的用户缺少 pg_dump需要的权限时，可以使用这个功能切换到一个相应权限的角色。
  --role=ROLENAME          do SET ROLE before dump

If no database name is supplied, then the PGDATABASE environment
variable value is used.

Report bugs to <pgsql-bugs@lists.postgresql.org>.
PostgreSQL home page: <https://www.postgresql.org/>
[postgres@192 ~]$