目录
1、概述
2、pg_dump
3、pg_dumpall
4、pg_restore
5、pg_basebackup
6、总结
概述
PostgreSQL 提供了一系列强大的工具来备份和恢复数据库。这些工具包括 pg_dump, pg_restore,pg_dumpall,pg_basebackup。下面是这些工具的简要概述和一些注意事项：
1. pg_dump
概述：pg_dump 是一个用于备份单个 PostgreSQL 数据库的工具。它可以生成 SQL 文件或自包含的归档文件，其中包含了创建和填充数据库所需的所有 SQL 命令。
用途：备份单个数据库。
2. pg_restore
概述：pg_restore 是一个用于从创建的备份文件恢复数据的工具。
用途：恢复数据库。
3. pg_dumpall
概述：pg_dumpall 用于备份整个 PostgreSQL 集群，包括所有数据库、角色、表空间等。
用途：备份整个 PostgreSQL 集群。
4. pg_basebackup
概述：pg_basebackup 用于创建整个 PostgreSQL 数据目录的物理备份，这对于灾难恢复尤为重要。
用途：备份整个 PostgreSQL 数据目录。
pg_dump
pg_dump参数详解
# 通用选项
-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使用示例
要将数据库转储到自定义格式的存档文件中，请执行以下操作：
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.sqlpg_dumpall
pg_dumpall参数详解
# 使用语法
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-authorizationpg_dumpall使用示例
#备份整个数据库以及角色
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.gzpg_basebackup
pg_basebackup参数详解
# 使用语法
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使用示例
普通文件：
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
pg_restore参数详解
# 使用语法
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 选项可以组合并多次指定以选择多个对象。
# 如果没有提供输入文件名，则使用标准输入。pg_restore使用示例
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总结
选择适当的工具，根据备份的需求选择合适的工具。pg_dump 用于单个数据库的备份和恢复，pg_dumpall 用于整个集群的备份，pg_basebackup 用于物理备份。
备份策略：制定定期备份计划，并测试恢复过程，确保备份文件可用。
安全：备份文件可能包含敏感数据，应妥善保管备份文件，并考虑使用加密。
测试：定期测试备份文件的恢复，以确保在需要时能够正确恢复数据。
性能：对于大型数据库，考虑使用流式备份或并行备份来提高备份和恢复的速度。
权限：确保执行备份和恢复的用户具有适当的权限。
公众号内直接回复加群也可以,扫码加群也可以，觉得帖子写的不错，点点关注，点点赞，多多转载，请多多支持。
从零开始学PostgreSQL (十二)：高效批量写入数据库
扫一扫
576
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
