PostgreSQL16-数据备份(SQL转储)与数据恢复

16.数据备份(SQL转储)与数据恢复

SQL转储就是将数据对象通过工具输出到由SQL语句组成的文件中

pg_dump可以单独备份表、schema或者database；pg_dumpall仅支持导出全库数据。
pg_dump可以将数据备份为SQL文本格式，也支持备份为自定义的压缩格式或TAR格式。压缩格式和TAR格式的备份文件可以实现并行恢复
pg_dumpall仅可以将当前PG服务实例中所有database的数据导出为SQL文本,不支持其他格式导出，也可以同时导出表空间和角色的全局对象

PostgreSQL支持以下两种数据恢复方法：
1、使用psql恢复pg_dump或pg_dumpall工具生成的SQL文本格式的数据备份。
2、使用pg_restore工具来恢复由pg_dump工具生成的自定义压缩格式、TAR包格式或者目录格式备份。

数据备份

备份
pg_dump -U user_name db_name -p 5432 -C -f /tmp/114_auto.sql  指定库名，-C有建库命令，不写没有命令
pg_dumpall -U user_name  -p 5432 -f /tmp/114_auto_all.sql  不能指定库名，没有-C选项

自定义备份格式
pg_dump -Fc -h 192.168.8.19 -U 用户名 库名 > /路径/文件名.dump

-F 自定义输出格式，后面可以跟 p c d t 来指定格式
p  输出一个纯文本SQL脚本文件(默认值)
c  输出适合于pg_restore输入的自定义格式归档文件
d 输出适合于pg_restore输入的目录格式归档文件
t  输出适合输入到pg_restore的tar格式存档

pg_dump -t 表名 库名 -f  /路径/文件名.sql        备份单张表
pg_dump -t '模式名.*' 库名 -f  /路径/文件名.sql      备份某个模式下的所有表
pg_dump -t '模式名.a*' -T 模式名.被排除的表名 库名 -f /路径/文件名.sql       备份某个模式以a开头的表，并排除一张表

数据恢复

psql -U postgres < /tmp/220_all.sql   使用pg_dumpall创建的备份，恢复时不用指定库名，也不用建库
psql -U postgres < /tmp/220_auto.sql  220_auto.sql在备份时指定了-C

恢复(先建库)
psql -U postgres -d auto_114 -p 5432 --set ON_ERROR_STOP=ON -f /tmp/114_auto_all.sql
--set ON_ERROR_STOP=ON 遇到错误停止继续执行
--single-transaction   有错误就回滚

以postgres的身份，恢复数据库
pg_restore -h 192.168.9.20 -U postgres -C -d postgres -j 2  /pgbackup/mydb_back2019.dump

-j    --jobs=    执行恢复操作的进程数
-C   --create   恢复的库和SQL文件中的同名，则可省略省略建库步骤

注：如果指定了--create选项，那么恢复出来的数据库名就会默认采用备份时的数据库名，不允许改名。如果还同时指定了--dbname选项，那么此时连接的数据库名一定不能是待恢复的数据库名，因为要恢复数据库之前必然要建数据库，而要建数据库之前必然要先连到某个已存在的数据库，--dbname选项指定的就是建立被恢复的数据库之前先连到哪个数据库，所以必然不能与待恢复的数据库同名，我们一般指定先连到postgres数据库。

---

使用pg_restore进行恢复

2.可以使用pg_restore扫描备份文件来生成一张备份内容列表，通过该列表可以确认备份包含了哪些内容。还可以通过编辑该内容列表来控制恢复哪些内容。

3.pg_dump支持选择性地仅备份部分对象以节省备份时间，类似的，pg_restore也支持选择性的仅恢复部分对象，不管备份文件本身是全库备份还是部分对象的备份都没有问题。

4.pg_restore的大部分功能是向后兼容的，即支持将老版本PostgreSQL生产的备份数据恢复到新版本的PostgreSQL中。

pg_dump

用法:

pg_dump [选项]... [数据库名字]

一般选项:

-f, --file=FILENAME          输出文件或目录名

-F, --format=c|d|t|p         输出文件格式 (定制, 目录, tar)

明文 (默认值))

-j, --jobs=NUM               执行多个并行任务进行备份转储工作

-v, --verbose                详细模式

-V, --version                输出版本信息，然后退出

-Z, --compress=0-9           被压缩格式的压缩级别

--lock-wait-timeout=TIMEOUT  在等待表锁超时后操作失败

--no-sync                    do not wait for changes to be written safely to disk

-?, --help                   显示此帮助, 然后退出

控制输出内容选项:

-a, --data-only              只转储数据,不包括模式

-b, --blobs                  在转储中包括大对象

-B, --no-blobs               exclude large objects in dump

-c, --clean                  在重新创建之前，先清除（删除）数据库对象

-C, --create                 在转储中包括命令,以便创建数据库

-E, --encoding=ENCODING      转储以ENCODING形式编码的数据

-n, --schema=SCHEMA          只转储指定名称的模式

-N, --exclude-schema=SCHEMA  不转储已命名的模式

-o, --oids                   在转储中包括 OID

-O, --no-owner               在明文格式中, 忽略恢复对象所属者

-s, --schema-only            只转储模式, 不包括数据

-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            do not dump publications

--no-security-labels         不转储安全标签的分配

--no-subscriptions           do not dump subscriptions

--no-synchronized-snapshots  在并行工作集中不使用同步快照

--no-tablespaces             不转储表空间分配信息

--no-unlogged-table-data     不转储没有日志的表数据

--quote-all-identifiers      所有标识符加引号，即使不是关键字

--section=SECTION            备份命名的节 (数据前, 数据, 及 数据后)

--serializable-deferrable   等到备份可以无异常运行

--snapshot=SNAPSHOT          为转储使用给定的快照

--strict-names               要求每个表和/或schema包括模式以匹配至少一个实体

--use-set-session-authorization  使用 SESSION AUTHORIZATION 命令代替ALTER OWNER 命令来设置所有权

联接选项:

-d, --dbname=DBNAME       对数据库 DBNAME备份

-h, --host=主机名        数据库服务器的主机名或套接字目录

-p, --port=端口号        数据库服务器的端口号

-U, --username=名字      以指定的数据库用户联接

-w, --no-password        永远不提示输入口令

-W, --password           强制口令提示 (自动)

--role=ROLENAME          在转储前运行SET ROLE

如果没有提供数据库名字, 那么使用 PGDATABASE 环境变量

的数值.