从零开始学PostgreSQL-工具篇： 备份与恢复

# 通用选项
-f, --file=FILENAME          # 输出文件或目录的名称。
-F, --format=c|d|t|p         # 输出文件格式（自定义[c]、目录[d]、tar[t]、纯文本[p]，默认为纯文本）。
-j, --jobs=NUM               # 使用指定数量的并行任务来执行数据库转储。
-v, --verbose                # 详细模式。
-V, --version                # 输出版本信息，然后退出。
-Z, --compress=METHOD[:DETAIL] # 按指定的方式压缩输出。
--lock-wait-timeout=TIMEOUT  # 在等待表锁超时后失败，超时时间为TIMEOUT。
--no-sync                    # 不等待更改安全地写入磁盘。
-?, --help                   # 显示帮助，然后退出。

# 控制输出内容的选项
-a, --data-only              # 只转储数据，不转储模式。
-b, --large-objects          # 在转储中包含大对象。
--blobs                      # （与--large-objects相同，已弃用）
-B, --no-large-objects       # 在转储中排除大对象。
--no-blobs                   # （与--no-large-objects相同，已弃用）
-c, --clean                  # 清理（删除）数据库对象后再重新创建。
-C, --create                 # 在转储中包含创建数据库的命令。
-e, --extension=PATTERN      # 只转储指定的扩展。
-E, --encoding=ENCODING      # 以指定的编码ENCODING转储数据。
-n, --schema=PATTERN         # 只转储指定的模式。
-N, --exclude-schema=PATTERN # 不转储指定的模式。
-O, --no-owner               # 在纯文本格式中跳过对象所有权的恢复。
-s, --schema-only            # 只转储模式，不转储数据。
-S, --superuser=NAME         # 在纯文本格式中使用的超级用户名。
-t, --table=PATTERN          # 只转储指定的表。
-T, --exclude-table=PATTERN  # 不转储指定的表。
-x, --no-privileges          # 不转储权限（grant/revoke）。
--binary-upgrade             # 仅用于升级工具。
--column-inserts             # 作为带有列名的INSERT命令转储数据。
--disable-dollar-quoting     # 禁用美元符号引用，使用SQL标准引用。
--disable-triggers           # 在仅数据恢复时禁用触发器。
--enable-row-security        # 启用行级安全性（仅转储用户有访问权限的内容）。
--exclude-table-and-children=PATTERN  # 不转储指定的表及其子表和分区表。
--exclude-table-data=PATTERN # 不转储指定表的数据。
--exclude-table-data-and-children=PATTERN  # 不转储指定表及其子表和分区表的数据。
--extra-float-digits=NUM     # 覆盖extra_float_digits的默认设置。
--if-exists                  # 在删除对象时使用IF EXISTS。
--include-foreign-data=PATTERN # 包含与指定模式匹配的外部服务器上的外部表数据。
--inserts                    # 作为INSERT命令而不是COPY命令转储数据。
--load-via-partition-root    # 通过根表加载分区。
--no-comments                # 不转储注释。
--no-publications            # 不转储发布。
--no-security-labels         # 不转储安全标签分配。
--no-subscriptions           # 不转储订阅。
--no-table-access-method     # 不转储表访问方法。
--no-tablespaces             # 不转储表空间分配。
--no-toast-compression       # 不转储TOAST压缩方法。
--no-unlogged-table-data     # 不转储未登录表的数据。
--on-conflict-do-nothing     # 在INSERT命令中添加ON CONFLICT DO NOTHING。
--quote-all-identifiers      # 引用所有标识符，即使它们不是关键字。
--rows-per-insert=NROWS      # 每个INSERT命令中的行数；隐含--inserts。
--section=SECTION            # 转储命名的段（预数据、数据或后数据）。
--serializable-deferrable    # 等待直到可以无异常地运行转储。
--snapshot=SNAPSHOT          # 使用给定的快照进行转储。
--strict-names               # 要求表和/或模式包含模式至少匹配一个实体。
--table-and-children=PATTERN # 只转储指定的表及其子表和分区表。
--use-set-session-authorization # 使用SET SESSION AUTHORIZATION命令代替ALTER OWNER命令来设置所有权。

# 连接选项
-d, --dbname=DBNAME      # 要转储的数据库名称。
-h, --host=HOSTNAME      # 数据库服务器的主机名或套接字目录。
-p, --port=PORT          # 数据库服务器的端口号。
-U, --username=NAME      # 以指定的数据库用户连接。
-w, --no-password        # 永远不要提示输入密码。
-W, --password           # 强制提示输入密码（应自动发生）。
--role=ROLENAME          # 在转储前执行SET ROLE。

# 如果没有提供数据库名称，则使用PGDATABASE环境变量的值。

要将数据库转储到自定义格式的存档文件中，请执行以下操作：
pg_dump -U postgres -h 127.0.0.1 -p 5432 -W -Fc -d mydb  > db.dump

要将数据库转储到目录格式存档中，请执行以下操作：
pg_dump -U postgres -h 127.0.0.1 -p 5432 -W -Fd -d mydb -f dumpdir

要将数据库转储到目录格式存档中，同时执行 5 个工作线程作业：
pg_dump -U postgres -h 127.0.0.1 -p 5432 -W -Fd -d mydb -j 5 -f dumpdir

转储名为 ：mytab
pg_dump -U postgres -h 127.0.0.1 -p 5432 -W -d mydb  > mytab.sql

转储以emp开头的所有表，排除名为 ：empdetroitemployee_log集合
pg_dump -U postgres -h 127.0.0.1 -p 5432 -W -t 'detroit.emp*' -T detroit.employee_log mydb > db.sql

转储名称以 或 开头且以 结尾的所有集合，排除名称包含单词test ：的集合
pg_dump -U postgres -h 127.0.0.1 -p 5432 -W  -n 'west*gsm' -N '*test*' mydb > db.sql

同样，使用正则表达式表示法来合并开关：
pg_dump -U postgres -h 127.0.0.1 -p 5432 -W -n '(east|west)*gsm' -N '*test*' mydb > db.sql

转储除名称以ts_*开头的集合之外的所有集合
pg_dump -U postgres -h 127.0.0.1 -p 5432 -W -T 'ts_*' mydb > db.sql

转储具有混合大小写名称的单个表，您需要类似-t
pg_dump -U postgres -h 127.0.0.1 -p 5432 -W -t "\"MixedCaseName\"" mydb > mytab.sql

备份数据库结构（不包含数据）
pg_dump -U postgres -h 127.0.0.1 -p 5432 -W --schema-only mydatabase > mydatabase_schema_only.sql

备份数据库数据（不包含结构）
pg_dump -U postgres -h 127.0.0.1 -p 5432 -W --data-only mydatabase > mydatabase_schema_only.sql

备份多个特定表
pg_dump -U postgres -h 127.0.0.1 -p 5432 -W -t table1 -t table2 mydatabase > tables_backup.sql

备份数据库并压缩
pg_dump -U postgres -h 127.0.0.1 -p 5432 -W -d mydb | gzip > mydatabase_backup.sql.gz

备份并导出为 INSERT 语句
pg_dump -U postgres -h 127.0.0.1 -p 5432 -W --format=c --large-objects --inserts mydatabase > insert_statements.sql

pg_dump -U postgres -h 127.0.0.1 -p 5432 -W  --schema-only mydatabase > all_schemas_structure.sql

# 使用语法
pg_dumpall [OPTION]...

# 通用选项
-f, --file=FILENAME          # 输出文件名
-v, --verbose                # 详细模式
-V, --version                # 输出版本信息，然后退出
--lock-wait-timeout=TIMEOUT  # 锁等待超时后失败，单位为毫秒
-?, --help                   # 显示此帮助，然后退出

# 控制输出内容的选项
-a, --data-only              # 仅转储数据，不转储模式
-c, --clean                  # 清除（删除）数据库后再重新创建
-E, --encoding=ENCODING      # 以编码ENCODING转储数据
-g, --globals-only           # 仅转储全局对象，不包括数据库
-O, --no-owner               # 跳过对象所有权的恢复
-r, --roles-only             # 仅转储角色，不包括数据库或表空间
-s, --schema-only            # 仅转储模式，不包括数据
-S, --superuser=NAME         # 超级用户用户名，用于转储中
-t, --tablespaces-only       # 仅转储表空间，不包括数据库或角色
-x, --no-privileges          # 不转储权限（授予/撤销）
--binary-upgrade             # 仅供升级工具使用
--column-inserts             # 将数据转储为带有列名的INSERT命令
--disable-dollar-quoting     # 禁用美元符引用，使用SQL标准引用
--disable-triggers           # 在仅数据恢复时禁用触发器
--exclude-database=PATTERN   # 排除名称匹配PATTERN的数据库
--extra-float-digits=NUM     # 覆盖默认的extra_float_digits设置
--if-exists                  # 删除对象时使用IF EXISTS
--inserts                    # 将数据转储为INSERT命令，而非COPY命令
--load-via-partition-root    # 通过根表加载分区
--no-comments                # 不转储注释
--no-publications            # 不转储发布
--no-role-passwords          # 不转储角色密码
--no-security-labels         # 不转储安全标签分配
--no-subscriptions           # 不转储订阅
--no-sync                    # 不等待变更被安全地写入磁盘
--no-table-access-method     # 不转储表访问方法
--no-tablespaces             # 不转储表空间分配
--no-toast-compression       # 不转储TOAST压缩方法
--no-unlogged-table-data     # 不转储未登录表的数据
--on-conflict-do-nothing     # 在INSERT命令中添加ON CONFLICT DO NOTHING
--quote-all-identifiers      # 引用所有标识符，即使它们不是关键字
--rows-per-insert=NROWS      # 每个INSERT语句中的行数；隐含--inserts
--use-set-session-authorization

#备份整个数据库以及角色
pg_dumpall  -U postgres -h 127.0.0.1 -p 5432 -W -f alldb_backup.sql
#备份所有数据库角色和全局对象
pg_dumpall  -U postgres -h 127.0.0.1 -p 5432 -W --globals-only -f globals_backup.sql
#仅备份角色
pg_dumpall  -U postgres -h 127.0.0.1 -p 5432 -W --roles-only  -f alldb_backup.sql
#仅备份数据
pg_dumpall  -U postgres -h 127.0.0.1 -p 5432 -W --data-only  -f alldb_backup.sql
#忽略某个库
pg_dumpall  -U postgres -h 127.0.0.1 -p 5432 -W -f  alldb_backup.sql --exclude-database=mydb > alldb_backup.sql
#将数据转储为带有列名的INSERT命令
pg_dumpall  -U postgres -h 127.0.0.1 -p 5432 -W --column-inserts   -f alldb_backup.sql
#将数据转储为INSERT命令
pg_dumpall  -U postgres -h 127.0.0.1 -p 5432 -W --inserts   -f alldb_backup.sql
#备份压缩
pg_dumpall  -U postgres -h 127.0.0.1 -p 5432 -w --inserts   | gzip > alldb_backup.sql.gz

# 使用语法
pg_basebackup [OPTION]...

# 控制输出的选项
-D, --pgdata=DIRECTORY    # 将基线备份接收至指定目录
-F, --format=p|t          # 输出格式（plain（默认），tar）
-r, --max-rate=RATE       # 设置数据目录传输的最大速率（单位为kB/s，或使用"k"或"M"后缀）
-R, --write-recovery-conf # 写入用于复制的配置文件
-t, --target=TARGET[:DETAIL]
                          # 备份目标（如果不同于客户端）
-T, --tablespace-mapping=OLDDIR=NEWDIR
                          # 将位于OLDDIR的表空间重定位至NEWDIR
--waldir=WALDIR           # 写前日志（WAL）目录的位置
-X, --wal-method=none|fetch|stream
                          # 使用指定的方法包含所需的WAL文件
-z, --gzip                # 压缩tar输出
-Z, --compress=[{client|server}-]METHOD[:DETAIL]
                          # 按指定方式在客户端或服务器上进行压缩
-Z, --compress=none       # 不压缩tar输出

# 通用选项
-c, --checkpoint=fast|spread
                          # 设置快速或分散的检查点
-C, --create-slot         # 创建复制槽
-l, --label=LABEL         # 设置备份标签
-n, --no-clean            # 出现错误后不清理
-N, --no-sync             # 不等待更改被安全地写入磁盘
-P, --progress            # 显示进度信息
-S, --slot=SLOTNAME       # 使用的复制槽
-v, --verbose             # 输出详细信息
-V, --version             # 输出版本信息，然后退出
--manifest-checksums=SHA{224,256,384,512}|CRC32C|NONE
                          # 使用算法进行清单校验和
--manifest-force-encode   # 对清单中的所有文件名进行十六进制编码
--no-estimate-size        # 不在服务器端估计备份大小
--no-manifest             # 抑制生成备份清单
--no-slot                 # 阻止创建临时复制槽
--no-verify-checksums     # 不验证校验和
-?, --help                # 显示此帮助，然后退出

# 连接选项
-d, --dbname=CONNSTR      # 连接字符串
-h, --host=HOSTNAME       # 数据库服务器主机名或套接字目录
-p, --port=PORT           # 数据库服务器端口号
-s, --status-interval=INTERVAL
                          # 发送至服务器的状态包的时间间隔（单位为秒）
-U, --username=NAME       # 作为指定的数据库用户连接
-w, --no-password         # 从不提示输入密码
-W, --password            # 强制密码提示（应自动发生）

普通文件：
pg_basebackup -h 10.10.9.7 -U replication_user -p 5432 -Xs -v -Fp -P -r 100M -R -D /root/zz/
压缩：
pg_basebackup -h 10.10.9.7 -U replication_user -p 5432 -Xs -v -Ft -P -r 100M -R -D /root/zz/

# 使用语法
pg_restore [OPTION]... [FILE]

# 通用选项
-d, --dbname=NAME          # 连接到指定的数据库名
-f, --file=FILENAME        # 输出文件名 (- 表示标准输出)
-F, --format=c|d|t        # 备份文件格式（通常应自动识别）
-l, --list                 # 打印归档的TOC（目录）摘要
-v, --verbose              # 详细模式
-V, --version              # 输出版本信息，然后退出
-?, --help                 # 显示帮助信息，然后退出

# 控制恢复的选项
-a, --data-only            # 只恢复数据，不恢复模式(schema)
-c, --clean                # 清理（删除）数据库对象之前重新创建
-C, --create               # 创建目标数据库
-e, --exit-on-error        # 出错时退出，默认是继续执行
-I, --index=NAME           # 恢复指定名称的索引
-j, --jobs=NUM             # 使用多个并行任务来恢复
-L, --use-list=FILENAME    # 使用此文件中的TOC选择/排序输出
-n, --schema=NAME          # 只恢复此模式(schema)中的对象
-N, --exclude-schema=NAME  # 不恢复此模式(schema)中的对象
-O, --no-owner             # 跳过对象所有权的恢复
-P, --function=NAME(args)  # 恢复指定名称的函数
-s, --schema-only          # 只恢复模式(schema)，不恢复数据
-S, --superuser=NAME       # 用于禁用触发器的超级用户名
-t, --table=NAME           # 恢复指定名称的关系（表，视图等）
-T, --trigger=NAME         # 恢复指定名称的触发器
-x, --no-privileges        # 跳过访问权限的恢复（grant/revoke）
-1, --single-transaction   # 作为一个单一的事务恢复
--disable-triggers         # 在仅数据恢复期间禁用触发器
--enable-row-security      # 启用行级安全性
--if-exists                # 在删除对象时使用IF EXISTS
--no-comments              # 不恢复注释
--no-data-for-failed-tables # 不恢复未能创建的表的数据
--no-publications          # 不恢复发布(publications)
--no-security-labels       # 不恢复安全标签
--no-subscriptions         # 不恢复订阅
--no-table-access-method   # 不恢复表访问方法
--no-tablespaces           # 不恢复表空间分配
--section=SECTION          # 恢复指定部分（pre-data, data, 或 post-data）
--strict-names             # 要求表和/或模式包含模式至少匹配每个实体
--use-set-session-authorization
                           # 使用SET SESSION AUTHORIZATION命令而不是ALTER OWNER命令来设置所有权

# 连接选项
-h, --host=HOSTNAME        # 数据库服务器主机名或套接字目录
-p, --port=PORT            # 数据库服务器端口号
-U, --username=NAME        # 以指定的数据库用户身份连接
-w, --no-password          # 从不提示输入密码
-W, --password             # 强制密码提示（应自动发生）
--role=ROLENAME            # 在恢复前执行SET ROLE

# 注意事项
# -I, -n, -N, -P, -t, -T 和 --section 选项可以组合并多次指定以选择多个对象。

# 如果没有提供输入文件名，则使用标准输入。

1: 恢复整个数据库
pg_restore -U postgres -h 127.0.0.1 -p 5432 -W -d mydb db.dump

2：恢复到指定的数据库并使用不同的用户名
pg_restore -U username -d new_database db.dump

3：恢复部分数据库对象
恢复指定表
pg_restore -d new_database -t table_name db.dump
恢复的模式名
pg_restore -d new_database -n schema_name db.dump

4：恢复时使用 --create 选项创建数据库
pg_restore --create -d postgres db.dump

5：恢复到现有数据库，并使用并行恢复
pg_restore -d new_database -j 4 db.dump

6：将恢复输出重定向到文件
pg_restore -U postgres -h 127.0.0.1 -p 5432 -W --file=output.sql db.dump

7：只恢复数据，不恢复表结构
pg_restore -U postgres -h 127.0.0.1 -p 5432 -W  -d mydb --data-only  -j 4  db.dump

8：报错时退出，默认继续执行
pg_restore -d new_database -j 4 --exit-on-error db.dump 

9：不恢复此模式(schema)中的表
pg_restore -d new_database -j 4 --exit-on-error --exclude-schema=NAME db.dump 

10：跳过权限的恢复
pg_restore -d new_database -j 4 --exit-on-error --no-owner --no-privileges db.dump 

11：以事务方式导入
pg_restore -U postgres -h 127.0.0.1 -p 5432 -W  -d mydb --single-transaction db.dump