不過在開發資料庫原始碼或是進行罕見問題除錯，還是有一點可能去碰的。
這邊紀錄的一個叫做 pg_filedump 的工具，專門將資料檔的內容「倒出來」。
pg_filedump 並沒有專門放到官方 YUM Repo 裡面，而是放在官方 Wiki 頁面：Pg filedump - PostgreSQL wiki。
這邊使用 EDB 12 的企業版來作示範～
編譯之前，總是要準備編譯環境：這些步驟的準備，主要是先準備 EDB 的 header file 套件與基本編譯群組，並反覆幾次操作後面編譯，確認缺少什麼模組一一補上的。
user@lxdlab:~$ lxc launch images:centos/7/amd64 edb12-dev user@lxdlab:~$ lxc shell edb12-dev
[root@edb12-dev ~]# yum install -y edb-as12-server-devel openssl-devel [root@edb12-dev ~]# yum group install -y "Development Tools" [root@edb12-dev ~]# yum install -y centos-release-scl epel-release [root@edb12-dev ~]# yum-config-manager --enable rhel-server-rhscl-7-rpms [root@edb12-dev ~]# yum install -y devtoolset-7 llvm-toolset-7 [root@edb12-dev ~]#
首先下載下來（從上面頁面找出連結）
[root@edb12-dev ~]# yum install -y wget [root@edb12-dev ~]# wget "https://git.postgresql.org/gitweb/?p=pg_filedump.git;a=snapshot;h=REL_12_0;sf=tgz" -O pg_filedump-REL_12_0-a22c98e.tar.gz [root@edb12-dev ~]# tar -xvf pg_filedump-REL_12_0-a22c98e.tar.gz
下載後是一個原始碼，需要編譯：注意編譯時 pg_config 指令需要在 PATH 環境變數上
[root@edb12-dev ~]# export PATH=/usr/edb/as12/bin:$PATH [root@edb12-dev ~]# cd pg_filedump-REL_12_0-a22c98e/ [root@edb12 pg_filedump-REL_12_0-a22c98e]# make -f Makefile.contrib USE_PGXS=1 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 -I/usr/libexec/edb-as12-icu53.1/include/ -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -I/usr/include/et -I. -I./ -I/usr/edb/as12/include/server -I/usr/edb/as12/include/internal -I/usr/libexec/edb-as12-icu53.1/include/ -I/usr/include/et -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include -c -o decode.o decode.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 -I/usr/libexec/edb-as12-icu53.1/include/ -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -I/usr/include/et -I. -I./ -I/usr/edb/as12/include/server -I/usr/edb/as12/include/internal -I/usr/libexec/edb-as12-icu53.1/include/ -I/usr/include/et -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include -c -o pg_filedump.o pg_filedump.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 -I/usr/libexec/edb-as12-icu53.1/include/ -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -I/usr/include/et -I. -I./ -I/usr/edb/as12/include/server -I/usr/edb/as12/include/internal -I/usr/libexec/edb-as12-icu53.1/include/ -I/usr/include/et -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include -c -o stringinfo.o stringinfo.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 -I/usr/libexec/edb-as12-icu53.1/include/ -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -I/usr/include/et decode.o pg_filedump.o stringinfo.o -L/usr/edb/as12/lib -L/usr/libexec/edb-as12-icu53.1/lib/ -L/usr/lib64/llvm5.0/lib -L/usr/lib64 -Wl,--as-needed -Wl,-rpath,'/usr/edb/as12/lib',--enable-new-dtags -L/usr/edb/as12/lib -lpgcommon -lpgport -L/usr/edb/as12/lib -lpq -o pg_filedump /opt/rh/llvm-toolset-7/root/usr/bin/clang -Wno-ignored-attributes -fno-strict-aliasing -fwrapv -O2 -I. -I./ -I/usr/edb/as12/include/server -I/usr/edb/as12/include/internal -I/usr/libexec/edb-as12-icu53.1/include/ -I/usr/include/et -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include -flto=thin -emit-llvm -c -o decode.bc decode.c /opt/rh/llvm-toolset-7/root/usr/bin/clang -Wno-ignored-attributes -fno-strict-aliasing -fwrapv -O2 -I. -I./ -I/usr/edb/as12/include/server -I/usr/edb/as12/include/internal -I/usr/libexec/edb-as12-icu53.1/include/ -I/usr/include/et -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include -flto=thin -emit-llvm -c -o pg_filedump.bc pg_filedump.c /opt/rh/llvm-toolset-7/root/usr/bin/clang -Wno-ignored-attributes -fno-strict-aliasing -fwrapv -O2 -I. -I./ -I/usr/edb/as12/include/server -I/usr/edb/as12/include/internal -I/usr/libexec/edb-as12-icu53.1/include/ -I/usr/include/et -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include -flto=thin -emit-llvm -c -o stringinfo.bc stringinfo.c [root@edb12 pg_filedump-REL_12_0-a22c98e]# make install DESTDIR=~/edb_as12_pg_filedump/ /bin/mkdir -p '/root/edb_as12_pg_filedump//usr/edb/as12/bin' /bin/install -c pg_filedump '/root/edb_as12_pg_filedump//usr/edb/as12/bin' [root@edb12-dev pg_filedump-REL_12_0-a22c98e]#
打包起來：這邊打包的方式，後續只要解壓縮到根目錄就可以正確安裝了
[root@edb12-dev pg_filedump-REL_12_0-a22c98e]# cd ~/edb_as12_pg_filedump/ [root@edb12-dev edb_as12_pg_filedump]# tar -czvf ~/edb_as12_pg_filedump.tgz ./ ./ ./usr/ ./usr/edb/ ./usr/edb/as12/ ./usr/edb/as12/bin/ ./usr/edb/as12/bin/pg_filedump [root@edb12-dev edb_as12_pg_filedump]# ls -l ~/edb_as12_pg_filedump.tgz -rw-r--r-- 1 root root 102119 Nov 15 03:50 /root/edb_as12_pg_filedump.tgz [root@edb12-dev edb_as12_pg_filedump]#
[root@edb12 ~]# tar -xvf ~/edb_as12_pg_filedump.tgz -C / ./ ./usr/ ./usr/edb/ ./usr/edb/as12/ ./usr/edb/as12/bin/ ./usr/edb/as12/bin/pg_filedump [root@edb12 ~]#
查看一下執行說明頁面。
[root@edb12 ~]# su - enterprisedb
[enterprisedb@edb12 ~]$ pg_filedump --help
Error: Missing file name to dump.
Usage: pg_filedump [-abcdfhikxy] [-R startblock [endblock]] [-D attrlist] [-S blocksize] [-s segsize] [-n segnumber] file
Display formatted contents of a PostgreSQL heap/index/control file
Defaults are: relative addressing, range of the entire file, block
size as listed on block 0 in the file
The following options are valid for heap and index files:
-a Display absolute addresses when formatting (Block header
information is always block relative)
-b Display binary block images within a range (Option will turn
off all formatting options)
-d Display formatted block content dump (Option will turn off
all other formatting options)
-D Decode tuples using given comma separated list of types
Supported types:
bigint bigserial bool char charN date float float4 float8 int
json macaddr name oid real serial smallint smallserial text
time timestamp timetz uuid varchar varcharN xid xml
~ ignores all attributes left in a tuple
-f Display formatted block content dump along with interpretation
-h Display this information
-i Display interpreted item details
-k Verify block checksums
-o Do not dump old values.
-R Display specific block ranges within the file (Blocks are
indexed from 0)
[startblock]: block to start at
[endblock]: block to end at
A startblock without an endblock will format the single block
-s Force segment size to [segsize]
-t Dump TOAST files
-v Ouput additional information about TOAST relations
-n Force segment number to [segnumber]
-S Force block size to [blocksize]
-x Force interpreted formatting of block items as index items
-y Force interpreted formatting of block items as heap items
The following options are valid for control files:
-c Interpret the file listed as a control file
-f Display formatted content dump along with interpretation
-S Force block size to [blocksize]
Report bugs to <pgsql-bugs@postgresql.org>
[enterprisedb@edb12 ~]$
要查看的東西在資料庫的 base/ 目錄，或是外部的 tablespace 目錄。這些平常都是禁地～
[enterprisedb@edb12 ~]$ ls $PGDATA/base/ 1 16222 16223 16224 16388 16454 16488 24678 pgsql_tmp [enterprisedb@edb12 ~]$
先定位打算查看的表格
edb=# select oid,datname from pg_database ;
oid | datname
-------+------------
16223 | postgres
16224 | edb
1 | template1
16222 | template0
16388 | asciitest
16454 | test
16488 | latin1test
24678 | trgtdb
(8 rows)
Time: 1.558 ms
edb=#
edb=# \d test
Table "public.test"
Column | Type | Collation | Nullable | Default
--------+----------------------+-----------+----------+---------
txt | character varying(5) | | |
edb=# select oid from pg_class where relname ='test';
oid
-------
33131
(1 row)
Time: 0.611 ms
edb=#
edb=# select * from test;
txt
-----------------
12345
一二三四五
(2 rows)
Time: 0.480 ms
edb=#
接著再用 pg_filedump 去看它：這邊直接指定表格定義，就可以倒出內容物：不過不知為什麼，好像要先單純倒一次（不加選項）之後才可以順利指定 Datatype 呈現內容。
[enterprisedb@edb12 ~]$ pg_filedump $PGDATA/base/16224/33131 ******************************************************************* * PostgreSQL File/Block Formatted Dump Utility * * File: /pgdata/as12/data/base/16224/33131 * Options used: None ******************************************************************* Block 0 ******************************************************** <Header> ----- Block Offset: 0x00000000 Offsets: Lower 32 (0x0020) Block: Size 8192 Version 4 Upper 8120 (0x1fb8) LSN: logid 2 recoff 0x30018270 Special 8192 (0x2000) Items: 2 Free Space: 8088 Checksum: 0x0000 Prune XID: 0x00000000 Flags: 0x0000 () Length (including item array): 32 <Data> ----- Item 1 -- Length: 30 Offset: 8160 (0x1fe0) Flags: NORMAL Item 2 -- Length: 40 Offset: 8120 (0x1fb8) Flags: NORMAL *** End of File Encountered. Last Block Read: 0 *** [enterprisedb@edb12 ~]$ [enterprisedb@edb12 ~]$ pg_filedump -D varchar $PGDATA/base/16224/33131 ******************************************************************* * PostgreSQL File/Block Formatted Dump Utility * * File: /pgdata/as12/data/base/16224/33131 * Options used: -D varchar ******************************************************************* Block 0 ******************************************************** <Header> ----- Block Offset: 0x00000000 Offsets: Lower 32 (0x0020) Block: Size 8192 Version 4 Upper 8120 (0x1fb8) LSN: logid 2 recoff 0x30018270 Special 8192 (0x2000) Items: 2 Free Space: 8088 Checksum: 0x0000 Prune XID: 0x00000000 Flags: 0x0000 () Length (including item array): 32 <Data> ----- Item 1 -- Length: 30 Offset: 8160 (0x1fe0) Flags: NORMAL COPY: 12345 Item 2 -- Length: 40 Offset: 8120 (0x1fb8) Flags: NORMAL COPY: 一二三四五 *** End of File Encountered. Last Block Read: 0 *** [enterprisedb@edb12 ~]$
順利成功～也再次確認 EDB 企業版是貨真價實的 PGSQL～
這工具的使用情境，例如像是查詢遇到 data file 有 checksum error 時（訊息類似 ERROR: invalid page in block ????? of relation ?????），就可以嘗試去看看是哪邊壞掉。不過這種情況也很罕見就是了～
參考資料
Pg filedump - PostgreSQL wiki
df7cb/pg_filedump: pg_filedump provides facilities for low-level examination of PostgreSQL tables and indexes
pg_filedump - pgPedia - a PostgreSQL Encyclopedia
Utilities Developed for PostgreSQL - Red Hat Edition
PostgreSQL 数据文件灾难恢复 - 解析与数据pg_filedump - blog/20170310_03.md at master · digoal/blog
Displaying the contents of a PostgreSQL data file with pg_filedump - Blog dbi services
Restore a PostgreSQL-9.6 database from an old complete dump and/or a up-to-date just base directory and other rescued files - Stack Overflow
pg_filedump 之二 -S 8192 -i -f_一名数据库爱好者的专栏-CSDN博客
Another new feature of pg_filedump: restoring the PostgreSQL directory / Sudo Null IT News
PG Day'17 Russia How data is stored in PostgreSQL and how to recover it using pg_filedump
Physical recovery with pg_filedump - Alexey Chernyshov's blog
How to install postgresql-filedump ubuntu package on Ubuntu 18.04/Ubuntu 19.04/Ubuntu 16.04
Архив рассылок [pgsql-patches] : Компания Postgres Professional
pg_filedump - PostgreSQL heap/index/control file dumper
沒有留言:
張貼留言