数据库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

pg_dump -U postgres -h 127.0.0.1 -p 5432 -W -Fd -d mydb -j 5 -f dumpdir

pg_dump -U postgres -h 127.0.0.1 -p 5432 -W -d mydb > mytab.sql

pg_dump -U postgres -h 127.0.0.1 -p 5432 -W -t 'detroit.emp*' -T detroit.employee_log mydb > db.sql

pg_dump -U postgres -h 127.0.0.1 -p 5432 -W -n 'east*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

pg_dump -U postgres -h 127.0.0.1 -p 5432 -W -T 'ts_*' mydb > db.sql

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

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 选项可以组合并多次指定以选择多个对象。

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

pg_restore -U postgres -h 127.0.0.1 -p 5432 -W -d mydb db.dump

pg_restore -U username -d new_database db.dump

pg_restore -d new_database -t table_name db.dump

pg_restore -d new_database -n schema_name db.dump

pg_restore --create -d postgres db.dump

pg_restore -d new_database -j 4 db.dump

pg_restore -U postgres -h 127.0.0.1 -p 5432 -W --file=output.sql db.dump

pg_restore -U postgres -h 127.0.0.1 -p 5432 -W -d mydb --data-only -j 4 db.dump

pg_restore -d new_database -j 4 --exit-on-error db.dump 

pg_restore -d new_database -j 4 --exit-on-error --exclude-schema=NAME db.dump 

pg_restore -d new_database -j 4 --exit-on-error --no-owner --no-privileges db.dump 

pg_restore -U postgres -h 127.0.0.1 -p 5432 -W -d mydb --single-transaction db.dump