PG误操作恢复工具之pg_recovery插件
Tags： pg_recoveryPostgreSQL插件误操作恢复闪回
👉 本文共约1329个字，系统预计阅读时间或需5分钟。
简介
pg_recovery 是一款 PostgreSQL 数据找回工具。可以恢复 COMMIT / DELETE / UPDATE / ROLLBACK / DROP COLUMN 操作后导致的数据变化，并以表的形式返回。安装方便，操作简单。仓库地址：
https://github.com/radondb/pg_recovery
安装
若报权限不足，例如“/bin/install: cannot create regular file ‘/usr/pgsql-14/lib/pg_recovery.so’: Permission denied”，则可以使用root用户编译：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | wget https://codeload.github.com/radondb/pg_recovery/zip/refs/heads/master -O /tmp/pg_recovery.zip unzip /tmp/pg_recovery.zip cd /tmp/pg_recovery-master -- 执行make和make install会生成后续的需要执行的命令 make PG_CONFIG=/pg13/pg13/bin/pg_config gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I. -I./ -I/pg13/pg13/include/postgresql/server -I/pg13/pg13/include/postgresql/internal -D_GNU_SOURCE -c -o pg_recovery.o pg_recovery.c gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -shared -o pg_recovery.so pg_recovery.o -L/pg13/pg13/lib -Wl,--as-needed -Wl,-rpath,'/pg13/pg13/lib',--enable-new-dtags make install PG_CONFIG=/pg13/pg13/bin/pg_config /usr/bin/mkdir -p '/pg13/pg13/lib/postgresql' /usr/bin/mkdir -p '/pg13/pg13/share/postgresql/extension' /usr/bin/mkdir -p '/pg13/pg13/share/postgresql/extension' /usr/bin/install -c -m 755 pg_recovery.so '/pg13/pg13/lib/postgresql/pg_recovery.so' /usr/bin/install -c -m 644 .//pg_recovery.control '/pg13/pg13/share/postgresql/extension/' /usr/bin/install -c -m 644 .//pg_recovery--1.0.sql '/pg13/pg13/share/postgresql/extension/' |
初始化插件成功，返回如下信息。
1 2 | $ create extension pg_recovery ; CREATE EXTENSION |
安装过程:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 | [pg13@lhrpgall lib]$ wget https://codeload.github.com/radondb/pg_recovery/zip/refs/heads/master -O /tmp/pg_recovery.zip --2022-01-03 11:33:41-- https://codeload.github.com/radondb/pg_recovery/zip/refs/heads/master Resolving codeload.github.com (codeload.github.com)... 20.205.243.165 Connecting to codeload.github.com (codeload.github.com)|20.205.243.165|:443... connected. HTTP request sent, awaiting response... 200 OK Length: unspecified [application/zip] Saving to: ‘/tmp/pg_recovery.zip’ [ <=> ] 13,023 --.-K/s in 0.002s 2022-01-03 11:33:42 (5.78 MB/s) - ‘/tmp/pg_recovery.zip’ saved [13023] [pg13@lhrpgall lib]$ cd /tmp/ [pg13@lhrpgall tmp]$ ll total 24 -rw-r--r-- 1 pg13 postgres 13023 Jan 3 11:33 pg_recovery.zip drwx------ 3 root root 4096 Jan 3 11:27 systemd-private-d7fc21f66f934054b315ae990c16cfe9-dovecot.service-pvff3H drwx------ 3 root root 4096 Jan 3 11:27 systemd-private-d7fc21f66f934054b315ae990c16cfe9-ntpd.service-UtplcI [pg13@lhrpgall tmp]$ unzip pg_recovery.zip Archive: pg_recovery.zip 886fc628534b43eb27344aaa07aabcc85f4d0b0e creating: pg_recovery-master/ inflating: pg_recovery-master/.gitignore inflating: pg_recovery-master/License inflating: pg_recovery-master/Makefile inflating: pg_recovery-master/README.md inflating: pg_recovery-master/README_zh_CN.md creating: pg_recovery-master/expected/ inflating: pg_recovery-master/expected/recovery.out inflating: pg_recovery-master/pg_recovery--1.0.sql inflating: pg_recovery-master/pg_recovery.c inflating: pg_recovery-master/pg_recovery.control creating: pg_recovery-master/sql/ inflating: pg_recovery-master/sql/recovery.sql [pg13@lhrpgall tmp]$ cd pg_recovery-master/ [pg13@lhrpgall pg_recovery-master]$ make PG_CONFIG=/pg13/pg13/bin/pg_config gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I. -I./ -I/pg13/pg13/include/postgresql/server -I/pg13/pg13/include/postgresql/internal -D_GNU_SOURCE -c -o pg_recovery.o pg_recovery.c gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -shared -o pg_recovery.so pg_recovery.o -L/pg13/pg13/lib -Wl,--as-needed -Wl,-rpath,'/pg13/pg13/lib',--enable-new-dtags [pg13@lhrpgall pg_recovery-master]$ gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I. -I./ -I/pg13/pg13/include/postgresql/server -I/pg13/pg13/include/postgresql/internal -D_GNU_SOURCE -c -o pg_recovery.o pg_recovery.c [pg13@lhrpgall pg_recovery-master]$ gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -shared -o pg_recovery.so pg_recovery.o -L/pg13/pg13/lib -Wl,--as-needed -Wl,-rpath,'/pg13/pg13/lib',--enable-new-dtags [pg13@lhrpgall pg_recovery-master]$ make install PG_CONFIG=/pg13/pg13/bin/pg_config /usr/bin/mkdir -p '/pg13/pg13/lib/postgresql' /usr/bin/mkdir -p '/pg13/pg13/share/postgresql/extension' /usr/bin/mkdir -p '/pg13/pg13/share/postgresql/extension' /usr/bin/install -c -m 755 pg_recovery.so '/pg13/pg13/lib/postgresql/pg_recovery.so' /usr/bin/install -c -m 644 .//pg_recovery.control '/pg13/pg13/share/postgresql/extension/' /usr/bin/install -c -m 644 .//pg_recovery--1.0.sql '/pg13/pg13/share/postgresql/extension/' [pg13@lhrpgall pg_recovery-master]$ /usr/bin/mkdir -p '/pg13/pg13/lib/postgresql' [pg13@lhrpgall pg_recovery-master]$ /usr/bin/mkdir -p '/pg13/pg13/share/postgresql/extension' [pg13@lhrpgall pg_recovery-master]$ /usr/bin/mkdir -p '/pg13/pg13/share/postgresql/extension' [pg13@lhrpgall pg_recovery-master]$ /usr/bin/install -c -m 755 pg_recovery.so '/pg13/pg13/lib/postgresql/pg_recovery.so' [pg13@lhrpgall pg_recovery-master]$ /usr/bin/install -c -m 644 .//pg_recovery.control '/pg13/pg13/share/postgresql/extension/' [pg13@lhrpgall pg_recovery-master]$ /usr/bin/install -c -m 644 .//pg_recovery--1.0.sql '/pg13/pg13/share/postgresql/extension/' [pg13@lhrpgall pg_recovery-master]$ pwd /tmp/pg_recovery-master [pg13@lhrpgall ~]$ psql psql (13.3) Type "help" for help. postgres=# create extension pg_recovery ; CREATE EXTENSION postgres=# postgres=# \dx List of installed extensions Name | Version | Schema | Description --------------------+---------+------------+------------------------------------------------------------------------ pageinspect | 1.8 | public | inspect the contents of database pages at a low level pg_recovery | 1.0 | public | recovery table data of update/delete/rollback rows and drop columns pg_stat_statements | 1.8 | public | track planning and execution statistics of all SQL statements executed plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language (4 rows) |
示例
init data
准备一个表和一些数据。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | lhrdb=# create extension pg_recovery ; CREATE EXTENSION lhrdb=# create table test(id int, dp int); CREATE TABLE lhrdb=# insert into test values(1, 1); INSERT 0 1 lhrdb=# insert into test values(2, 2); INSERT 0 1 lhrdb=# select * from test; id | dp ----+---- 1 | 1 2 | 2 (2 rows) |
recovery update
对数据进行变更操作，不加 WHERE 条件。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | lhrdb=# update test set id=3, dp=3; UPDATE 2 lhrdb=# select * from pg_recovery('test') as (id int, dp int); id | dp ----+---- 1 | 1 2 | 2 (2 rows) lhrdb=# select * from test; id | dp ----+---- 3 | 3 3 | 3 (2 rows) |
recovery delete
尝试恢复 DELETE 的数据。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | lhrdb=# delete from test; DELETE 2 lhrdb=# select * from test; id | dp ----+---- (0 rows) lhrdb=# select * from pg_recovery('test') as (id int, dp int); id | dp ----+---- 1 | 1 2 | 2 3 | 3 3 | 3 (4 rows) |
recovery rollback
尝试恢复回滚操作之前的数据。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | lhrdb=# begin ; BEGIN lhrdb=# insert into test values(4, 4); INSERT 0 1 lhrdb=# rollback ; ROLLBACK lhrdb=# select * from test; id | dp ----+---- (0 rows) lhrdb=# select * from pg_recovery('test') as (id int, dp int); id | dp ----+---- 1 | 1 2 | 2 3 | 3 3 | 3 4 | 4 (5 rows) |
recovery drop column
尝试恢复表中被删除的列及数据。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | lhrdb=# alter table test drop column dp; ALTER TABLE lhrdb=# select attnum from pg_attribute, pg_class where attrelid = pg_class.oid and pg_class.relname='test' and attname ~ 'dropped'; attnum -------- 2 (1 row) lhrdb=# select * from test; id ---- (0 rows) lhrdb=# select * from pg_recovery('test') as (id int, dropped_attnum_2 int); id | dropped_attnum_2 ----+------------------ 1 | 1 2 | 2 3 | 3 3 | 3 4 | 4 (5 rows) -- dropped_attnum_2: if the drop attnum is 5, set dropped_attnum_2 to dropped_attnum_5 |
show all data
显示该表历史上所有写入过的数据。
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | lhrdb=# insert into test values(5); INSERT 0 1 lhrdb=# select * from test; id ---- 5 (1 row) lhrdb=# select * from pg_recovery('test', recoveryrow => false) as (id int, recoveryrow bool); id | recoveryrow ----+------------- 1 | t 2 | t 3 | t 3 | t 4 | t 5 | f (6 rows) |
注意事项
支持的 PostgreSQL 版本
目前 pg_revovery工具已支持 PostgreSQL 12/13/14 。
可恢复事务数
PostgreSQL 通过参数 vacuum_defer_cleanup_age 值大小，可限制可恢复的事务数。如果预期需要恢复的数据量较大，可通过配置参数值，提高可恢复的事务数。
pg_recovery 通过读取 PostgreSQL dead 元组来恢复不可见的表数据。如果元组被 vacuum 清除掉，那么 pg_recovery 便不能恢复数据。
锁请求
pg_recovery 使用期间，支持正常的读表的锁请求。此外 pg_recovery未使用期间，不会对数据库造成任何额外的开销或是影响，无需暂停服务。
安装报错处理
1、若PG数据库是yum安装的，则在安装pg_recovery会报错：
1 2 3 4 | -bash-4.2$ make PG_CONFIG=/usr/pgsql-14/bin/pg_config Makefile:11: /usr/pgsql-14/lib/pgxs/src/makefiles/pgxs.mk: No such file or directory make: *** No rule to make target `/usr/pgsql-14/lib/pgxs/src/makefiles/pgxs.mk'. Stop. -bash-4.2$ |
解决：需要安装依赖包postgresql14-devel：
1 2 3 4 5 | -- 必须先安装centos-release-scl-rh -- 安装成功会在目录/etc/yum.repos.d/下产生文件CentOS-SCLo-scl-rh.repo -- 若安装不成功则会报错 Requires: llvm-toolset-7-clang >= 4.0.1 或 /opt/rh/llvm-toolset-7/root/usr/bin/clang: No such file or directory yum install -y centos-release-scl-rh yum install -y postgresql14-devel |
以上包安装成功后再安装pg_recovery即可。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 | [root@ttt yum.repos.d]# cat CentOS-SCLo-scl-rh.repo # CentOS-SCLo-rh.repo # # Please see http://wiki.centos.org/SpecialInterestGroup/SCLo for more # information [centos-sclo-rh] name=CentOS-7 - SCLo rh #baseurl=http://mirror.centos.org/centos/7/sclo/$basearch/rh/ mirrorlist=http://mirrorlist.centos.org?arch=$basearch&release=7&repo=sclo-rh gpgcheck=1 enabled=1 gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-SIG-SCLo [centos-sclo-rh-testing] name=CentOS-7 - SCLo rh Testing baseurl=http://buildlogs.centos.org/centos/7/sclo/$basearch/rh/ gpgcheck=0 enabled=0 gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-SIG-SCLo [centos-sclo-rh-source] name=CentOS-7 - SCLo rh Sources baseurl=http://vault.centos.org/centos/7/sclo/Source/rh/ gpgcheck=1 enabled=0 gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-SIG-SCLo [centos-sclo-rh-debuginfo] name=CentOS-7 - SCLo rh Debuginfo baseurl=http://debuginfo.centos.org/centos/7/sclo/$basearch/ gpgcheck=1 enabled=0 gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-SIG-SCLo |
类似工具
其它误操作恢复的文章参考：
- PG中通过pg_waldump来分析pg_wal日志：https://www.xmmup.com/pgzhongtongguopg_waldumplaifenxipg_walrizhi.html
- PG日志挖掘工具之WalMiner：https://www.xmmup.com/pgrizhiwajuegongjuzhiwalminer.html
- PG工具pg_resetwal介绍：https://www.xmmup.com/postgresql11xiugaiwal-segsizedaxiao.html
- PG之闪回数据库：https://www.xmmup.com/pgzhishanhuishujuku.html
- PG中的DDL支持回滚：https://www.xmmup.com/pgzhongdeddlzhichihuigun.html
- PostgreSQL闪回表之pg_dirtyread插件：https://www.xmmup.com/postgresqlshanhuibiaozhipg_dirtyreadchajian.html
