PostgreSQL的备份和恢复-SQL转储
与所有包含有价值数据的内容一样，应定期备份 PostgreSQL 数据库。虽然该过程本质上很简单，但清楚了解基础技术和假设非常重要。
有三种从根本上不同的方法来备份 PostgreSQL 数据
- SQL 转储
- 文件系统级备份
- 连续归档
本文将重点介绍SQL转储使用的pg_dump、pg_dumpall、pg_restore三个命令。
1. SQL转储
1.1 基本总结
1.1.1 pg_dump
概述
PostgreSQL 数据库因其强大的功能和可靠性被广泛应用于各类业务场景，而数据备份是数据库运维中至关重要的一环。PostgreSQL 提供了多种内置的备份工具，包括 pg_dump和pg_dumpall，它们在不同的备份策略和恢复场景下发挥着关键作用。本文将详细介绍这两种工具的区别以及其具体使用方法。
基本用法
pg_dump [选项] [数据库名]
pg_dump [选项] [数据库名] > [存储文件名]
命令选项
-
-h 或 --host：指定数据库服务器的主机名或IP地址。
-
-U 或 -username：指定连接数据库时使用的用户名。
-
-p 或 --port：指定数据库服务器的端口号。
-
-d 或 --dbname：指定要导出的数据库名称。
-
-f 或 --file：指定输出文件的路径。
-
-F 或 --format：指定导出文件的格式（plain, custom, tar, directory）。
-
-c 或 --clean：在导入时清理（删除）数据库。
-
-a 或 --data-only：只导出数据，不导出结构（表定义）。
-
-s 或 --schema-only：只导出结构，不导出数据。
-
-b 包含事务块开始点，以便于一致性的恢复。
-
-v 开启详细输出模式。
通用选项：
-f, --file=FILENAME 输出文件名或者目录名
-F, --format=c|d|t|p 输出文件格式（自定义格式、目录格式、TAR包格式、纯文本）
-j, --jobs=NUM 使用这多个并行作业进行转储
-v, --verbose 详细信息模式
-Z, --compress=0-9 压缩格式的压缩级别
--lock-wait-timeout=TIMEOUT 等待表锁超时后操作失败
--no-sync 不等待变更安全写入磁盘 ➊
--help 显示此帮助信息并退出
-v, --version 输出版本信息并退出
控制输出内容的选项：
-a, --data-only 仅转储数据，而不转储schema(数据定义)
-b, --blobs 在转储中包含大对象
-B, --no-blobs 不对大对象进行备份 ➋
-c, --clean 在重新创建数据库对象之前清除（删除）数据库对象
-C, --create 包含用于在转储中创建数据库的命令
-E, --encoding=ENCODING 以ENCODING编码格式转储数据
-n, --schema=SCHEMA 仅转储命名schema
-N, --exclude-schema=SCHEMA 不转储命名schema
-o, --oids 在转储中包含OID
-O, --no-owner 以纯文本格式跳过对象所有权的恢复
-s, --schema-only 仅转储schema，而不转储数据
-S, --superuser=NAME 要以纯文本格式使用的超级用户名
-t, --table=TABLE 仅转储命名表
-T, --exclude-table=TABLE 不转储命名表
-x, --no-privileges 不转储特权（grant/revoke）
--binary-upgrade 仅供升级工具使用
--column-inserts 以带有列名的INSERT命令的形式转储数据
--disable-dollar-quoting 禁用美元（符号）引号，而是使用SQL标准引号
--disable-triggers 在仅恢复数据期间禁用触发器
--enable-row-security 启用行级安全控制（只导出用户有权访问的数据）➌
--exclude-table-data=TABLE 不转储命名表中的数据
--if-exists 删除对象时使用IF EXISTS
--inserts 以INSERT命令（而非COPY命令）的形式转储数据
--no-publications 不导出逻辑复制发布端数据源定义 ➍
--no-security-labels 不转储安全标签分配
--no-subscriptions 不导出逻辑复制订阅端的数据订阅定义 ➎
--no-synchronized-snapshots 在并行作业中不使用同步快照
--no-tablespaces 不转储表空间分配
--no-unlogged-table-data 不转储不记录WAL日志的表的数据
--quote-all-identifiers 所有标识符加引号，即使不是关键字也加
--section=SECTION 转储命名部分（包括三个部分：pre-data、data以及post-data。
data部分包含表记录数据、大对象数据以及序列的值；
post-data部分包含索引、触发器、规则和约束（除了验证检查约束）的定义；
pre-data部分包含此外其他所有的对象定义）
--serializable-deferrable 等待直至转储正常运行为止
--snapshot=SNAPSHOT 为导出使用指定的快照 ➏
--strict-names 要求每个表和/或schema包括模式以匹配至少一个实体 ➐
--use-set-session-authorization 使用 SESSION AUTHORIZATION 命令代 ALTER OWNER 命令来设置所有权
连接选项：
-d, --dbname=DBNAME 要转储的数据库
-h, --host=主机名 数据库服务器主机或套接字目录
-p, --port=端口号 数据库服务器端口号
-U, --username=名称 作为指定数据库用户连接
-w, --no-password 永远不提示输入密码
-W, --password 强制要求输入密码（应该自动发生）
--role=ROLENAME 在转储之前执行SET ROLE命令
➊➋➌ PostgreSQL 10 中引入的新特性。
➍ PostgreSQL 9.6 中引入的新特性。
➎➏ PostgreSQL 9.5 中引入的新特性。
➐ PostgreSQL 9.4 中引入的新特性。
备份数据库
pg_dump test1 > test1.sql
- 备份整个数据库到 SQL 文件
pg_dump -U fbase -h localhost -p 8432 test1 -f test1_backup.sql
- 备份整个数据库到自定义格式文件
pg_dump -U fbase -h localhost -p 8432 test1 -F c -f test1_backup.dump
- 备份特定表到 SQL 文件
pg_dump -U fbase -h localhost -p 8432 test1 -t test1 -f test1_backup.sql
恢复数据库
psql dbname < dumpfile
- 使用
pg_restore工具可以恢复
pg_restore -U fbase -h localhost -p 8432 -d test1 test1_backup.dump
- 使用
psql工具可以恢复纯文本格式备份文件
psql -U fbase -h localhost -p 8432 -d test1 -f test1_backup.sql
1.1.2 pg_dumpall
概述
pg_dumpall 是一个全局级别的逻辑备份工具，它可以生成当前 PostgreSQL 实例中所有数据库的 SQL 脚本，包括用户、角色、表空间、权限设置以及所有数据库的数据和结构。
基本用法
pg_dumpall [选项] ...
pg_dumpall > dumpfile
命令选项
常规选项：
-f, --file=FILENAME 输出文件名
-v, --verbose 详细模式
-V, --version 输出版本信息，然后退出
--lock-wait-timeout=TIMEOUT 等待表锁超时后失败
-?, --help 显示此帮助，然后退出
-F <格式> 指定备份文件的格式，对于 pg_dumpall 来说，默认和推荐的格式是 `plain` SQL 格式。
--clean 在备份脚本开始处添加删除数据库对象的命令，以便在恢复前清理目标数据库。
--globals-only 只导出全局对象（如角色、表空间等），不导出任何数据库的内容。
控制输出内容的选项：
-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-tablespaces 不转储表空间分配
--no-unlogged-table-data 不转储未记录的表数据
--on-conflict-do-nothing 添加 ON CONFLICT DO NOTHING 到 INSERT 命令
--quote-all-identifiers 引用所有标识符，即使不是关键字
--rows-per-insert=NROWS 每个插入的行数；暗示 --inserts
--use-set-session-authorization
使用 SET SESSION AUTHORIZATION 命令而不是 ALTER OWNER 命令来设置所有权
连接选项：
-d, --dbname=CONNSTR 使用连接字符串连接
-h, --host=HOSTNAME 数据库服务器主机或套接字目录
-l, --database=DBNAME 替代默认数据库
-p, --port=PORT 数据库服务器端口号
-U, --username=NAME 以指定的数据库用户连接
-w, --no-password 从不提示输入密码
-W, --password 强制密码提示（应该自动发生）
--role=ROLENAME 在转储前做 SET ROLE
➊➋➌➍ PostgreSQL 10 中引入的新特性。
演示示例
- 导出所有数据库
$ pg_dumpall > pg_all.sql
- 导出所有Role 和Tablespace
pg_dumpall -g > pg_globle.sql
pg_dumpall -g -h 127.0.0.1 > pg_globle.sql
#仅导出Role：
pg_dumpall -r >pg_roles.sql
# 仅导出Tablespace:
pg_dumpall -t > pg_tablespace.sql
- 恢复
# 导入 :不存在的会导入，存在的不导入
psql -f pg_roles.sql
psql -f pg_roles.sql -h 127.0.0.1
1.1.3 pg_restore
概述
可以使用 pg_restore 可恢复使用 pg_dump 创建的备份文件，这些备份文件的格式包括 TAR 包格式、自定义压缩格式以及目录格式等。
功能：
（1）支持并行恢复，使用-j选项可以控制并行恢复的线程数。多个恢复线程可以并行处理，每个线程处理一张表。该模式可以显著提高恢复速度。
（2）可以使用pg_restore扫描备份文件来生成一张备份内容列表，通过该列表可以确认备份红包含了哪些内容。还可以通过编辑该内容列表来控制恢复哪些内容。
（3）pg_dump支持选择性地仅备份部分对象以节省备份时间，类似的，pg_restore也支持选择性的仅恢复部分对象，不管备份文件本身是全库备份还是部分对象的备份都没有问题。
（4）pg_restore的大部分功能是向后兼容的，即支持将老版本PostgreSQL生产的备份数据恢复到新版本的PostgreSQL中。
基本用法
pg_restore [选项]... [文件名]
命令选项
通用选项：
-d, --dbname=NAME 连接到数据库名称
-f, --file=文件名 输出文件名
-F, --format=c|d|t 备份文件格式（应该是自动的）
-l, --list 打印存档的汇总目录
-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 将此文件的目录用于选择输出或对输出进行排序
-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 作为单个事务恢复
--enable-row-security 启用行安全性 ➌
--disable-triggers 在仅恢复数据期间禁用触发器
--no-data-for-failed-tables 如果表创建失败，则不对其进行数据恢复
--no-publications 不导出逻辑复制发布端数据源定义 ➍
--no-security-labels 不恢复安全标签
--no-subscriptions 不导出逻辑复制订阅端的数据订阅定义 ➎
--no-tablespaces 不恢复表空间分配
--section=SECTION 恢复命名部分（包括三个部分：pre-data、data以postdata。
data部分包含表记录数据、大对象数据以及序列的值；
post-data部分包含索引、触发器、规则和约束（除了验证检查约束）的定义；
pre-data部分包含此外其他所有的对象定义）
--strict-names 要求每个表和/或schema包括模式以匹配至少一个实体 ➏
--use-set-session-authorization 使用SET SESSION AUTHORIZATION命令代替ALTER OWNER命令来设置所有权
连接选项：
-h, --host=主机名 数据库服务器主机或套接字目录
-p, --port=端口号 数据库服务器端口号
-U, --username=名称 作为指定数据库用户连接
-w, --no-password 永远不提示输入密码
-W, --password 强制要求输入密码（应该自动发生）
--role=ROLENAME 在恢复之前执行SET ROLE命令
➊➋➌ PostgreSQL 10 中引入的新特性
➍➎ PostgreSQL 9.6 中引入的新特性。在 9.6 版之前，-t 选项只用于过滤普通表。在 9.6 版中，它拓展支持了外表、视图、物化视图和序列号生成器。
➏ PostgreSQL 9.5 中引入的新特性
演示示例
直接恢复到数据库
pg_dump -Fc mydb > db.dump
pg_restore -C -d postgres db.dump
在
-d开关中命名的数据库可以是群集中存在的任何数据库；pg_restore 仅使用它为mydb发出CREATE DATABASE命令。使用-C，数据始终还原到转储文件中出现的数据库名称中。
列出备份文件db.dmp的内容。
[fbase@localhost pg_sql]$ pg_restore -l db.dump > db.list
[fbase@localhost pg_sql]$ cat db.list
;
; Archive created at 2024-07-16 08:31:58 CST
; dbname: mydb
; TOC Entries: 20
; Compression: gzip
; Dump Version: 1.15-0
; Format: CUSTOM
; Integer: 4 bytes
; Offset: 8 bytes
; Dumped from database version: 16.3
; Dumped by pg_dump version: 16.3
;
;
; Selected TOC Entries:
;
218; 1259 24611 TABLE public cities fbase
217; 1259 24610 SEQUENCE public cities_id_seq fbase
4077; 0 0 SEQUENCE OWNED BY public cities_id_seq fbase
216; 1259 24597 TABLE public t_user fbase
215; 1259 24596 SEQUENCE public t_user_id_seq fbase
4078; 0 0 SEQUENCE OWNED BY public t_user_id_seq fbase
3914; 2604 24614 DEFAULT public cities id fbase
3911; 2604 24600 DEFAULT public t_user id fbase
4070; 0 24611 TABLE DATA public cities fbase
4068; 0 24597 TABLE DATA public t_user fbase
4079; 0 0 SEQUENCE SET public cities_id_seq fbase
4080; 0 0 SEQUENCE SET public t_user_id_seq fbase
3923; 2606 24616 CONSTRAINT public cities cities_pkey fbase
3917; 2606 24609 CONSTRAINT public t_user t_user_account_id_key fbase
3919; 2606 24607 CONSTRAINT public t_user t_user_email_key fbase
3921; 2606 24605 CONSTRAINT public t_user t_user_pkey fbase
分号开头为注释，行首的数字引用分配给每个项目的内部存档 ID。文件中的行可以注释掉、删除和重新排序。这会列出
db.dump文件中所有备份对象的明细，而不执行实际恢复动作。
只恢复备份中的架构
pg_restore -d mydb --schema-only db.dump
postgres=# \l
List of databases
Name | Owner | Encoding | Locale Provider | Collate | Ctype | ICU Locale | ICU Rules | Access privileges
-----------+-------+----------+-----------------+---------+-------+------------+-----------+-------------------
mydb | fbase | UTF8 | libc | C | C | | |
mydb1 | fbase | UTF8 | libc | C | C | | |
postgres | fbase | UTF8 | libc | C | C | | |
template0 | fbase | UTF8 | libc | C | C | | | =c/fbase +
| | | | | | | | fbase=CTc/fbase
template1 | fbase | UTF8 | libc | C | C | | | =c/fbase +
| | | | | | | | fbase=CTc/fbase
test1 | fbase | UTF8 | libc | C | C | | |
test2 | fbase | UTF8 | libc | C | C | | |
(7 rows)
postgres=# \c mydb
You are now connected to database "mydb" as user "fbase".
mydb=# \gt
invalid command \gt
Try \? for help.
mydb=# \dn
List of schemas
Name | Owner
--------+-------------------
public | pg_database_owner
(1 row)
mydb=# select * from public.t_user;
id | name | gender | email | account_id | is_deleted | created_at
----+------+--------+-------+------------+------------+------------
(0 rows)
mydb=# select * from public.cities;
id | city_name | province | is_direct_admin | average_temperature | climate_type
----+-----------+----------+-----------------+---------------------+--------------
(0 rows)
mydb=# \d
List of relations
Schema | Name | Type | Owner
--------+---------------+----------+-------
public | cities | table | fbase
public | cities_id_seq | sequence | fbase
public | t_user | table | fbase
public | t_user_id_seq | sequence | fbase
(4 rows)
这命令恢复
db.dump中的表结构等架构信息，但不包含数据。
使用多个并行作业来加速恢复过程
pg_restore -d mydb1 -j 4 db.dump
postgres=# \c mydb1
You are now connected to database "mydb1" as user "fbase".
mydb1=# \d
List of relations
Schema | Name | Type | Owner
--------+---------------+----------+-------
public | cities | table | fbase
public | cities_id_seq | sequence | fbase
public | t_user | table | fbase
public | t_user_id_seq | sequence | fbase
(4 rows)
mydb1=# select * from t_user;
id | name | gender | email | account_id | is_deleted | created_at
----+--------------+--------+--------------------------+------------+------------+---------------------
1 | Alice Zhang | F | [email protected] | 1001 | f | 2024-07-29 10:00:00
2 | Bob Li | M | [email protected] | 1002 | f | 2024-07-29 11:00:00
3 | Charlie Wang | M | [email protected] | 1003 | f | 2024-07-29 12:00:00
4 | Diana Chen | F | [email protected] | 1004 | f | 2024-07-29 13:00:00
5 | Evan Liu | O | [email protected] | 1005 | f | 2024-07-29 14:00:00
(5 rows)
mydb1=# select * from cities
mydb1-# ;
id | city_name | province | is_direct_admin | average_temperature | climate_type
----+-----------+----------+-----------------+---------------------+----------------
1 | 北京 | 北京市 | t | 12.5 | 温带季风气候
2 | 上海 | 上海市 | t | 15 | 亚热带湿润气候
3 | 广州 | 广东省 | f | 22 | 亚热带湿润气候
4 | 成都 | 四川省 | f | 17 | 亚热带湿润气候
5 | 深圳 | 广东省 | f | 22.5 | 亚热带湿润气候
6 | 武汉 | 湖北省 | f | 18 | 亚热带湿润气候
7 | 西安 | 陕西省 | f | 13 | 温带大陆性气候
8 | 杭州 | 浙江省 | f | 16 | 亚热带湿润气候
9 | 青岛 | 山东省 | f | 14 | 温带海洋性气候
10 | 沈阳 | 辽宁省 | f | 9 | 温带大陆性气候
(10 rows)
这会启用 4 个并行作业来恢复
db.dump到mydb1数据库，加快恢复速度。（需要注意的是，这可能增加数据库的负载）
注意事项
-
在执行
pg_restore命令之前，请确保有足够的权限，如超级用户权限，尤其是在执行创建数据库或安装扩展等操作时。 -
使用 -C 选项时，
pg_restore只尝试创建数据库，但不会设置连接参数（如主机名和端口），你需要确保已经正确配置。 -
恢复过程中可能会因为依赖关系等原因遇到错误。在某些情况下，可能需要多次运行
pg_restore或手动解决这些问题。
pg_restore 是一个强大的工具，可以帮助你从各种格式的 PostgreSQL 备份中恢复数据，特别是当备份文件大或者需要精确控制恢复过程时。
1.1.4 pg_dump和pg_dumpall的区别
-
pg_dump:
-
功能：
pg_dump是一个逻辑备份工具，它能够生成特定数据库或其内部部分（如单个模式、表等）的 SQL 脚本。这个脚本包含了创建表结构、索引、视图、存储过程以及数据内容的 SQL 命令。 -
应用场景：
你可以精确指定要备份哪个数据库或者哪些对象，适用于只备份一部分数据库的需求。
-
特点：
由于是逻辑备份，
pg_dump可以跨不同的 PostgreSQL 版本进行恢复，并且易于在其他服务器上重建数据库，即使目标服务器的硬件或文件系统架构不同。
-
-
pg_dumpall:
-
功能：
pg_dumpall则是一个全局级别的逻辑备份工具，它可以生成当前 PostgreSQL 实例中所有数据库的 SQL 脚本，包括用户、角色、表空间、权限设置以及所有数据库的数据和结构。 -
应用场景：
当你需要一次性备份整个 PostgreSQL 集群的所有数据库时使用
pg_dumpall，而不仅仅是单一数据库。 -
特点：
除了各个数据库的内容外，
pg_dumpall还会导出全局的对象定义，这对于迁移整个集群到新的服务器或环境非常有用，因为恢复时可以确保所有的系统级设置也被复制到位。
-
1.1.5 copy命令导入导出
（1）创建text文件
#copy 命令用于表与文件（和标准输出，标准输入）之间的相互拷贝，把文本文件的内容导入到数据库，同时也可以把表的数据导出生成文本文件，实现数据库和文本文件之间的数据迁移。
#创建一个test_copy.txt文件，内容为：
1 a
2 b
3 c
[fbase@localhost ~]$ cd /home/fbase
[fbase@localhost ~]$ vi text_copy.txt
[fbase@localhost ~]$ cat text_copy.txt
1 a
2 b
3 c
# 使用tab键（制表符）
（2）创建数据库表
#创建test_copy表：
testdb=# create table test_copy(id int,name varchar(10));
#导入test_copy.txt文件到表test_copy中：
testdb=# \copy test_copy from /home/fbase/test_copy.txt;
#查看test_copy是否导入：
testdb=# select * from test_copy;
testdb=# select * from test_copy;
id | name
----+------
1 | a
2 | b
3 | c
(3 rows)
（3）导出
#导出示例：
#将test_copy导出到 /home/postgres路径下为test_copy1.txt文件：
testdb=# \copy test_copy to /home/fbase/test_copy1.txt;
（4）导入
#把文本数据导入到表中，以tab制表符隔离
testdb=# \copy test_copy from /home/fbase/test_copy.txt;
#以tab制表符隔离生成文本文件
testdb=# \copy test_copy to /home/fbase/test_copy1.txt;
#以csv格式（以逗号隔离）生成文本文件
testdb=# \copy test_copy to /home/fbase/test_copy1.txt.csv with csv;
#以csv格式文本文件导入表
testdb=# \copy test_copy from /home/fbase/test_copy1.txt.csv with csv;
1.2官方介绍
1.2.1 转储生成
此转储方法背后的想法是生成一个包含 SQL 命令的文件，当将该文件反馈给服务器时，它将以转储时的相同状态重新创建数据库。PostgreSQL 提供实用程序 pg_dump 以用于此目的。此命令的基本用法是
pg_dump dbname > dumpfile
pg_dump 将其结果写入标准输出。我们将在下面看到这如何有用。虽然上述命令创建一个文本文件，但 pg_dump 可以创建其他格式的文件，这些文件允许并行处理和更细粒度的对象恢复控制。
pg_dump 是一个常规的 PostgreSQL 客户端应用程序（尽管它是一个特别聪明的应用程序）。这意味着您可以从任何可以访问数据库的远程主机执行此备份过程。但请记住，pg_dump 不使用特殊权限进行操作。特别是，它必须具有对您要备份的所有表的读取访问权限，因此为了备份整个数据库，您几乎总是必须以数据库超级用户的身份运行它。（如果您没有足够的权限来备份整个数据库，您仍然可以使用诸如 -n *schema* 或 -t *table*）之类的选项来备份您有权访问的数据库部分。）
要指定 pg_dump 应联系哪个数据库服务器，请使用命令行选项 -h *host* 和 -p *port*。默认主机是本地主机或 PGHOST 环境变量指定的任何内容。类似地，默认端口由 PGPORT 环境变量指示，或者如果没有，则由编译时默认值指示。（方便的是，服务器通常具有相同的编译时默认值。）
与任何其他 PostgreSQL 客户端应用程序一样，pg_dump 默认情况下将使用与当前操作系统用户名相等的数据库用户名进行连接。要覆盖此设置，请指定 -U 选项或设置环境变量 PGUSER。请记住，pg_dump 连接受制于正常的客户端身份验证机制。
与后面描述的其他备份方法相比，pg_dump 的一个重要优势在于，pg_dump 的输出通常可以重新加载到较新版本的 PostgreSQL 中，而文件级备份和连续归档都极大地依赖于服务器版本。当将数据库传输到不同的机器架构（例如从 32 位服务器转到 64 位服务器）时，pg_dump 也是唯一可行的方法。
由 pg_dump 创建的转储在内部是一致的，这意味着，转储表示 pg_dump 开始运行时数据库的快照。pg_dump 在工作时不会阻止对数据库的其他操作。（例外情况是需要以独占锁操作的操作，例如大多数形式的 ALTER TABLE。）
1.2.2 还原转储
pg_dump 创建的文本文件旨在由 psql 程序读取。还原转储的一般命令格式为
psql dbname < dumpfile
其中 dumpfile 是 pg_dump 命令输出的文件。此命令不会创建数据库 dbname，因此您必须在执行 psql 之前从 template0 中创建它（例如，使用 createdb -T template0 *dbname*）。psql 支持类似于 pg_dump 的选项，用于指定要连接的数据库服务器和要使用的用户名。有关更多信息，请参阅 psql 参考页。使用 pg_restore 实用程序还原非文本文件转储。
在还原 SQL 转储之前，所有拥有对象或被授予转储数据库中对象权限的用户必须已经存在。如果他们不存在，则还原将无法使用原始所有权和/或权限重新创建对象。（有时这是您想要的，但通常不是。）
默认情况下，在遇到 SQL 错误后，psql 脚本将继续执行。您可能希望使用 ON_ERROR_STOP 变量运行 psql 以更改该行为，并在发生 SQL 错误时让 psql 以退出状态 3 退出
psql --set ON_ERROR_STOP=on dbname < dumpfile
无论哪种方式，您都只会拥有部分还原的数据库。或者，您可以指定将整个转储还原为单个事务，以便还原完全完成或完全回滚。可以通过将 -1 或 --single-transaction 命令行选项传递给 psql 来指定此模式。使用此模式时，请注意，即使是轻微的错误也可能回滚已经运行了数小时的还原。但是，这可能仍然优于在部分还原转储后手动清理复杂数据库。
由于 pg_dump 和 psql 具有写入或读取管道的能力，因此可以将数据库直接从一台服务器转储到另一台服务器，例如
pg_dump -h host1 dbname | psql -h host2 dbname
pg_dump生成的转储相对于template0。这意味着通过template1添加的任何语言、过程等也将由pg_dump转储。因此，在还原时，如果您使用的是自定义的template1，则必须从template0创建空数据库，如上例所示。还原备份后，最好对每个数据库运行
ANALYZE，以便查询优化器具有有用的统计信息。
1.2.3 使用pg_dumpall
pg_dump 每次仅转储一个数据库，并且不转储有关角色或表空间的信息（因为这些信息是针对整个集群的，而不是针对每个数据库的）。为了支持方便地转储数据库集群的全部内容，提供了 pg_dumpall 程序。 pg_dumpall 备份给定集群中的每个数据库，并且还保留集群范围的数据，例如角色和表空间定义。此命令的基本用法是
pg_dumpall > dumpfile
可以使用 psql 还原生成的转储
psql -f dumpfile postgres
（实际上，您可以指定任何现有的数据库名称作为起点，但如果您要加载到空集群中，则通常应使用 postgres。）还原 pg_dumpall 转储时，始终需要具有数据库超级用户访问权限，因为这是还原角色和表空间信息所必需的。如果您使用表空间，请确保转储中的表空间路径适用于新安装。
pg_dumpall 通过发出命令来重新创建角色、表空间和空数据库，然后为每个数据库调用 pg_dump 来工作。这意味着虽然每个数据库在内部是一致的，但不同数据库的快照不同步。
可以使用 pg_dumpall --globals-only 选项单独转储群集范围的数据。如果对各个数据库运行 pg_dump 命令，则需要此选项才能完全备份群集。
1.2.4 处理大型数据库
某些操作系统具有最大文件大小限制，在创建大型 pg_dump 输出文件时会导致问题。幸运的是，pg_dump 可以写入标准输出，因此您可以使用标准 Unix 工具来解决此潜在问题。有几种可能的方法
使用压缩转储。 您可以使用您最喜欢的压缩程序，例如 gzip
pg_dump dbname | gzip > filename.gz
使用重新加载
gunzip -c filename.gz | psql dbname
或
cat filename.gz | gunzip | psql dbname
使用 split。 split 命令允许您将输出拆分为较小的文件，这些文件的大小可以被底层文件系统接受。例如，要制作 2 GB 大小的块
pg_dump dbname | split -b 2G - filename
使用重新加载
cat filename* | psql dbname
如果使用 GNU split，则可以同时使用它和 gzip
pg_dump dbname | split -b 2G --filter='gzip > $FILE.gz'
可以使用 zcat 恢复它。
使用 pg_dump 的自定义转储格式。 如果 PostgreSQL 是在安装了 zlib 压缩库的系统上构建的，则自定义转储格式将在将数据写入输出文件时对其进行压缩。这将生成类似于使用 gzip 的转储文件大小，但它具有可以有选择地恢复表的附加优势。以下命令使用自定义转储格式转储数据库
pg_dump -Fc dbname > filename
自定义格式转储不是 psql 的脚本，而必须使用 pg_restore 恢复，例如
pg_restore -d dbname filename
有关详细信息，请参阅 pg_dump 和 pg_restore 参考页。
对于非常大的数据库，您可能需要将 split 与其他两种方法之一结合使用。
使用 pg_dump 的并行转储功能。 要加快大型数据库的转储速度，可以使用 pg_dump 的并行模式。这将同时转储多个表。您可以使用 -j 参数控制并行度。仅支持“目录”存档格式的并行转储。
pg_dump -j num -F d -f out.dir dbname
您可以使用 pg_restore -j 并行恢复转储。这适用于“自定义”或“目录”存档模式的任何存档，无论是否使用 pg_dump -j 创建。
1.2.5 pg_dump
概要
pg_dump [connection-option...] [option...] [dbname]
说明
pg_dump 是一个用于备份 PostgreSQL 数据库的实用程序。即使数据库正在同时使用，它也能进行一致的备份。 pg_dump 不会阻止其他用户访问数据库（读者或写入者）。
pg_dump 仅转储单个数据库。要备份整个集群，或备份集群中所有数据库共有的全局对象（如角色和表空间），请使用 pg_dumpall。
转储可以输出为脚本或存档文件格式。脚本转储是纯文本文件，其中包含重建数据库到保存时状态所需的 SQL 命令。要从这样的脚本中恢复，请将其提供给 psql。脚本文件可用于在其他机器和其他架构上重建数据库；经过一些修改，甚至可以在其他 SQL 数据库产品上重建数据库。
替代的存档文件格式必须与 pg_restore 一起使用才能重建数据库。它们允许 pg_restore 选择性地还原内容，甚至在还原之前重新排序项目。存档文件格式设计为跨架构可移植。
当与其中一种存档文件格式一起使用并与 pg_restore 结合使用时，pg_dump 提供了一种灵活的存档和传输机制。 pg_dump 可用于备份整个数据库，然后 pg_restore 可用于检查存档和/或选择要还原的数据库部分。最灵活的输出文件格式是 “custom” 格式 (-Fc) 和 “directory” 格式 (-Fd)。它们允许选择和重新排序所有存档的项目，支持并行还原，并且默认情况下进行压缩。 “directory” 格式是唯一支持并行转储的格式。
在运行 pg_dump 时，应检查输出中是否存在任何警告（打印在标准错误中），尤其要考虑以下列出的限制。
诊断
pg_dump 在内部执行 SELECT 语句。如果您在运行 pg_dump 时遇到问题，请确保您能够使用（例如，psql）从数据库中选择信息。此外，libpq 前端库使用的任何默认连接设置和环境变量都将适用。
pg_dump 的数据库活动通常由累积统计系统收集。如果这不可取，您可以通过 PGOPTIONS 或 ALTER USER 命令将参数 track_counts 设置为 false。
备注
如果您的数据库集群对 template1 数据库有任何本地添加，请务必小心地将 pg_dump 的输出还原到一个真正空数据库中；否则，您可能会由于添加的对象重复定义而导致错误。要创建一个不含任何本地添加的空数据库，请从 template0 复制，而不是 template1，例如
CREATE DATABASE foo WITH TEMPLATE template0;
当选择仅数据转储并且使用选项 --disable-triggers 时，pg_dump 会发出命令在插入数据之前禁用用户表上的触发器，然后在插入数据之后发出重新启用它们的命令。如果还原在中间停止，系统目录可能会处于错误状态。
由 pg_dump 生成的转储文件不包含优化器用于制定查询计划决策的统计信息。因此，明智的做法是在从转储文件还原后运行 ANALYZE 以确保最佳性能；有关更多信息，请参见 第 25.1.3 节 和 第 25.1.6 节。
由于 pg_dump 用于将数据传输到较新版本的 PostgreSQL，因此可以预期 pg_dump 的输出加载到比 pg_dump 版本更新的 PostgreSQL 服务器版本中。 pg_dump 还可以从比其自身版本更旧的 PostgreSQL 服务器转储数据。（目前，支持回溯到版本 9.2 的服务器。）但是，pg_dump 无法从比其自身主要版本更新的 PostgreSQL 服务器转储数据；它甚至会拒绝尝试，而不是冒着创建无效转储的风险。此外，不能保证 pg_dump 的输出可以加载到旧主要版本的服务器中——即使转储是从该版本的服务器中获取的。将转储文件加载到旧服务器中可能需要手动编辑转储文件以删除旧服务器无法理解的语法。在跨版本的情况下，建议使用 --quote-all-identifiers 选项，因为它可以防止因不同 PostgreSQL 版本中保留字列表不同而产生的问题。
在转储逻辑复制订阅时，pg_dump 将生成使用 connect = false 选项的 CREATE SUBSCRIPTION 命令，以便还原订阅不会为创建复制槽或初始表副本而进行远程连接。这样，可以在不需要对远程服务器进行网络访问的情况下还原转储。然后由用户以合适的方式重新激活订阅。如果涉及的主机已更改，则可能必须更改连接信息。在启动新的完整表副本之前，截断目标表也可能是合适的。如果用户打算在刷新期间复制初始数据，则必须使用 two_phase = false 创建槽。在初始同步之后，如果订阅最初是使用 two_phase = true 选项创建的，则订阅者将自动启用 two_phase 选项。
示例
将名为 mydb 的数据库转储到 SQL 脚本文件
$ pg_dump mydb > db.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
转储名称以 east 或 west 开头且以 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
1.2.6 pg_dunpall
pg_dumpall — 将 PostgreSQL 数据库集群提取到脚本文件中
概要
pg_dumpall [connection-option...] [option...]
说明
pg_dumpall 是一个实用程序，用于将 (“转储”) 所有 PostgreSQL 数据库的一个集群写入一个脚本文件。该脚本文件包含 SQL 命令，这些命令可以用作 psql 的输入，以恢复数据库。它通过为集群中的每个数据库调用 pg_dump 来执行此操作。 pg_dumpall 还会转储所有数据库共有的全局对象，即数据库角色、表空间以及配置参数的特权授予。（pg_dump 不会保存这些对象。）
由于 pg_dumpall 从所有数据库读取表，因此您很可能必须以数据库超级用户的身份连接才能生成完整的转储。此外，您需要超级用户权限才能执行已保存的脚本，以便被允许添加角色和创建数据库。
SQL 脚本将被写入标准输出。使用 -f/--file 选项或 shell 运算符将其重定向到一个文件中。
pg_dumpall 需要连接到 PostgreSQL 服务器多次（每个数据库一次）。如果您使用密码身份验证，它每次都会要求输入密码。在这种情况下，最好有一个 ~/.pgpass 文件。有关更多信息，请参阅 第 34.16 节。
注释
由于 pg_dumpall 在内部调用 pg_dump，因此一些诊断消息将引用 pg_dump。
即使您打算将转储脚本还原到新集群中，--clean 选项也可能很有用。使用 --clean 授权脚本删除并重新创建内置 postgres 和 template1 数据库，确保这些数据库保留与源集群中相同的属性（例如，语言环境和编码）。如果没有此选项，这些数据库将保留其现有的数据库级属性以及任何预先存在的的内容。
还原后，最好对每个数据库运行 ANALYZE，以便优化器拥有有用的统计信息。您还可以运行 vacuumdb -a -z 来分析所有数据库。
转储脚本不应被期望完全无错误地运行。特别是，由于脚本将为源集群中存在的每个角色发出 CREATE ROLE，因此，除非目标集群使用不同的引导超级用户名称进行初始化，否则肯定会出现引导超级用户的 “角色已存在” 错误。此错误无害，应予以忽略。使用 --clean 选项可能会产生有关不存在对象的附加无害错误消息，但可以通过添加 --if-exists 来最小化这些错误消息。
pg_dumpall 要求在还原之前存在所有必需的表空间目录；否则，将为非默认位置的数据库创建失败。
示例
转储所有数据库
$ pg_dumpall > db.out
要从此文件还原数据库，可以使用
$ psql -f db.out postgres
在此连接到哪个数据库并不重要，因为 pg_dumpall 创建的脚本文件将包含创建和连接到已保存数据库的适当命令。例外情况是，如果您指定了 --clean，则必须最初连接到 postgres 数据库；脚本将尝试立即删除其他数据库，并且对于您连接到的数据库，此操作会失败。
1.2.7 pg_restore
pg_restore — 从 pg_dump 创建的归档文件中还原 PostgreSQL 数据库
语法：
pg_restore [connection-option...] [option...] [filename]
描述：
pg_restore 是一个用于从 pg_dump 以非纯文本格式创建的存档中还原 PostgreSQL 数据库的实用程序。它将发出必要的命令，以将数据库重建为保存时所处状态。存档文件还允许 pg_restore 选择性地还原内容，甚至在还原之前重新排序项目。存档文件被设计为跨体系结构可移植。
pg_restore 可以以两种模式运行。如果指定了数据库名称，pg_restore 将连接到该数据库并将存档内容直接还原到数据库中。否则，将创建一个包含重建数据库所需的 SQL 命令的脚本，并将其写入文件或标准输出。此脚本输出等效于 pg_dump 的纯文本输出格式。因此，控制输出的一些选项类似于 pg_dump 选项。
显然，pg_restore 无法还原存档文件中不存在的信息。例如，如果使用 “以 INSERT 命令转储数据” 选项创建存档，pg_restore 将无法使用 COPY 语句加载数据。
注意
如果您的安装对 template1 数据库有任何本地添加，请务必将 pg_restore 的输出加载到一个真正空数据库中；否则，您可能会因添加的对象重复定义而导致错误。要创建一个没有任何本地添加的空数据库，请从 template0 复制，而不是从 template1 复制，例如
CREATE DATABASE foo WITH TEMPLATE template0;
pg_restore 的限制详述如下。
- 在将数据恢复到预先存在的表且使用了
--disable-triggers选项时，pg_restore会发出命令在插入数据之前禁用用户表上的触发器，然后发出命令在插入数据之后重新启用它们。如果恢复在中途停止，系统目录可能会处于错误状态。 pg_restore无法有选择地恢复大型对象；例如，仅恢复特定表的大型对象。如果存档包含大型对象，则所有大型对象都将被恢复，或者如果它们通过-L、-t或其他选项被排除，则没有大型对象将被恢复。
另请参阅 pg_dump 文档，了解 pg_dump 的限制详情。
还原后，建议对每个还原的表运行 ANALYZE，以便优化器拥有有用的统计信息。
示例
假设我们已将名为 mydb 的数据库转储到自定义格式的转储文件中
$ pg_dump -Fc mydb > db.dump
若要删除数据库并从转储中重新创建它
$ dropdb mydb
$ pg_restore -C -d postgres db.dump
在 -d 开关中命名的数据库可以是群集中存在的任何数据库；pg_restore 仅使用它为 mydb 发出 CREATE DATABASE 命令。使用 -C，数据始终还原到转储文件中出现的数据库名称中。
若要将转储还原到名为 newdb 的新数据库中
$ createdb -T template0 newdb
$ pg_restore -d newdb db.dump
请注意，我们不使用 -C，而是直接连接到要还原到的数据库。另请注意，我们从 template0（而不是 template1）克隆新数据库，以确保它最初为空。
若要重新排序数据库项目，首先需要转储存档的目录
$ pg_restore -l db.dump > db.list
清单文件包含一个标题和每项的一行，例如
;
; Archive created at Mon Sep 14 13:55:39 2009
; dbname: DBDEMOS
; TOC Entries: 81
; Compression: 9
; Dump Version: 1.10-0
; Format: CUSTOM
; Integer: 4 bytes
; Offset: 8 bytes
; Dumped from database version: 8.3.5
; Dumped by pg_dump version: 8.3.8
;
;
; Selected TOC Entries:
;
3; 2615 2200 SCHEMA - public pasha
1861; 0 0 COMMENT - SCHEMA public pasha
1862; 0 0 ACL - public pasha
317; 1247 17715 TYPE public composite pasha
319; 1247 25899 DOMAIN public domain0 pasha
分号开头为注释，行首的数字引用分配给每个项目的内部存档 ID。
文件中的行可以注释掉、删除和重新排序。例如
10; 145433 TABLE map_resolutions postgres
;2; 145344 TABLE species postgres
;4; 145359 TABLE nt_header postgres
6; 145402 TABLE species_records postgres
;8; 145416 TABLE ss_old postgres
可以用作 pg_restore 的输入，并且只会按该顺序还原项目 10 和 6
$ pg_restore -L db.list db.dump