pg_dirtyread可以恢复没有被覆盖的delete,update，显示全部（包括新旧）的数据,但无法恢复truncate数据。
https://github.com/df7cb/pg_dirtyread
-bash-4.2$ git clone https://github.com/df7cb/pg_dirtyread.git
Cloning into 'pg_dirtyread'...
remote: Enumerating objects: 410, done.
remote: Counting objects: 100% (15/15), done.
remote: Compressing objects: 100% (11/11), done.
remote: Total 410 (delta 7), reused 11 (delta 4), pack-reused 395
Receiving objects: 100% (410/410), 80.41 KiB | 81.00 KiB/s, done.
Resolving deltas: 100% (235/235), done.
-bash-4.2$ ll
total 8
drwxrwxr-x 8 postgres postgres 4096 Jun 9 15:12 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$ cd pg_dirtyread/
-bash-4.2$ ll
total 76
drwxrwxr-x 2 postgres postgres 164 Jun 9 15:12 contrib
drwxrwxr-x 4 postgres postgres 162 Jun 9 15:12 debian
-rw-rw-r-- 1 postgres postgres 12406 Jun 9 15:12 dirtyread_tupconvert.c
-rw-rw-r-- 1 postgres postgres 1118 Jun 9 15:12 dirtyread_tupconvert.h
drwxrwxr-x 2 postgres postgres 80 Jun 9 15:12 expected
-rw-rw-r-- 1 postgres postgres 1655 Jun 9 15:12 LICENSE
-rw-rw-r-- 1 postgres postgres 351 Jun 9 15:12 Makefile
-rw-rw-r-- 1 postgres postgres 130 Jun 9 15:12 pg_dirtyread--1.0--2.sql
-rw-rw-r-- 1 postgres postgres 88 Jun 9 15:12 pg_dirtyread--1.0.sql
-rw-rw-r-- 1 postgres postgres 96 Jun 9 15:12 pg_dirtyread--2.sql
-rw-rw-r-- 1 postgres postgres 5615 Jun 9 15:12 pg_dirtyread.c
-rw-rw-r-- 1 postgres postgres 150 Jun 9 15:12 pg_dirtyread.control
-rw-rw-r-- 1 postgres postgres 6110 Jun 9 15:12 README.md
drwxrwxr-x 2 postgres postgres 63 Jun 9 15:12 sql
-rw-rw-r-- 1 postgres postgres 11687 Jun 9 15:12 tupconvert.c.upstream
-rw-rw-r-- 1 postgres postgres 1439 Jun 9 15:12 tupconvert.h.upstream
-bash-4.2$ echo $PG_CONFIG
-bash-4.2$ which pg_config
/opt/pgsql/bin/pg_config
-bash-4.2$ make PG_CONFIG=/opt/pgsql/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/opt/pgsql/include/server -I/opt/pgsql/include/internal -D_GNU_SOURCE -c -o pg_dirtyread.o pg_dirtyread.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 dirtyread_tupconvert.o dirtyread_tupconvert.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_dirtyread.so pg_dirtyread.o dirtyread_tupconvert.o -L/opt/pgsql/lib -Wl,--as-needed -Wl,-rpath,'/opt/pgsql/lib',--enable-new-dtags
-bash-4.2$ make install PG_CONFIG=make install PG_CONFIG=/opt/pgsql/bin/pg_config
/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_dirtyread.so '/opt/pgsql/lib/pg_dirtyread.so'
/usr/bin/install: cannot create regular file 鈥opt/pgsql/lib/pg_dirtyread.so鈥 Permission denied
make: *** [install-lib-shared] Error 1
-bash-4.2$ ll /opt/
total 16267592
-rw------- 1 root root 1500276 Jun 7 16:50 nohup.out
-rw-r--r-- 1 root root 16656348440 Jun 7 16:39 opt.tar.gz
drwxr-xr-x 3 root root 183 Mar 9 16:31 oracle
drwxr-xr-x 5 root root 4096 Mar 9 16:43 oracle_fdw-2.4.0
-rw-r--r-- 1 root root 150692 Mar 9 16:40 oracle_fdw-2.4.0.zip
drwxrwxrwx 2 root root 4096 Jan 18 17:27 pg
drwxr-xr-x 4 postgres postgres 26 Jan 19 16:16 pgdata
drwxr-xr-x 7 root root 68 Aug 18 2020 pgsql
-bash-4.2$ exit
logout
[root@pg ~]# chown -R postgres:postgres /opt/pgsql
[root@pg ~]# su - postgres
Last login: Thu Jun 9 14:53:35 CST 2022 on pts/1
-bash-4.2$ cd pg_dirtyread/
-bash-4.2$ make PG_CONFIG=/opt/pgsql/bin/pg_config
make: Nothing to be done for `all'.
-bash-4.2$ make install PG_CONFIG=make install PG_CONFIG=/opt/pgsql/bin/pg_config
/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_dirtyread.so '/opt/pgsql/lib/pg_dirtyread.so'
/usr/bin/install -c -m 644 .//pg_dirtyread.control '/opt/pgsql/share/extension/'
/usr/bin/install -c -m 644 .//pg_dirtyread--1.0.sql .//pg_dirtyread--1.0--2.sql .//pg_dirtyread--2.sql '/opt/pgsql/share/extension/'
make: Nothing to be done for `install'.
-bash-4.2$ psql -d jyc
Password for user postgres:
psql: error: could not connect to server: FATAL: password authentication failed for user "postgres"
-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
---------+---------+------------+------------------------------
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(1 row)
jyc=# create extension pg_dirtyread;
CREATE EXTENSION
jyc=# create extension pageinspect;
CREATE EXTENSION
jyc=# \dt
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | j | table | postgres
(1 row)
jyc=# select * from j;
id | name
------+----------------
1 | a
2222 | bbbbbbbbbbbb
3 | cc
4 | d
6 | eeeeeeeeeeeeee
(5 rows)
jyc=# select * from pg_dirtyread('j');
ERROR: a column definition list is required for functions returning "record"
LINE 1: select * from pg_dirtyread('j');
^
jyc=# select * from pg_dirtyread('j') as t(id int,name text);
ERROR: Error converting tuple descriptors!
DETAIL: Attribute "name" has type character varying(20) in corresponding attribute of type j.
jyc=# select * from pg_dirtyread('j') as t(id int,name varchar);
ERROR: Error converting tuple descriptors!
DETAIL: Attribute "name" has type character varying(20) in corresponding attribute of type j.
jyc=# select * from pg_dirtyread('j') as t(id int,name varchar(20));
id | name
------+----------------
1 | a
2 | b
2222 | bbbbbbbbbbbb
3 | cc
4 | d
5 | eeeeeeeeeeeeee
6 | eeeeeeeeeeeeee
(7 rows)
jyc=# select * from pg_dirtyread('j') j(id int,name varchar(20));
id | name
------+----------------
1 | a
2 | b
2222 | bbbbbbbbbbbb
3 | cc
4 | d
5 | eeeeeeeeeeeeee
6 | eeeeeeeeeeeeee
(7 rows)
jyc=# select * from pg_dirtyread('j') t(id int,name varchar(20));
id | name
------+----------------
1 | a
2 | b
2222 | bbbbbbbbbbbb
3 | cc
4 | d
5 | eeeeeeeeeeeeee
6 | eeeeeeeeeeeeee
(7 rows)
jyc=# \d j
Table "public.j"
Column | Type | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
id | integer | | |
name | character varying(20) | | |
jyc=# \d+ j
Table "public.j"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-----------------------+-----------+----------+---------+----------+--------------+-------------
id | integer | | | | plain | |
name | character varying(20) | | | | extended | |
Access method: heap
jyc=# delete from j where id=1;
DELETE 1
jyc=# select * from j;
id | name
------+----------------
2222 | bbbbbbbbbbbb
3 | cc
4 | d
6 | eeeeeeeeeeeeee
(4 rows)
jyc=# select * from pg_dirtyread('j') t(id int,name varchar(20));
id | name
------+----------------
1 | a
2 | b
2222 | bbbbbbbbbbbb
3 | cc
4 | d
5 | eeeeeeeeeeeeee
6 | eeeeeeeeeeeeee
(7 rows)
jyc=# select * from pg_dirtyread('j') t(id int,name varchar(10));
ERROR: Error converting tuple descriptors!
DETAIL: Attribute "name" has type character varying(20) in corresponding attribute of type j.
jyc=# truncate table j;
TRUNCATE TABLE
jyc=# select * from j;
id | name
----+------
(0 rows)
jyc=# select * from pg_dirtyread('j') t(id int,name varchar(20));
id | name
----+------
(0 rows)
最后修改时间：2022-06-09 16:34:23
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
