作者：张连壮 PostgreSQL 研发工程师
从事多年 PostgreSQL 数据库内核开发，对 citus 有非常深入的研究。
快速恢复丢失的数据是数据库的重要功能要求，一般推荐使用官方推荐的工具。对于开源数据库，生态中也有很多有用的开源工具。
[En]
Rapid recovery of lost data is an important functional requirement of the database, and it is generally recommended to use officially recommended tools. For open source databases, there are also many useful open source tools in the ecology.
PostgreSQL 是非常流行的开源数据库，接下来介绍一款近期在社区开源的 PostgreSQL 数据找回工具 pg_recovery ，并实例演示如何找回误操作而丢失的数据。
pg_recovery 是一款 PostgreSQL 数据找回工具。可以恢复 COMMIT / DELETE / UPDATE / ROLLBACK / DROP COLUMN 操作后导致的数据变化，并以表的形式返回。安装方便，操作简单。仓库地址：https://github.com/radondb/pg_recovery
快速安装
根据环境配置 PG_CONFIG。
$ make PG_CONFIG=/home/lzzhang/PG/postgresql/base/bin/pg_config
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -g -g -O0 -fPIC -I. -I./ -I/home/lzzhang/PG/postgresql/base/include/server -I/home/lzzhang/PG/postgresql/base/include/internal -D_GNU_SOURCE -c -o pg_recovery.o pg_recovery.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -g -g -O0 -fPIC -shared -o pg_recovery.so pg_recovery.o -L/home/lzzhang/PG/postgresql/base/lib -Wl,--as-needed -Wl,-rpath,'/home/lzzhang/PG/postgresql/base/lib',--enable-new-dtags
$ make install PG_CONFIG=/home/lzzhang/PG/postgresql/base/bin/pg_config
/usr/bin/mkdir -p '/home/lzzhang/PG/postgresql/base/lib'
/usr/bin/mkdir -p '/home/lzzhang/PG/postgresql/base/share/extension'
/usr/bin/mkdir -p '/home/lzzhang/PG/postgresql/base/share/extension'
/usr/bin/install -c -m 755 pg_recovery.so '/home/lzzhang/PG/postgresql/base/lib/pg_recovery.so'
/usr/bin/install -c -m 644 .//pg_recovery.control '/home/lzzhang/PG/postgresql/base/share/extension/'
/usr/bin/install -c -m 644 .//pg_recovery--1.0.sql '/home/lzzhang/PG/postgresql/base/share/extension/'
插件已成功初始化，并返回以下信息。
[En]
The plug-in was initialized successfully and the following information was returned.
$ create extension pg_recovery ;
CREATE EXTENSION
1. 准备初始化数据
准备一个表和一些数据。
$ create table lzzhang(id int, dp int);
CREATE TABLE
insert into lzzhang values(1, 1);
INSERT 0 1
$ insert into lzzhang values(2, 2);
INSERT 0 1
2. 找回 UPDATE 数据
对数据进行变更操作，不加 WHERE 条件。
$ update lzzhang set id=3, dp=3;
UPDATE 2
lzzhang=# select * from pg_recovery('lzzhang') as (id int, dp int);
id | dp
3 | 3
3 | 3
(2 rows)
3. 找回 DELETE 数据
尝试恢复 DELETE 的数据。
$ delete from lzzhang;
DELETE 2
lzzhang=# select * from lzzhang;
id | dp
1 | 1
2 | 2
3 | 3
3 | 3
(4 rows)
4. 找回 ROLLBACK 数据
尝试在回滚操作之前恢复数据。
[En]
Attempt to recover the data before the rollback operation.
$ begin ;
BEGIN
$ insert into lzzhang values(4, 4);
INSERT 0 1
$ rollback ;
ROLLBACK
$ select * from lzzhang;
id | dp
1 | 1
2 | 2
3 | 3
3 | 3
4 | 4
(5 rows)
5. 找回 DROP COLUMN 数据
尝试恢复表中已删除的列和数据。
[En]
Try to recover the deleted columns and data in the table.
`
$ alter table lzzhang drop column dp;
ALTER TABLE
$ select attnum from pg_attribute, pg_class where attrelid = pg_class.oid and pg_class.relname=’lzzhang’ and attname ~ ‘dropped’;
attnum
(0 rows)
$ select * from pg_recovery(‘lzzhang’) as (id int, dropped_attnum_2 int);
id | dropped_attnum_2
5
(1 row)
$ select * from pg_recovery(‘lzzhang’, recoveryrow => false) as (id int, recoveryrow bool);
id | recoveryrow
Original: https://www.cnblogs.com/radondb/p/15598046.html
Author: RadonDB
Title: 工具 | 一条 SQL 实现 PostgreSQL 数据找回
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/508000/
转载文章受原作者版权保护。转载请注明原作者出处！