合 PostgreSQL误操作恢复工具之pg_dirtyread插件
Tags： PostgreSQL插件误操作恢复闪回pg_dirtyread
👉 本文共约1921个字，系统预计阅读时间或需8分钟。
目录
简介
github：https://github.com/df7cb/pg_dirtyread
误删数据总是在所难免，一种是使用延时备库，在延迟间隔内可以从备库恢复数据，另一种使用开源插件pg_dirtyread，pg_dirtyread使用时需要关闭表的自动清理功能，不然可能也恢复不了。
Oracle数据库有时候不小心删除掉数据，想查询这些数据，或者恢复数据，就可以使用带有as of子句的select语句进行闪回查询。
PG粉有福了，下面介绍一种类似“闪回查询”插件 pg_dirtyread，可以读取未被vacuum的dead数据。
版本支持：10和11已经支持，2.0以后的版本已经支持12和13，社区还是很活跃。
语法：
1 | SELECT * FROM pg_dirtyread('tablename') AS t(col1 type1, col2 type2, ...); |
安装插件pg_dirtyread
GitHub地址：https://github.com/df7cb/pg_dirtyread
若安装报权限问题，例如“Fatal error: can't create pg_dirtyread.o: Permission denied”，则可以使用root用户安装：
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 | -- Debian cat > /etc/apt/sources.list <<"EOF" deb http://mirrors.ustc.edu.cn/debian stable main contrib non-free deb http://mirrors.ustc.edu.cn/debian stable-updates main contrib non-free EOF sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list' wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - apt-get update -y apt-get install -y postgresql-13-dirtyread # su - pg13 wget https://github.com/df7cb/pg_dirtyread/archive/refs/tags/2.4.zip unzip 2.4.zip cd pg_dirtyread-2.4/ -- 会生成要执行的命令 make PG_CONFIG=/pg13/pg13/bin/pg_config make install PG_CONFIG=/pg13/pg13/bin/pg_config C:\Users\lhrxxt>psql -U postgres -h 192.168.66.35 -p 15432 Password for user postgres: psql (13.2) Type "help" for help. postgres=# \dx List of installed extensions Name | Version | Schema | Description ---------+---------+------------+------------------------------ plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language (1 row) 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) |
示例：
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 | [root@ttt tmp]# cd pg_dirtyread-master/ [root@ttt pg_dirtyread-master]# make PG_CONFIG=/usr/pgsql-14/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 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -fPIC -I. -I./ -I/usr/pgsql-14/include/server -I/usr/pgsql-14/include/internal -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include -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 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -fPIC -I. -I./ -I/usr/pgsql-14/include/server -I/usr/pgsql-14/include/internal -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include -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 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -fPIC -shared -o pg_dirtyread.so pg_dirtyread.o dirtyread_tupconvert.o -L/usr/pgsql-14/lib -Wl,--as-needed -L/usr/lib64/llvm5.0/lib -L/usr/lib64 -Wl,--as-needed -Wl,-rpath,'/usr/pgsql-14/lib',--enable-new-dtags /opt/rh/llvm-toolset-7/root/usr/bin/clang -Wno-ignored-attributes -fno-strict-aliasing -fwrapv -Wno-unused-command-line-argument -O2 -I. -I./ -I/usr/pgsql-14/include/server -I/usr/pgsql-14/include/internal -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include -flto=thin -emit-llvm -c -o pg_dirtyread.bc pg_dirtyread.c /opt/rh/llvm-toolset-7/root/usr/bin/clang -Wno-ignored-attributes -fno-strict-aliasing -fwrapv -Wno-unused-command-line-argument -O2 -I. -I./ -I/usr/pgsql-14/include/server -I/usr/pgsql-14/include/internal -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include -flto=thin -emit-llvm -c -o dirtyread_tupconvert.bc dirtyread_tupconvert.c [root@ttt pg_dirtyread-master]# [root@ttt pg_dirtyread-master]# [root@ttt pg_dirtyread-master]# [root@ttt pg_dirtyread-master]# [root@ttt pg_dirtyread-master]# [root@ttt pg_dirtyread-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 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -fPIC -I. -I./ -I/usr/pgsql-14/include/server -I/usr/pgsql-14/include/internal -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include -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 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -fPIC -shared -o pg_dirtyread.so pg_dirtyread.o dirtyread_tupconvert.o -L/usr/pgsql-14/lib -Wl,--as-needed -L/usr/lib64/llvm5.0/lib -L/usr/lib64 -Wl,--as-needed -Wl,-rpath,'/usr/pgsql-14/lib',--enable-new-dtags /opt/rh/llvm-toolset-7/root/usr/bin/clang -Wno-ignored-attributes -fno-strict-aliasing -fwrapv -Wno-unused-command-line-argument -O2 -I. -I./ -I/usr/pgsql-14/include/server -I/usr/pgsql-14/include/internal -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include -flto=thin -emit-llvm -c -o pg_dirtyread.bc pg_dirtyread.c /opt/rh/llvm-toolset-7/root/usr/bin/clang -Wno-ignored-attributes -fno-strict-aliasing -fwrapv -Wno-unused-command-line-argument -O2 -I. -I./ -I/usr/pgsql-14/include/server -I/usr/pgsql-14/include/internal -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include -flto=thin -emit-llvm -c -o dirtyread_tupconvert.bc dirtyread_tupconvert.c [root@ttt pg_dirtyread-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 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -fPIC -I. -I./ -I/usr/pgsql-14/include/server -I/usr/pgsql-14/include/internal -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include -c -o dirtyread_tupconvert.o dirtyread_tupconvert.c [root@ttt pg_dirtyread-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 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -fPIC -shared -o pg_dirtyread.so pg_dirtyread.o dirtyread_tupconvert.o -L/usr/pgsql-14/lib -Wl,--as-needed -L/usr/lib64/llvm5.0/lib -L/usr/lib64 -Wl,--as-needed -Wl,-rpath,'/usr/pgsql-14/lib',--enable-new-dtags [root@ttt pg_dirtyread-master]# /opt/rh/llvm-toolset-7/root/usr/bin/clang -Wno-ignored-attributes -fno-strict-aliasing -fwrapv -Wno-unused-command-line-argument -O2 -I. -I./ -I/usr/pgsql-14/include/server -I/usr/pgsql-14/include/internal -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include -flto=thin -emit-llvm -c -o pg_dirtyread.bc pg_dirtyread.c [root@ttt pg_dirtyread-master]# /opt/rh/llvm-toolset-7/root/usr/bin/clang -Wno-ignored-attributes -fno-strict-aliasing -fwrapv -Wno-unused-command-line-argument -O2 -I. -I./ -I/usr/pgsql-14/include/server -I/usr/pgsql-14/include/internal -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include -flto=thin -emit-llvm -c -o dirtyread_tupconvert.bc dirtyread_tupconvert.c [root@ttt pg_dirtyread-master]# [root@ttt pg_dirtyread-master]# make install PG_CONFIG=/usr/pgsql-14/bin/pg_config /bin/mkdir -p '/usr/pgsql-14/lib' /bin/mkdir -p '/usr/pgsql-14/share/extension' /bin/mkdir -p '/usr/pgsql-14/share/extension' /bin/install -c -m 755 pg_dirtyread.so '/usr/pgsql-14/lib/pg_dirtyread.so' /bin/install -c -m 644 .//pg_dirtyread.control '/usr/pgsql-14/share/extension/' /bin/install -c -m 644 .//pg_dirtyread--1.0.sql .//pg_dirtyread--1.0--2.sql .//pg_dirtyread--2.sql '/usr/pgsql-14/share/extension/' /bin/mkdir -p '/usr/pgsql-14/lib/bitcode/pg_dirtyread' /bin/mkdir -p '/usr/pgsql-14/lib/bitcode'/pg_dirtyread/ /bin/install -c -m 644 pg_dirtyread.bc '/usr/pgsql-14/lib/bitcode'/pg_dirtyread/./ /bin/install -c -m 644 dirtyread_tupconvert.bc '/usr/pgsql-14/lib/bitcode'/pg_dirtyread/./ cd '/usr/pgsql-14/lib/bitcode' && /usr/lib64/llvm5.0/bin/llvm-lto -thinlto -thinlto-action=thinlink -o pg_dirtyread.index.bc pg_dirtyread/pg_dirtyread.bc pg_dirtyread/dirtyread_tupconvert.bc [root@ttt pg_dirtyread-master]# [root@ttt pg_dirtyread-master]# [root@ttt pg_dirtyread-master]# [root@ttt pg_dirtyread-master]# /bin/mkdir -p '/usr/pgsql-14/lib' [root@ttt pg_dirtyread-master]# /bin/mkdir -p '/usr/pgsql-14/share/extension' [root@ttt pg_dirtyread-master]# /bin/mkdir -p '/usr/pgsql-14/share/extension' /bin/install -c -m 644 .//pg_dirtyread.control '/usr/pgsql-14/share/extension/' [root@ttt pg_dirtyread-master]# /bin/install -c -m 755 pg_dirtyread.so '/usr/pgsql-14/lib/pg_dirtyread.so' [root@ttt pg_dirtyread-master]# /bin/install -c -m 644 .//pg_dirtyread.control '/usr/pgsql-14/share/extension/' [root@ttt pg_dirtyread-master]# /bin/install -c -m 644 .//pg_dirtyread--1.0.sql .//pg_dirtyread--1.0--2.sql .//pg_dirtyread--2.sql '/usr/pgsql-14/share/extension/' [root@ttt pg_dirtyread-master]# /bin/mkdir -p '/usr/pgsql-14/lib/bitcode/pg_dirtyread' /bin/mkdir -p '/usr/pgsql-14/lib/bitcode'/pg_dirtyread/ [root@ttt pg_dirtyread-master]# /bin/mkdir -p '/usr/pgsql-14/lib/bitcode'/pg_dirtyread/ [root@ttt pg_dirtyread-master]# /bin/install -c -m 644 pg_dirtyread.bc '/usr/pgsql-14/lib/bitcode'/pg_dirtyread/./ [root@ttt pg_dirtyread-master]# /bin/install -c -m 644 dirtyread_tupconvert.bc '/usr/pgsql-14/lib/bitcode'/pg_dirtyread/./ [root@ttt pg_dirtyread-master]# cd '/usr/pgsql-14/lib/bitcode' && /usr/lib64/llvm5.0/bin/llvm-lto -thinlto -thinlto-action=thinlink -o pg_dirtyread.index.bc pg_dirtyread/pg_dirtyread.bc pg_dirtyread/dirtyread_tupconvert.bc [root@ttt bitcode]# [root@ttt bitcode]# su - postgres Last login: Thu Mar 16 09:57:48 CST 2023 on pts/1 -bash-4.2$ psql psql (14.7) Type "help" for help. 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 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 (3 rows) postgres=# create extension pageinspect; CREATE EXTENSION postgres=# |
安装插件pageinspect extension
pageinspect模块提供函数让你从低层次观察数据库页面的内容，这对于调试目的很有用。所有这些函数只能被超级用户使用。
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
