合 PG误操作恢复工具之pg_recovery插件
Tags： PostgreSQL插件误操作恢复闪回pg_recovery
👉 本文共约1333个字，系统预计阅读时间或需6分钟。
目录
简介
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) |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
