进阶数据库系列（二十）：PostgreSQL 数据库备份与恢复

点击下方名片，设为星标！

回复“1024”获取2TB学习资源！

前面介绍了 PostgreSQL 事务与并发控制、主从复制、高可用方案、基于 Patroni 高可用架构部署及故障切换、 基于 repmgr 高可用架构实践、基于 pgpool 实现读写分离实践等相关的知识点，今天我将详细的为大家介绍 PostgreSQL 数据库备份与恢复相关知识，希望大家能够从中收获多多！如有帮助，请点在看、转发支持一波！！！

今天将就比较常用的postgresql数据库备份做一个比较详尽的总结。冷备冷恢复(第一种物理备份方式)

此种方式是直接备份数据库物理文件，在pg数据库里，这通常指的是PGDATA变量定义的文件夹，例如：

我的这个示例数据库是安装在了/usr/local/pgsql目录下，那么，备份如下，文件备份到了/media目录下了：

但需要注意的是，此种方式由于比较直接，不管是否数据库有无IO情况，因此，备份的时候需要先停止数据库，恢复的时候要删除原数据库文件，重新覆盖回去后，才可以在启动数据库，如果在数据库启动的时候备份，那么，可能会造成数据备份不全，也可以理解为冷备方式。物理备份的恢复

那么，此种方式比较霸道，也可以说是野蛮，因此，优点是简单，直接，高效，缺点是需要停机维护，不能实时备份，算是冷备范畴。另一个缺点是，一般生产环境的数据库由于经年累月的使用，数据库一般会是非常大的，几百G 上T也是经常的事情，而cp命令的效率是和文件大小是有关联的，受限于此，这个时候的备份和恢复是比较困难的。更多关于 PostgreSQL 系列的学习文章，请参阅：PostgreSQL 数据库，本系列持续更新中。热备冷恢复(第二种物理备份方式)pg_basebackup 基本原理和步骤

pg_basebackup 也算是物理方式，是可以使用流协议。并且是热备范畴，备份的时候不需要停数据库，但，恢复的时候需要停数据库（多说一句，某些项目数据库是24小时不能停的，如果停了，可能接受不了，因此，备份前最好搞清楚自己是否能够接受冷备）。

备份：使用pg_basebackup命令来进行备份，这个命令可以将postgresql的数据文件备份为两个压缩文件：base.tar和 pg_wal.tar。本别是数据文件和归档文件，恢复的时候，需要设置按照归档文件来恢复。那么，此种方式的备份可以备份自定义表空间。

恢复：需要先把备份的压缩文件替换当前的数据文件，然后修改postgresql.conf，因为这个配置文件在data文件夹中，所以只能是在把base.tar解压到数据库当前数据位置，也就是我们默认初始化指定的数据保存位置data文件夹中，才能修改配置，在配置好归档设置以后，可以启动pgsql服务，进行启动恢复。

在恢复过程中，会拷贝归档文件，进行数据恢复。

恢复成功，也就是数据库服务启动成功。这个时候我们访问数据库，它是作为归档状态存在的，所以只能读，不能写操作。

为了恢复数据库写操作，我们需要在命令行下执行切换数据库状态的指令。切换成功之后，才可以进行读写操作。实操示例

环境介绍：数据库主节点，IP：192.168.123.60，数据库端口：5432，数据库安装路径为/usr/local/pgsql ，管理用户为pg1。

创建一个自定义表空间，创建表空间存放路径。

登陆命令行，创建表空间

此时的$PGDATA目录下的pg_tblspc目录下有一个软链接：

创建一个新库和新表在此表空间内：

插入实验数据

查看是否正确插入数据:

开始备份

建立备份文件存放路径

可以看到有两个tablespace被备份了，-z -Z 5是压缩等级，范围是0-9， -Ft是tar包格式备份。

查看备份的文件：

备份完成后，删除test数据库，看看一会能否恢复。更多关于 PostgreSQL 系列的学习文章，请参阅：PostgreSQL 数据库，本系列持续更新中。恢复

停止数据库：

删除原库的数据文件：

解压备份文件到对应路径：要注意是解压到哪里

创建wal文件存放路径：赋予数据库管理用户权限，最后一个命令不能忘

修改postgresql.conf文件：任选一种恢复方式立刻恢复

可以按时间线恢复到最新

按时间点恢复

如果不想进入备份模式，直接数据库启动就可以用，那么就使用promote。

启动数据库

查看数据库状态：此时的数据库是备份状态，因此即可。

或者是进入命令行，执行以下函数即可：

最终确认是否恢复正常：

OK，利用 pg_basebackup 命令归档备份完全成功了。更多关于 PostgreSQL 系列的学习文章，请参阅：PostgreSQL 数据库，本系列持续更新中。热备热恢复

首选当然是pg_dump啦，这个备份工具是和pg_restore配套的，也可以看成是一个组合。

该备份工具的特点是稳定，高效，冷热备份恢复都可以，可以选择数据库部分表备份，只备份表结构，因此，该工具的使用比较复杂，这点是相对物理备份来说的。

怎么说呢，物理备份有一种暴力的美学感觉，简单的方法有时候更为高效。逻辑备份比较枯燥，复杂。pg_dump常用参数

pg_dump 热备单表和该表的数据热恢复

备份前数据库test的表内容：

可以看到有三个表，分别是emp，bonus，salgrade，库名是test。

现在计划备份emp表，该表备份到pg1用户的家目录下（如果是其它目录，可能会没有权限），使用工具为pg_dump。备份命令

如果有迁移到其它数据库，比如oracle的计划，那么，最好还是添加参数--inserts，上面的命令修改为如下：

如果希望恢复的时候不需要切换数据库，那么，应该使用参数大C，命令如下：

生成的备份文件内容如下

pg_dump的恢复命令

进入pg命令行直接执行备份的SQL文件即可（执行SQL语句前需要切换数据库到test）：

重进一次pg 命令行，执行查询恢复即可，可查到数据表明恢复成功：

以上备份和恢复都是使用的纯文本模式（也有人称之为转储），因此，在应对存有大量数据的数据库备份时，很可能会力不从心，可能需要归档备份模式，压缩备份模式。更多关于 PostgreSQL 系列的学习文章，请参阅：PostgreSQL 数据库，本系列持续更新中。归档文件分类

归档格式的备份文件又分为两种，最灵活的输出文件格式是“custom”自定义格式（使用命令项参数“-Fc”来指定），它允许对归档元素进行选取和重新排列，并且默认是压缩的；另一种是tar格式（使用命令项参数“-Ft”来指定），这种格式的文件不是压缩的，并且加载时不能重新排序，但是它也很灵活，可以用标准UNIX下的tar工具进行处理。custom自定义格式比较常用。

不带-F参数的时候，默认是纯文本模式（纯文本模式备份的文件可以使用记事本打开，里面都是SQL语句）

归档格式的备份文件必须与pg_restore一起使用来重建数据库，这种格式允许pg_restore选择恢复哪些数据，甚至可以在恢复之前对需要恢复的条目重新排序。

pg_dump可以将整个数据库备份到一个归档格式的备份文件中，而pg_restore则可以从这个归档格式的备份文件中选择性地恢复部分表或数据库对象，而不必恢复所有的数据。

归档模式的时候，必须使用pg_restore工具来进行恢复

以下仍然是单表的备份和恢复，备份文件名做了一个时间格式化，恢复的时候是使用pg_restore命令，使用psql将会报错。

pg_dump 热备单库热恢复（热恢复指的是不需要停止数据库服务）

备份的文件内容

可以看到有建库的SQL语句，因为使用了-C参数，一般情况下是不使用--insecres参数，如果数据库比较大的话，会非常的费时间，同样的，恢复的时候也非常费时间。

使用命令行恢复

自定义归档模式备份单库

利用归档文件恢复

需要提前建立test这个数据库

这里说的备份文件格式是由pg_dump -Fc参数生成的文件，此类文件是二进制格式的，不可当做文本文件查看，恢复的时候必须使用pg_restore命令，例如上面的test-database.dump文件就是此类文件。查看该文件的具体属性可以看到如下

此文件是可以利用pg_restore命令查看的，参数为-l ：

OK，将查看到的内容重定向到一个文件，那么，此重定向文件就是TOC文件了：

当然了，还可以这样生成TOC文件，和上面的命令是等价的（TOC.txt和TOC-1.txt两个文件是一模一样的）：

这个时候的TOC文件是可编辑的文本文件，不需要执行的部分删除或者;;注释掉就可以了。使用TOC文件：

当然，不使用TOC文件也是可以的，这也就是意味着全部还原，去掉-L参数即可：

带schema的备份：

带schema的恢复（-n后面接的是schema的名称，此schema需要提前创建）：

更多关于 PostgreSQL 系列的学习文章，请参阅：PostgreSQL 数据库，本系列持续更新中。

此工具是全库备份，但，一般是不使用这个的，因为，数据库有可能会很大，进而备份的时候出现问题，此工具可以备份用户信息，例如下面这个命令（全局对象里包括用户，因此，如果是仅备份用户信息，也可以使用参数r即可）：

OK，删除zsk这个用户，看看能否通过命令行恢复：

非常的方便，简单就可以把数据库内的所有用户信息备份了。

建议每天对角色和表空间定义等全局对象进行备份，但不建议每天使用pg_dumpall来备份全库数据，因为pg_dumpall仅支持导出为SQL文本格式，而使用这种庞大的SQL文本备份来进行全库级别的数据库恢复时极其耗时的，所以一般只建议使用pg_dumpall来备份全局对象而非全库数据。

Barman (Backup and Recovery Manager-备份恢复管理器) 。

是一个用Python语言实现的PostgreSQL灾难恢复管理工具，它由第二象限公司（2ndQuadrant）开源并维护。它允许我们在关键业务环境中执行远程备份，为数据库管理员在恢复阶段提供有效的数据保证。Barman最优秀的功能包括备份元数据、增量备份、保留策略、远程回复、WAL文件归档压缩和备份。

barman 建议以一个 dedicated server 存在。尽量不要部署到 postgresql server 上，但不是不能部署到 postgresql server 主机上。barman优点可以与postgresql的master和slaver分离部署，但是barman服务器需要安装postgresql的一些工具，如pg_basebackup、pg_receivewal通过流复制可以同步拉取wal，如果postgresql当前的wal还没close，barman会产生一个.partial文件实时同步，若在恢复时使用该文件需要将扩展后缀去掉barman可以一台服务器管理多个postgresql server，提供本地及远程恢复（远程恢复需要配置ssh免密）提供备份相关文件的检测及详细查询barman特点零数据丢失备份。保证用户在只有一台备份服务器的情况下达到零数据丢失。与备份服务器合作。允许备份服务器在与主服务器的流式复制不可用时，从barman获取wal文件。可靠的监控集成。用于监控和创建有关Barman配置状态的报告。可并行的备份和恢复。以更快的速度执行拷贝，加快了整个备份和恢复过程。备份目录。允许用户在同一界面中列出、保留、删除、存档和恢复多个完整备份。支持多个服务器。允许您从同一位置管理多个服务器的备份和恢复。存储备份数据。存储来自多个PostgreSQL实例的备份数据，支持不同的版本。完全热备份。从其他服务器执行PostgreSQL服务器的完全热备份，专门为备份和灾难恢复而设计。增量备份和恢复。针对新的和修改过的数据生成不同的备份，从而加快处理速度。保留策略。配置自定义备份保留策略以满足组织的需要。远程恢复。执行PostgreSQL服务器备份的远程恢复。Wal文件压缩。自定义归档和压缩WAL文件和备份。备份信息。生成所有备份的常规和磁盘使用信息。地理冗余。给DBA和系统管理员为PostgreSQL数据库的灾难恢复解决方案增加另一层保障。barman安装192.168.2.2 pg （postgresql server服务器）192.168.2.3 barman （barman备份服务器）postgresql版本11.2barman版本2.9.1yum安装postgresql

修改postgresql.conf配置

修改pg_hba.conf配置

创建pg_archive，并设置用户组。

启动postgresql

创建barman和barman_streaming用户

yum安装barman

由于barman是python写的，且有些功能需要使用postgresql的工具，安装时会有些依赖

修改barman的公共配置

修改barman对postgresql server的私有配置

将192.168.2.2 pg添加到/etc/hosts中barman使用

创建复制槽

barman check

其中minimum redundancy requirements: FAILED可忽略，首次配置后还没有全备，执行全备会变为OK。

首次配置完会产生minimum redundancy requirements: FAILED、receive-wal running:FAILED，执行barman cron即可变为OK，barman会执行如下过程。

所以barman的全备是利用pg_basebackup实现，增量是利用pg_receivewal实现。barman优点

1.可以与postgresql的master和slaver分离部署，但是barman服务器需要安装postgresql的一些工具，如、

2.通过流复制可以同步拉取wal，如果postgresql当前的wal还没close，barman会产生一个.partial文件实时同步，若在恢复时使用该文件需要将扩展后缀去掉

3.barman可以一台服务器管理多个postgresql server，提供本地及远程恢复（远程恢复需要配置ssh免密）

4.提供备份相关文件的检测及详细查询barman部分功能查看当前复制状态

查看备份列表

查看postgresql server

远程执行switch-wal

检查backupid

查看文件

–target参数包括：

备份恢复

还提供：、、、查看备份详情

更多关于 PostgreSQL 系列的学习文章，请参阅：PostgreSQL 数据库，本系列持续更新中。参考文章：https://blog.csdn.net/alwaysbefine/article/details/129422083 https://blog.csdn.net/weixin_39992480/article/details/100022373 https://zskjohn.blog.csdn.net/article/details/129648750读者专属技术群

构建高质量的技术交流社群，欢迎从事后端开发、运维技术进群（备注岗位，已在技术交流群的请勿重复添加）。主要以技术交流、内推、行业探讨为主，请文明发言。广告人士勿入，切勿轻信私聊，防止被骗。

扫码加我好友，拉你进群

推荐阅读 点击标题可跳转

突然，很多人被裁！知名公司遭曝光。。。

不雅信息发到工作群！回应：病毒入侵，已报警！

阿里再次大改革，江湖再无 P8 了。。。

这款轻量级文件传输工具真心强大！支持网页版

良心推荐！这 5 款免费开源软件一年为你节省上万元

最受欢迎Web服务器！来看看它比Nginx牛逼在哪？

面试官：如何快速修改 Docker 镜像默认存储位置？

PS：因为公众号平台更改了推送规则，如果不想错过内容，记得读完点一下“在看”，加个“星标”，这样每次新文章推送才会第一时间出现在你的订阅列表里。点“在看”支持我们吧!

南京兆柏数据恢复中心