pg_recovery类似pg_dirtyread可以恢复没有被覆盖的delete,update，只显示变更的数据，同样无法恢复truncate数据。
https://github.com/radondb/pg_recovery
-bash-4.2$ date
Thu Jun 9 16:18:01 CST 2022
-bash-4.2$ git clone https://github.com/radondb/pg_recovery.git
Cloning into 'pg_recovery'...
fatal: unable to access 'https://github.com/radondb/pg_recovery.git/': Encountered end of file
-bash-4.2$ git clone https://github.com/radondb/pg_recovery.git
Cloning into 'pg_recovery'...
^C
-bash-4.2$ ll
total 8
drwxrwxr-x 8 postgres postgres 4096 Jun 9 15:13 pg_dirtyread
drwxrwxr-x 6 postgres postgres 334 Jun 9 13:36 pg_filedump
-rwxr-xr-x 1 postgres postgres 28 Jun 8 08:47 startup.sh
-bash-4.2$ wget https://github.com/radondb/pg_recovery/archive/refs/heads/master.zip
--2022-06-09 16:21:13-- https://github.com/radondb/pg_recovery/archive/refs/heads/master.zip
Resolving github.com (github.com)... 20.205.243.166
Connecting to github.com (github.com)|20.205.243.166|:443... connected.
HTTP request sent, awaiting response... 302 Found
Location: https://codeload.github.com/radondb/pg_recovery/zip/refs/heads/master [following]
--2022-06-09 16:21:15-- 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: 鈥榤aster.zip鈥
[ <=> ] 13,023 --.-K/s in 0.08s
2022-06-09 16:21:16 (154 KB/s) - 鈥榤aster.zip鈥saved [13023]
-bash-4.2$ ll
total 24
-rw-rw-r-- 1 postgres postgres 13023 Jun 9 16:21 master.zip
drwxrwxr-x 8 postgres postgres 4096 Jun 9 15:13 pg_dirtyread
drwxrwxr-x 6 postgres postgres 334 Jun 9 13:36 pg_filedump
-rwxr-xr-x 1 postgres postgres 28 Jun 8 08:47 startup.sh
-bash-4.2$ unzip master.zip
Archive: master.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
-bash-4.2$ pwd
/home/postgres
-bash-4.2$ ll
total 24
-rw-rw-r-- 1 postgres postgres 13023 Jun 9 16:21 master.zip
drwxrwxr-x 8 postgres postgres 4096 Jun 9 15:13 pg_dirtyread
drwxrwxr-x 6 postgres postgres 334 Jun 9 13:36 pg_filedump
drwxrwxr-x 4 postgres postgres 198 Nov 22 2021 pg_recovery-master
-rwxr-xr-x 1 postgres postgres 28 Jun 8 08:47 startup.sh
-bash-4.2$ cd pg_recovery-master/
-bash-4.2$ ll
total 48
drwxrwxr-x 2 postgres postgres 26 Nov 22 2021 expected
-rw-rw-r-- 1 postgres postgres 1317 Nov 22 2021 License
-rw-rw-r-- 1 postgres postgres 196 Nov 22 2021 Makefile
-rw-rw-r-- 1 postgres postgres 126 Nov 22 2021 pg_recovery--1.0.sql
-rw-rw-r-- 1 postgres postgres 14977 Nov 22 2021 pg_recovery.c
-rw-rw-r-- 1 postgres postgres 178 Nov 22 2021 pg_recovery.control
-rw-rw-r-- 1 postgres postgres 4151 Nov 22 2021 README.md
-rw-rw-r-- 1 postgres postgres 4386 Nov 22 2021 README_zh_CN.md
drwxrwxr-x 2 postgres postgres 26 Nov 22 2021 sql
-bash-4.2$ export PG_CONFIG=/opt/pgsql/bin/pg_config
-bash-4.2$ echo $PG_CONFIG
/opt/pgsql/bin/pg_config
-bash-4.2$ make & make install
[1] 9985
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/opt/pgsql/include/server -I/opt/pgsql/include/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 -I. -I./ -I/opt/pgsql/include/server -I/opt/pgsql/include/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/opt/pgsql/lib -Wl,--as-needed -Wl,-rpath,'/opt/pgsql/lib',--enable-new-dtags
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/opt/pgsql/lib -Wl,--as-needed -Wl,-rpath,'/opt/pgsql/lib',--enable-new-dtags
/usr/bin/mkdir -p '/opt/pgsql/lib'
/usr/bin/mkdir -p '/opt/pgsql/share/extension'
/usr/bin/mkdir -p '/opt/pgsql/share/extension'
/usr/bin/install -c -m 755 pg_recovery.so '/opt/pgsql/lib/pg_recovery.so'
/usr/bin/install -c -m 644 .//pg_recovery.control '/opt/pgsql/share/extension/'
/usr/bin/install -c -m 644 .//pg_recovery--1.0.sql '/opt/pgsql/share/extension/'
[1]+ Done make
-bash-4.2$ pwd
/home/postgres/pg_recovery-master
-bash-4.2$ ll
total 80
drwxrwxr-x 2 postgres postgres 26 Nov 22 2021 expected
-rw-rw-r-- 1 postgres postgres 1317 Nov 22 2021 License
-rw-rw-r-- 1 postgres postgres 196 Nov 22 2021 Makefile
-rw-rw-r-- 1 postgres postgres 126 Nov 22 2021 pg_recovery--1.0.sql
-rw-rw-r-- 1 postgres postgres 14977 Nov 22 2021 pg_recovery.c
-rw-rw-r-- 1 postgres postgres 178 Nov 22 2021 pg_recovery.control
-rw-rw-r-- 1 postgres postgres 11912 Jun 9 16:22 pg_recovery.o
-rwxrwxr-x 1 postgres postgres 18392 Jun 9 16:22 pg_recovery.so
-rw-rw-r-- 1 postgres postgres 4151 Nov 22 2021 README.md
-rw-rw-r-- 1 postgres postgres 4386 Nov 22 2021 README_zh_CN.md
drwxrwxr-x 2 postgres postgres 26 Nov 22 2021 sql
-bash-4.2$ make && make install
make: Nothing to be done for `all'.
/usr/bin/mkdir -p '/opt/pgsql/lib'
/usr/bin/mkdir -p '/opt/pgsql/share/extension'
/usr/bin/mkdir -p '/opt/pgsql/share/extension'
/usr/bin/install -c -m 755 pg_recovery.so '/opt/pgsql/lib/pg_recovery.so'
/usr/bin/install -c -m 644 .//pg_recovery.control '/opt/pgsql/share/extension/'
/usr/bin/install -c -m 644 .//pg_recovery--1.0.sql '/opt/pgsql/share/extension/'
-bash-4.2$ ll
total 80
drwxrwxr-x 2 postgres postgres 26 Nov 22 2021 expected
-rw-rw-r-- 1 postgres postgres 1317 Nov 22 2021 License
-rw-rw-r-- 1 postgres postgres 196 Nov 22 2021 Makefile
-rw-rw-r-- 1 postgres postgres 126 Nov 22 2021 pg_recovery--1.0.sql
-rw-rw-r-- 1 postgres postgres 14977 Nov 22 2021 pg_recovery.c
-rw-rw-r-- 1 postgres postgres 178 Nov 22 2021 pg_recovery.control
-rw-rw-r-- 1 postgres postgres 11912 Jun 9 16:22 pg_recovery.o
-rwxrwxr-x 1 postgres postgres 18392 Jun 9 16:22 pg_recovery.so
-rw-rw-r-- 1 postgres postgres 4151 Nov 22 2021 README.md
-rw-rw-r-- 1 postgres postgres 4386 Nov 22 2021 README_zh_CN.md
drwxrwxr-x 2 postgres postgres 26 Nov 22 2021 sql
-bash-4.2$ psql -d jyc
Password for user postgres:
psql (12.2)
Type "help" for help.
jyc=# \dx
List of installed extensions
Name | Version | Schema | Description
--------------+---------+------------+-------------------------------------------------------
pageinspect | 1.7 | public | inspect the contents of database pages at a low level
pg_dirtyread | 2 | public | Read dead but unvacuumed rows from table
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(3 rows)
jyc=# create extension pg_recovery;
CREATE EXTENSION
jyc=# \dx
List of installed extensions
Name | Version | Schema | Description
--------------+---------+------------+---------------------------------------------------------------------
pageinspect | 1.7 | public | inspect the contents of database pages at a low level
pg_dirtyread | 2 | public | Read dead but unvacuumed rows from table
pg_recovery | 1.0 | public | recovery table data of update/delete/rollback rows and drop columns
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(4 rows)
jyc=# select * from j;
id | name
----+------
(0 rows)
jyc=# insert into j values(4,'d');
INSERT 0 1
jyc=# insert into j values(3,'c');
INSERT 0 1
jyc=# insert into j values(2,'bbb');
INSERT 0 1
jyc=# insert into j values(1,'ab');
INSERT 0 1
jyc=#
jyc=# select * from j;
id | name
----+------
4 | d
3 | c
2 | bbb
1 | ab
(4 rows)
jyc=# checkpoint;
CHECKPOINT
jyc=# select * from j;
id | name
----+------
4 | d
3 | c
2 | bbb
1 | ab
(4 rows)
jyc=# select pg_relation_filepath('j');
pg_relation_filepath
--------------------------
base/331531883/331531918
(1 row)
jyc=# update j set id=5 where id=1;
UPDATE 1
jyc=# select * from j;
id | name
----+------
4 | d
3 | c
2 | bbb
5 | ab
(4 rows)
jyc=# \dt j
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | j | table | postgres
(1 row)
jyc=# \d j
Table "public.j"
Column | Type | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
id | integer | | |
name | character varying(20) | | |
jyc=# select * from pg_recovery('j') t(id int,name varchar(20));
id | name
----+------
1 | ab
(1 row)
jyc=# select * from pg_recovery('j') (id int,name varchar(20));
ERROR: syntax error at or near "("
LINE 1: select * from pg_recovery('j') (id int,name varchar(20));
^
jyc=# select * from pg_recovery('j') as (id int,name varchar(20));
id | name
----+------
1 | ab
(1 row)
jyc=# select * from pg_recovery('j') j(id int,name varchar(20));
id | name
----+------
1 | ab
(1 row)
jyc=# select * from j;
id | name
----+------
4 | d
3 | c
2 | bbb
5 | ab
(4 rows)
jyc=# delete from j where id=5;
DELETE 1
jyc=# select * from j;
id | name
----+------
4 | d
3 | c
2 | bbb
(3 rows)
jyc=# select * from pg_recovery('j') j(id int,name varchar(20));
id | name
----+------
1 | ab
5 | ab
(2 rows)
jyc=# checkpoint;
CHECKPOINT
jyc=# select * from pg_recovery('j') j(id int,name varchar(20));
id | name
----+------
1 | ab
5 | ab
(2 rows)
jyc=# select * from j;
id | name
----+------
4 | d
3 | c
2 | bbb
(3 rows)
jyc=# select pg_relation_filepath('j');
pg_relation_filepath
--------------------------
base/331531883/331531918
(1 row)
jyc=# truncate table j;
TRUNCATE TABLE
jyc=# select * from j;
id | name
----+------
(0 rows)
jyc=# select pg_relation_filepath('j');
pg_relation_filepath
--------------------------
base/331531883/331531921
(1 row)
jyc=# \q
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
