我的PG数据库备份优化之路

1 背景

今天和大家聊一聊一段经历，之前待在传统ERP行业，由于涉及到出海的业务， 且国外的开发者对PG比较倾向，所以选择PG作为后端的数据库，并且在云上部署了应用和本地的数据库。 虽然知道上云是大方向，但是由于上云时间上比较着急了些， 所以暂时将所有的服务都部署在了云上的虚拟机上，包括数据库本身，说白了就是利用了云的虚拟化功能。PG采用了自建的部署方式，没有利用云原生的数据库服务。

本身应用PG数据库的数据量不大， 但是突然有天晚上会收到告警，连夜起来查看。刚打开电脑，连上VPN，又收到短信告警恢复。未理之，呼呼睡去。

第二日，忘记查看此问题，结果夜里再次发生告警。

第三日白天，查看PG所在服务的监控， 告警是对接的zabbix，内容为PG所在服务器磁盘容量超过80%。最先想到的是扩容磁盘。

可是仔细查看后，发现磁盘大部分时间在40%左右，就只是在夜里飙升到80%，达到告警阈值产生告警。

夜里究竟生了什么？ 实在是百思不得其解。

于是查看定时任务， crontab，发现是定时任务在为PG数据库备份(pg_dump)的时候， 备份占用空间较大。才导致数据盘实际可用空间减半。

2 备份流程分析

当时使用的备份步骤如下：

1.  pg_dump 导出数据文件 到 data目录：/opt/data/pg_data/data。

2.  自己的备份框架backupclient 压缩打包 到 backup目录：/opt/data/pg_data/backup。

3.  备份框架将压缩包上传至backup server。

注： /opt/data/pg_data目录为数据盘挂载点。

虽然这里有压缩，但是实际上是先备份，在压缩，最后上传服务器。原数据库200G，备份文件200G，压缩后的文件50G。 实际500G的盘，数据库可用空间仅200G。超过200G就会磁盘空间不足告警。同时可能存在夜里备份期间由于空间满导致数据库不可用的情况。到这里就比较清晰了，实际上备份的200G空间完全可以省略，可以直接将源库中的数据一次性备份为压缩文件。我自己是SHELL控，所以直接想到忽略这个中间数据文件， 那就用SHELL管道的方式处理， pg_dump出来的内容不落盘， 直接输出到压缩包。说干就干，由于是线上系统，所以需要验证下影响。

3 管道压缩验证

验证目的：

1. cpu 是否有影响？

2. 内存 是否有影响？

3. 临时空间是否增加？

4. 时间耗时比较。

环境信息：

CPU: 4核：

processor       : 3

vendor_id       : GenuineIntel

cpu family      : 6

model           : 62

model name      : Intel(R) Xeon(R) CPU E5-2690 v2 @ 3.00GHz

IO: 400 IOPS 写

MYHOST:~ # dd if=/dev/zero of=/u04/test2 bs=4k count=262144  oflag=direct

262144+0 records in

262144+0 records out

1073741824 bytes (1.1 GB) copied, 737.527 s, 1.5 MB/s

Device:    rrqm/s    wrqm/s    r/s    w/s    rsec/s    wsec/s    avgrq-sz    avgqu-sz    await    svctm    %util

de          0.00      1.00     0.00  406.00   0.00    3408.00     8.39        1.01       2.49     2.44     99.20

MEM: 8GB

数据量：

MY_ERP=# select datname,pg_size_pretty(pg_database_size(oid)) from pg_database where datname not in ($$TEMPLATE0$$, $$TEMPLATE1$$) order by pg_database_size(oid) desc;

DATNAME|PG_SIZE_PRETTY

MY_ERP|14 GB

Shared_buffers: 3GB

3.1 正常备份时间分析

1. 备份

date;time pg_dump  -p 6432 MY_ERP -f /u02/pg/MY_ERP.sql

Tue Jul  3 14:41:41 CST 2018

pg_dump: total time: 369355  ms  ##耗时369秒。

real    6m9.426s

user    0m13.985s

sys     0m22.757s

第一次执行369s，又执行了一次，执行时间只有259秒，原因是缓存的影响，读IO持续了40秒。

第一次执行，主要是io，利用率100%，存在读和写。读IO一直持续了6分钟。

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await  svctm  %util

de              0.00     0.00    451.00   603.00 37440.00 52848.00  85.66   162.01   157.04  0.95   100.00

主要时间消耗为备份(Copy)，cpu消耗50%左右。备份时的主要瓶颈在IO。

PID    USER    PR    NI    VIRT    RES    SHR    S    %CPU    %MEM     TIME+    COMMAND

103743  pga    20    0     3209m   3.0g   3.0g   R     56     39.2    1:28.55   pg: PGA MY_ERP [local] COPY

备份完成之后的文件大小为12G：

/dev/de2       20G   13G  6.1G  68% /u02 -- 空间占用为数据库备份文件12G。

-rw-r--r--  1 pga dbgrp 12717552237 Jul  3 14:47 /u02/pg/MY_ERP.sql

2. 压缩

开始压缩：

MYHOST:/u02/pg # date;time tar zcvf  /u02/pg/MY_ERP.sql.tar.gz /u02/pg/MY_ERP

Mon Jul  9 14:38:39 CST 2018

/u02/pg/MY_ERP

real    6m5.763s

CPU情况：

PID    USER    PR    NI    VIRT    RES    SHR    S    %CPU     %MEM    TIME+    COMMAND

93452  pga     20    0     12624   648    456    R     80      0.0    3:31.83    gzip

3449   pga     20    0     13100   868    716    D     6       0.0    0:09.08    tar

IO情况：

Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util

de              0.00     13.00     613.00  7.00  52224.00  160.00  84.49     2.20     3.55   0.42   26.00

由此可知，压缩时的主要瓶颈在CPU，压缩后文件大小为2G， 压缩比约为16%。压缩比主要看数据的重复占比，现网大概20%左右。

-rw-r--r-- 1 pga dbgrp 1993688791 Jul  3 15:14 /u02/pg/MY_ERP.sql.tar.gz

总结一下正常备份时候的时间分布和空间占用情况：

3.2 管道方式备份时间分析

1. 备份和压缩单个数据库

date;time pg_dump -p 6432 MY_ERP | gzip > /u02/pg/MY_ERP.gz

Fri Jul  6 15:18:04 CST 2018

pg_dump: total time: 370992  ms  ##耗时370秒，和正常备份时候的时间一致。

real    6m12.337s

CPU情况：

PID      USER  PR    NI    VIRT      RES      SHR      S    %CPU    %MEM    TIME+    COMMAND

53507    pga   20    0     12624     648      456      R     93     0.0    3:09.08   gzip

53508    pga   20    0     3209m     3.0g     3.0g     S     32     39.1   1:20.21   pg: PGA MY_ERP [local] COPY

IO情况：

Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util

de              0.00     0.00     408.00  547.00 34816.00 47920.00  86.63   158.94    169.81 1.05   100.00

主要瓶颈在IO和CPU，此时CPU和IO都被充分利用。备份完成后包的大小，和正常备份再压缩后基本一致：

1993691949 Jul  6 15:24 MY_ERP.gz

2. 所有文件打包

由于单个数据库备份， 所以需要把备份（gz）再次打包。

time tar cvf MY_ERP3.tar.gz MY_ERP3.gz

MY_ERP3.gz

real    0m30.013s

如果3个打包：

-rw-r--r-- 1 pga dbgrp 1993691949 Jul  6 15:24 MY_ERP.gz

-rw-r--r-- 1 pga dbgrp 1993691949 Jul  6 15:31 MY_ERP2.gz

-rw-r--r-- 1 root     root  1993691949 Jul  6 15:51 MY_ERP3.gz

MYHOST:/u02/pg # time tar cvf MY_ERP.tar.gz temp/*

temp/MY_ERP.gz

temp/MY_ERP2.gz

temp/MY_ERP3.gz

real    1m46.427s

打包后大小基本不变：1993697280 Jul  9 15:33 MY_ERP3.tar.gz

打包的主要瓶颈还是在IO方面，不涉及到压缩算法，所以CPU占比不高。

PID   USER      PR   NI  VIRT   RES  SHR  S   %CPU  %MEM    TIME+    COMMAND

43007 root      20   0   13376  976  816  D   10    0.0     0:08.90  tar

Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s     wsec/s avgrq-sz avgqu-sz   await  svctm  %util

da              0.00     0.00       0.00    1.00   0.00       8.00    8.00     0.01     8.00   8.00   0.80

de              0.00     4.00      737.00 1493.00 62904.00 130600.00  86.77   167.80   76.84   0.49   110.00

总结一下管道方式备份时候的时间分布和空间占用情况：

3.3 正常恢复流程分析

1. 解压

MYHOST:/u02/pg # time tar xzvf MY_ERP.tar.gz

MY_ERP

real    2m51.733s

2. 恢复

通过psql直接恢复

time psql -p 6432 MY_ERP -f /u02/pg/temp/MY_ERP

total time: 1725587  ms

real    28m45.656s

资源利用情况：

Device:         rrqm/s   wrqm/s    r/s     w/s    rsec/s   wsec/s    avgrq-sz avgqu-sz   await    svctm  %util

de              0.00     0.00      144.00  981.00 12288.00 85648.00  87.05    143.28     124.18   0.89   100.00

PID   USER     PR   NI  VIRT  RES  SHR  S   %CPU %MEM    TIME+    COMMAND

35271 pga      20   0   3207m 1.5g 1.5g R   86   19.6    5:15.21  pg: PGA MY_ERP [local] COPY

76511 pga      20   0   3205m 1.4g 1.4g S   0    18.6    0:10.41  pg: checkpointer process

数据库日志显示：

10:19:33.271 CST]  pg 35271  LOG:  duration: 0.377 ms  statement: COPY AAA

10:29:19.152 CST]  pg 35271  LOG:  duration: 103858.397 ms  statement: COPY BBB

10:30:16.080 CST]  pg 35271  LOG:  duration: 0.276 ms  statement: COPY CCC

10:30:16.184 CST]  pg 35271  LOG:  duration: 69.733 ms  statement: ALTER TABLE ONLY AAA ADD CONSTRAINT AAA_PKEY PRIMARY KEY (ID);

10:42:27.009 CST]  pg 35271  LOG:  duration: 155173.931 ms  statement: CREATE INDEX BBB_UUID ON BBB USING BTREE (BBB_UUID); (postgres.c:5427)

10:48:11.067 CST]  pg 35271  LOG:  duration: 8.153 ms  statement: ALTER TABLE ONLY CCC ADD CONSTRAINT CCC_BBB_UUID_FKEY FOREIGN KEY (BBB_UUID)

导入数据时间为11分钟， 创建索引耗时18分钟。总结：

3.4 管道方式恢复流程分析

1. 解压：把管道备份的文件tar.gz先解压为gz文件。

MYHOST:/u02/pg/temp # time tar xvf MY_ERP3.tar.gz

MY_ERP3.gz

real    0m19.327

2. 恢复：通过psql管道方式直接恢复

time psql -p 6432 MY_ERP -f /u02/pg/temp/MY_ERP

total time: 1725587  ms

real    28m45.656s

主要瓶颈在IO：

Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util

de              0.00     0.00   24.00 2256.00  2048.00 197664.00    87.59   144.24   63.85   0.44 100.00

数据库日志显示：

11:03:42.184 CST]  pg 72035  LOG:  duration: 0.252 ms  statement: COPY AAA

11:11:21.680 CST]  pg 72035  LOG:  duration: 84496.344 ms  statement: COPY BBB

11:12:08.434 CST]  pg 72035  LOG:  duration: 0.160 ms  statement: COPY VOLUME_USAGE_CACHE

11:27:48.790 CST]  pg 72035  LOG:  duration: 7.949 ms  statement: ALTER TABLE ONLY CCC

导入数据时间为9分钟， 创建索引耗时16分钟。总结：

4 初步优化总结

数据库大小14G的场景下：

Pg_dump原始备份

5 后记：还能再优化吗？

前段时间， 看见一朋友发了条朋友圈，说将自建的PG迁移到了华为云。于是突然也想研究一下大厂的备份方式，看下会不会也出现我这个在实例备份中遇到的问题。于是买了一个华为云的PG实例测试了一下。选的4U8G，高IO的规格， 17G的数据量大概在3分钟左右备份完成。最后的备份数据文件大小为1GB左右。备份时间比以前快了5倍，备份文件比以前小了1倍。

恢复的时长，17G大概在0.5分钟左右。数据库全库恢复比我原来的逻辑方式快了30倍。看了下恢复的文件， 应该是基于物理备份的方式， 所以恢复时长较我原来的逻辑备份要短很多。

而我最关心的是磁盘空间使用率， 我在华为RDS的监控上看到， 磁盘可用空间大小没有变化。

我猜测华为云RDS后端是不是又外挂了一个专门的存储盘？疑问来了， 那是否还要单独收费？赶紧查了下文档说明，如下：

可以猜测，如果是200G的数据盘， 后台可能是挂了一个200G的备份盘（或者利用云原生的evs snapshot备份功能？），备份完毕之后，再将备份文件上传，在删除备份盘里面的临时备份文件。而且目前看来， 如果有这部分备份空间，费用也是华为云为客户买单的。

另外还发现了一个“惊喜”，华为云PG还可以做增量的恢复， 能够恢复到任意时间点。确实给我们省了不少心，哈哈。

我预计在这个夏天的某个晚上，我们将通过华为的DRS服务将我们自己的pg顺利迁移到华为云的pg上。

这样， 就再也不会在晚上收到数据盘空间不足的异常告警了。

数据库 数据库 华为云数据库 云备份

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。