PostgreSQL学习随笔22 利用工具恢复误删除数据2之pg_dirtyread

编译安装
下载路径 https://github.com/df7cb/pg_dirtyread
[root@pgexp1 pg_dirtyread-2.4]# 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  


[root@pgexp1 pg_dirtyread-2.4]# make install 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 -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  
/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/'

加载和使用
一旦构建并安装了 pg_dirtyread，您就可以将它添加到数据库中。加载 pg_dirtyread 就像以超级用户身份连接到数据库并运行一样简单。
该pg_dirtyread()函数返回 RECORD，因此有必要附加一个描述表模式的表别名子句。列按名称匹配，因此可以在别名中省略某些列，或重新排列列。


使用示例
postgres=# CREATE EXTENSION pg_dirtyread;
CREATE EXTENSION
postgres=# \dx
                          List of installed extensions
     Name     | Version |   Schema   |               Description                
--------------+---------+------------+------------------------------------------
 pg_dirtyread | 2       | public     | Read dead but unvacuumed rows from table
 plpgsql      | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)


postgres=# -- Create table and disable autovacuum
postgres=# CREATE TABLE test (bar bigint, baz text);
CREATE TABLE
postgres=# ALTER TABLE test SET (autovacuum_enabled = false, toast.autovacuum_enabled = false);
ALTER TABLE
postgres=# INSERT INTO test VALUES (1, 'Test'), (2, 'New Test');
INSERT 0 2
postgres=# DELETE FROM test WHERE bar = 1;
DELETE 1
postgres=# SELECT * FROM pg_dirtyread('test') as t(bar bigint, baz text);
 bar |   baz    
-----+----------
   1 | Test
   2 | New Test
(2 rows)