PostgreSQL解析wal日志之—walminer

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:PostgreSQL解析wal日志之—walminer

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

在oracle数据库中可以通过logminer实现对归档日志的解析,从而分析执行sql语句和undo sql,可以实现某些情况下数据库一些操作的定位(比如日志突然增加,数据突然丢失)以及一些故障的恢复(比如需要把update/delete执行的数据找回)等。在PostgreSQL数据库中walminer可以实现该需求,对pg的预写式日志(wal)的解析,具体见官网:https://gitee.com/movead/XLogMiner/
walminer安装

[postgres@localhost tmp]$ ls -l walminer_x86_64_centos_v4.6.0.tar.gz
-rw-r--r--. 1 root root 3866437 Apr 18 10:08 walminer_x86_64_centos_v4.6.0.tar.gz
[postgres@localhost tmp]$ tar xzvf walminer_x86_64_centos_v4.6.0.tar.gz 
walminer_x86_64_centos_v4.6.0/
walminer_x86_64_centos_v4.6.0/bin/
walminer_x86_64_centos_v4.6.0/bin/walminer
walminer_x86_64_centos_v4.6.0/lib/
walminer_x86_64_centos_v4.6.0/lib/libpq.so.5.15
walminer_x86_64_centos_v4.6.0/lib/libpq.so.5
walminer_x86_64_centos_v4.6.0/lib/libpq.so
walminer_x86_64_centos_v4.6.0/share/
…………
[root@localhost ~]# mkdir -p /usr/local/walminer/
[root@localhost ~]# chown postgres:postgres  /usr/local/walminer/
[root@localhost ~]# cp /tmp/walminer/walminer.license /usr/local/walminer/
[postgres@localhost bin]$ cd /tmp/walminer
[postgres@localhost walminer]$ cp -rp * /usr/local/walminer/
[postgres@localhost bin]$ export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/walminer/
[postgres@localhost bin]$ cd /usr/local/walminer/bin
[postgres@localhost bin]$ ./walminer  help
walminer [command] [options]
COMMANDS
---------
#wal2sql
  options
    -D dic file for miner
    -a out detail info for catalog change
    -w wal file path to miner
    -t dest of miner result(1 stdout, 2 file, 3 db)(stdout default)
    -k boundary kind(1 all, 2 lsn, 3 time, 4 xid)(all default)
    -m miner mode(0 nomal miner, 1 accurate miner)(nomal default) if k=2
    -r the relname for single table miner 
    -s start location if k=2 or k=3, or xid if k = 4 
          if k=2 default the min lsn of input wals   
          if k=3 or k=4 you need input this
    -e end wal location if k=2 or k=3
          if k=2 default the max lsn of input wals   
          if k=3 you need input this
    -f file to store miner result if t = 2
    -d target database name if t=3(default postgres)
    -h target database host if t=3(default localhost)
    -p target database port if t=3(default 5432)
    -u target database user if t=3(default postgres)
    -W target user password if t=3
---------
#builtdic
  options
    -d target database name for connect(default postgres)
    -h target database host(default localhost)
    -p target database port(default 5432)
    -u target database user(default postgres)
    -W target user password
    -D dic produce path
    -f rewrite walminer dic if exists
    -s only database pointed by -d
---------
#showdic
  options
    -D dic file to show
---------
#avatardic
  options
    -r avatar rel that new created
    -n avatared relfilenode
    -D avatared walminer dic path
    -b target database name which contain rel pointed by -r
---------
#regress(not support for user)
  options
    -w test database wal path(default postgres)
    -d test database name(default postgres)
    -h test database host(default localhost)
    -p test database port(default 5432)
    -u test database user(default postgres)
    -P apply database port 
    -W test user password
---------
#fosync
  options
    -D dic file for miner
    -w wal file path to miner
    -t dest of miner result(1 stdout, 2 file, 3 db, 4 apply)(stdout default)
    -f file to store miner result if t = 2
    -l lsn it start fync
    -d target database name if t=3 or 4(default postgres)
    -h target database host if t=3 or 4(default localhost)
    -p target database port if t=3 or 4(default 5432)
    -u target database user if t=3 or 4(default postgres)
    -W target user password if t=3 or 4
---------
#pgto
  options
    -c configure path
    -i to init a CDC configure
    -r to run a CDC configure
    Below is needed when -i
    -d source database name(default postgres)
    -h source database host(default localhost)
    -p source database port(default 5432)
    -u source database user(default postgres)
    -w source user password
    -D target database name
    -H target database host
    -P target database port
    -U target database user
    -W target user password
    -K target database type(1 postgres) (support postgres only currently)
    -s slot name need for CDC
---------
#waldump
  options
    -D dic file for miner
    -w wal file path to dump
    -t dest of miner result(1 stdout, 2 file)(stdout default)
    -s start lsn to dump
    -e end lsn to dump
    -f file to store miner result if t = 2
    -v verbose
---------
#################################################

[postgres@localhost bin]$ 

postgresql创建测试表和插入数据

[postgres@localhost ~]$ psql
psql (16.2)
Type "help" for help.

postgres=# select now();
              now              
-------------------------------
 2024-04-25 10:48:00.602067-04
(1 row)

postgres=# 
postgres=# create table t_walminer(id int,name varchar(100));
CREATE TABLE
postgres=# insert into t_walminer values(1,'www.xifenfei.com');
INSERT 0 1
postgres=# insert into t_walminer values(2,'www.orasos.com');
INSERT 0 1
postgres=# insert into t_walminer values(3,'xifenfei');
INSERT 0 1
postgres=# select * from t_walminer;
 id |       name       
----+------------------
  1 | www.xifenfei.com
  2 | www.orasos.com
  3 | xifenfei
(3 rows)

postgres=# select now();
              now              
-------------------------------
 2024-04-25 10:49:47.036881-04
(1 row)
postgres=# select pg_switch_wal();
 pg_switch_wal 
---------------
 0/D5023E8
(1 row)

walminer 生成字典

[postgres@localhost bin]$ ./walminer builtdic -D /usr/local/walminer/xifenfei.dic
#################################################
Walminer for PostgreSQL wal
Contact Author by mail 'lchch1990@sina.cn'
Persional License for posgress
#################################################
DIC INFO#
sysid:7357852038421105818 timeline:1 dbversion:160002 walminer:4.6

walminer解析这个时间段wal操作

[postgres@localhost bin]$  ./walminer wal2sql -D /usr/local/walminer/xifenfei.dic -w /pg/database/data/pg_arch \
 -k 3 -s 2024-04-24 -e 2024-04-26
#################################################
Walminer for PostgreSQL wal
Contact Author by mail 'lchch1990@sina.cn'
Vip License for posgress
#################################################
Switch wal to /pg/database/data/pg_arch/000000010000000000000001 on time 2024-04-25 23:27:07.42721+08
Switch wal to /pg/database/data/pg_arch/000000010000000000000002 on time 2024-04-25 23:27:07.45369+08
Switch wal to /pg/database/data/pg_arch/000000010000000000000003 on time 2024-04-25 23:27:07.453891+08
Switch wal to /pg/database/data/pg_arch/000000010000000000000004 on time 2024-04-25 23:27:07.486403+08
Switch wal to /pg/database/data/pg_arch/000000010000000000000005 on time 2024-04-25 23:27:07.513144+08
Switch wal to /pg/database/data/pg_arch/000000010000000000000006 on time 2024-04-25 23:27:07.538212+08
Switch wal to /pg/database/data/pg_arch/000000010000000000000007 on time 2024-04-25 23:27:07.561455+08
Switch wal to /pg/database/data/pg_arch/000000010000000000000008 on time 2024-04-25 23:27:07.584488+08
Switch wal to /pg/database/data/pg_arch/000000010000000000000009 on time 2024-04-25 23:27:07.606598+08
Switch wal to /pg/database/data/pg_arch/00000001000000000000000A on time 2024-04-25 23:27:07.609195+08
Switch wal to /pg/database/data/pg_arch/00000001000000000000000B on time 2024-04-25 23:27:07.609344+08
Switch wal to /pg/database/data/pg_arch/00000001000000000000000C on time 2024-04-25 23:27:07.609364+08
Switch wal to /pg/database/data/pg_arch/00000001000000000000000D on time 2024-04-25 23:27:07.66233+08
Switch wal to /pg/database/data/pg_arch/00000001000000000000000E on time 2024-04-25 23:27:07.684666+08
Switch wal to /pg/database/data/pg_arch/00000001000000000000000F on time 2024-04-25 23:27:07.684877+08
Switch wal to /pg/database/data/pg_arch/000000010000000000000001 on time 2024-04-25 23:27:07.684899+08
Get start lsn 0/d4eb380 for time range
Switch wal to /pg/database/data/pg_arch/00000001000000000000000D on time 2024-04-25 23:27:07.694947+08
[XID]=425507, [TOPXID]=0
[SQLNO]=1
[SQL]=INSERT INTO public.t_walminer(id ,name) VALUES(1 ,'www.xifenfei.com')
[UNDO]=DELETE FROM public.t_walminer WHERE id=1 AND name='www.xifenfei.com'
[database]=postgres
[COMPLETE]=true
[LSN]=0/d5021c8
[COMMITLSN]=0/d502218
[COMMITTIME]=2024-04-25 22:48:55.775279+08
------------------------------------------------------
[XID]=425508, [TOPXID]=0
[SQLNO]=1
[SQL]=INSERT INTO public.t_walminer(id ,name) VALUES(2 ,'www.orasos.com')
[UNDO]=DELETE FROM public.t_walminer WHERE id=2 AND name='www.orasos.com'
[database]=postgres
[COMPLETE]=true
[LSN]=0/d502278
[COMMITLSN]=0/d5022c8
[COMMITTIME]=2024-04-25 22:49:10.769752+08
------------------------------------------------------
[XID]=425509, [TOPXID]=0
[SQLNO]=1
[SQL]=INSERT INTO public.t_walminer(id ,name) VALUES(3 ,'xifenfei')
[UNDO]=DELETE FROM public.t_walminer WHERE id=3 AND name='xifenfei'
[database]=postgres
[COMPLETE]=true
[LSN]=0/d502328
[COMMITLSN]=0/d502370
[COMMITTIME]=2024-04-25 22:49:23.382642+08
------------------------------------------------------
Switch wal to /pg/database/data/pg_arch/00000001000000000000000E on time 2024-04-25 23:27:07.696041+08
Switch wal to /pg/database/data/pg_arch/00000001000000000000000F on time 2024-04-25 23:27:07.696062+08
[postgres@localhost bin]$ 

通过上述测试证明walminer可以非常好的解析pg的wal日志

PostgreSQL恢复系列:pg_filedump批量处理

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:PostgreSQL恢复系列:pg_filedump批量处理

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

pg_filedump工具使用起来比较麻烦,主要存在问题:
1. 需要人工一个个枚举各个列类型无法实现批量恢复,参考以前写的PostgreSQL恢复系列:pg_filedump基本使用
2. 特别是在pg库无法正常运行的情况下,如果没有业务提供表创建语句,恢复基本上无法正常进行.
基于这两个问题,在以前的文章中写过PostgreSQL恢复系列:pg_filedump恢复字典构造,为了解决上述的两个,弄了一个pg_filedump_batch脚本实现批量恢复需求

在测试的pg库中创建了一些测试表,并查看部分表数据,便于对比后续恢复效果

postgres=# \d
             List of relations
 Schema |      Name      | Type  |  Owner   
--------+----------------+-------+----------
 public | t_tbs          | table | postgres
 public | t_xff          | table | postgres
 public | t_xff2         | table | postgres
 public | t_xff3         | table | postgres
 public | t_xff4         | table | postgres
 public | t_xifenfei     | table | postgres
 public | tab_attribute  | table | postgres
 public | tab_class      | table | postgres
 public | tab_database   | table | postgres
 public | tab_namespace  | table | postgres
 public | tab_tablespace | table | postgres
 public | tab_type       | table | postgres
(12 rows)

postgres=# select * from tab_database;
  oid  |   datname   | datdba | encoding | datcollate  |  datctype   | datistemplate | datallowconn | datconnlimit | datlastsysoi
d | datfrozenxid | datminmxid | dattablespace 
-------+-------------+--------+----------+-------------+-------------+---------------+--------------+--------------+-------------
--+--------------+------------+---------------
 14187 | postgres    |     10 |        6 | en_US.UTF-8 | en_US.UTF-8 | f             | t            |           -1 |         1418
6 |          479 |          1 |          1663
 16403 | db_xff      |     10 |        6 | en_US.UTF-8 | en_US.UTF-8 | f             | t            |           -1 |         1418
6 |          479 |          1 |          1663
     1 | template1   |     10 |        6 | en_US.UTF-8 | en_US.UTF-8 | t             | t            |           -1 |         1418
6 |          479 |          1 |          1663
 14186 | template0   |     10 |        6 | en_US.UTF-8 | en_US.UTF-8 | t             | f            |           -1 |         1418
6 |          479 |          1 |          1663
 16407 | db_xifenfei |  16405 |        6 | en_US.UTF-8 | en_US.UTF-8 | f             | t            |           -1 |         1418
6 |          479 |          1 |         16406
(5 rows)
postgres=# select count(1) from tab_class;
 count 
-------
   407
(1 row)

postgres=# select *from pg_tablespace;
  oid  |   spcname    | spcowner | spcacl | spcoptions 
-------+--------------+----------+--------+------------
  1663 | pg_default   |       10 |        | 
  1664 | pg_global    |       10 |        | 
 16406 | tbs_xifenfei |    16405 |        | 
(3 rows)

使用pg_filedump_bath脚本来实现批量恢复

[root@xifenfei tmp]# ./pg_filedump_batch recover --database-oid=14187  \
 --output-directory=/data/recovery --pgdata=/var/lib/pgsql/12/data
Recover tables in database with oid: 14187
LOG: starting to process table tab_attribute
LOG: starting to process table tab_class
LOG: starting to process table tab_database
LOG: starting to process table tab_namespace
LOG: starting to process table tab_tablespace
LOG: starting to process table tab_type
LOG: starting to process table t_tbs
LOG: starting to process table t_xff
LOG: starting to process table t_xff2
LOG: starting to process table t_xff3
LOG: starting to process table t_xff4
LOG: starting to process table t_xifenfei
Check dumps in /data/recovery

参考数据恢复

[root@xifenfei tmp]# cd /data/recovery/
[root@xifenfei recovery]# ls -ltr
total 156
-rw-r--r-- 1 root root 82797 Apr 18 20:35 recovered-14187-tab_attribute.csv
-rw-r--r-- 1 root root 31129 Apr 18 20:35 recovered-14187-tab_class.csv
-rw-r--r-- 1 root root   343 Apr 18 20:35 recovered-14187-tab_database.csv
-rw-r--r-- 1 root root   118 Apr 18 20:35 recovered-14187-tab_namespace.csv
-rw-r--r-- 1 root root    50 Apr 18 20:35 recovered-14187-tab_tablespace.csv
-rw-r--r-- 1 root root  7907 Apr 18 20:35 recovered-14187-tab_type.csv
-rw-r--r-- 1 root root     0 Apr 18 20:35 recovered-14187-t_tbs.csv
-rw-r--r-- 1 root root    38 Apr 18 20:35 recovered-14187-t_xff.csv
-rw-r--r-- 1 root root    38 Apr 18 20:35 recovered-14187-t_xff2.csv
-rw-r--r-- 1 root root    38 Apr 18 20:35 recovered-14187-t_xff3.csv
-rw-r--r-- 1 root root    38 Apr 18 20:35 recovered-14187-t_xff4.csv
-rw-r--r-- 1 root root    38 Apr 18 20:35 recovered-14187-t_xifenfei.csv
[root@xifenfei recovery]# cat recovered-14187-tab_database.csv
14187   postgres        10      6       en_US.UTF-8     en_US.UTF-8     f       t       -1      14186   479     1       1663
16403   db_xff  10      6       en_US.UTF-8     en_US.UTF-8     f       t       -1      14186   479     1       1663
1       template1       10      6       en_US.UTF-8     en_US.UTF-8     t       t       -1      14186   479     1       1663
14186   template0       10      6       en_US.UTF-8     en_US.UTF-8     t       f       -1      14186   479     1       1663
16407   db_xifenfei     16405   6       en_US.UTF-8     en_US.UTF-8     f       t       -1      14186   479     1       16406
[root@xifenfei recovery]# cat recovered-14187-tab_class.csv|wc -l
407
[root@xifenfei recovery]# cat recovered-14187-tab_tablespace.csv
1663    pg_default
1664    pg_global
16406   tbs_xifenfei

把pg_class恢复数据导入库中进行对比,证明恢复的数据完全正确

postgres=# COPY tab_class_new FROM '/data/recovery/recovered-14187-tab_class.csv';
COPY 407

postgres=# select count(1) from tab_class;
 count 
-------
   407
(1 row)

 count 
-------
   407
(1 row)

postgres=# select count(1) from tab_class_new;
 count 
-------
   407
(1 row)

postgres=# select * from tab_class_new 
postgres-# EXCEPT
postgres-# select * from tab_class;
 oid | relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | rel
allvisible | reltoastrelid | relhasindex | relisshared | relpersistence | relkind 
-----+---------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+----
-----------+---------------+-------------+-------------+----------------+---------
(0 rows)

postgres=# select * from tab_class
postgres-# EXCEPT
postgres-# select * from tab_class_new;
 oid | relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | rel
allvisible | reltoastrelid | relhasindex | relisshared | relpersistence | relkind 
-----+---------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+----
-----------+---------------+-------------+-------------+----------------+---------
(0 rows)

通过上述操作证明:
1. 在没有人工列出列类型的情况下实现批量pg_filedump恢复功能
2. 在pg库没有启动的情况下直接解析字典实现恢复功能
3. 实现pg数据库的批量恢复
如果有PostgreSQL的数据库故障,自行无法解决,请联系我们提供专业数据库恢复技术支持:
电话/微信:17813235971    Q Q:107644445QQ咨询惜分飞    E-Mail:dba@xifenfei.com

PostgreSQL恢复系列:pg_filedump恢复字典构造

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:PostgreSQL恢复系列:pg_filedump恢复字典构造

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

pg_filedump是在pg数据库极端情况下直接解析数据库文件的利器,但是由于是开源软件,本身难以实现批量处理,通过对底层基表分析,可以实现批量处理功能
分析PostgreSQL库中数据库信息

--数据库查询结果
postgres=# select oid,datname,datdba,dattablespace from pg_database;
  oid  |   datname   | datdba | dattablespace 
-------+-------------+--------+---------------
 14187 | postgres    |     10 |          1663
 16403 | db_xff      |     10 |          1663
     1 | template1   |     10 |          1663
 14186 | template0   |     10 |          1663
 16407 | db_xifenfei |  16405 |         16406
(5 rows)

--通过dump 该文件解析数据
<Data> -----
 Item   1 -- Length:    0  Offset:    5 (0x0005)  Flags: REDIRECT
 Item   2 -- Length:    0  Offset:    6 (0x0006)  Flags: REDIRECT
 Item   3 -- Length:  260  Offset: 7320 (0x1c98)  Flags: NORMAL
COPY: 14187     postgres
 Item   4 -- Length:  260  Offset: 7056 (0x1b90)  Flags: NORMAL
COPY: 16403     db_xff
 Item   5 -- Length:  297  Offset: 7888 (0x1ed0)  Flags: NORMAL
COPY: 1 template1
 Item   6 -- Length:  297  Offset: 7584 (0x1da0)  Flags: NORMAL
COPY: 14186     template0
 Item   7 -- Length:  260  Offset: 6792 (0x1a88)  Flags: NORMAL
COPY: 16407     db_xifenfei

分析PostgreSQL 表空间信息

--sql查询表空间信息
postgres=# select * from pg_tablespace;
  oid  |   spcname    | spcowner | spcacl | spcoptions 
-------+--------------+----------+--------+------------
  1663 | pg_default   |       10 |        | 
  1664 | pg_global    |       10 |        | 
 16406 | tbs_xifenfei |    16405 |        | 
(3 rows)


--通过dump 该文件解析数据
<Data> -----
 Item   1 -- Length:   96  Offset: 8096 (0x1fa0)  Flags: NORMAL
COPY: 1663      pg_default
 Item   2 -- Length:   96  Offset: 8000 (0x1f40)  Flags: NORMAL
COPY: 1664      pg_global
 Item   3 -- Length:   96  Offset: 7904 (0x1ee0)  Flags: NORMAL
COPY: 16406     tbs_xifenfei

分析PostgreSQL 对象id、name、path对应关系

--对象信息查询
postgres=# select oid ,relname,relnamespace,reltype,reloftype,relowner,relam,relfilenode,
           reltablespace from pg_class where relname like 't_t%' or relname like 't_x%';
  oid  |  relname   | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace 
-------+------------+--------------+---------+-----------+----------+-------+-------------+---------------
 16387 | t_xifenfei |         2200 |   16389 |         0 |       10 |     2 |       16390 |             0
 16391 | t_xff      |         2200 |   16393 |         0 |       10 |     2 |       16391 |             0
 16394 | t_xff2     |         2200 |   16396 |         0 |       10 |     2 |       16394 |             0
 16397 | t_xff3     |         2200 |   16399 |         0 |       10 |     2 |       16397 |             0
 16400 | t_xff4     |         2200 |   16402 |         0 |       10 |     2 |       16400 |             0
 16408 | t_tbs      |         2200 |   16410 |         0 |       10 |     2 |       16408 |         16406
(6 rows)

--通过dump 该文件解析数据(显示部分)
COPY: 16394     t_xff2  2200    16396   0       10      2       16394   0       0       0.000000000000  0       0       f       f       p       r
 Item  29 -- Length:    0  Offset:   31 (0x001f)  Flags: REDIRECT
 Item  30 -- Length:  172  Offset: 2592 (0x0a20)  Flags: NORMAL
COPY: 16397     t_xff3  2200    16399   0       10      2       16397   0       0       0.000000000000  0       0       f       f       p       r
 Item  31 -- Length:  205  Offset: 3376 (0x0d30)  Flags: NORMAL
COPY: 12093     pg_shadow       11      12094   0       10      0       0       0       0       0.000000000000  0       0       f       f       p       v
 Item  32 -- Length:  172  Offset: 2416 (0x0970)  Flags: NORMAL
COPY: 16400     t_xff4  2200    16402   0       10      2       16400   0       0       0.000000000000  0       0       f       f       p       r

这个里面获取到pg_class.reltablespace是表空间的id值,根据自定义表空间的规则:在pgdata/pg_tblspc创建link指向创建表空间时候的文件夹路径

-bash-4.2$ pwd
/var/lib/pgsql/12/data/pg_tblspc
-bash-4.2$ ls -ltr
total 0
lrwxrwxrwx 1 postgres postgres 30 Apr 15 20:13 16406 -> /var/lib/pgsql/12/data/tbs_xff

结合上述的pg_database,pg_tablespace,pg_class信息,可以获取到每个表对应实际的存储路径
分析PostgreSQL 模式信息

--sql查询模式信息
postgres=# select * from pg_namespace;
  oid  |      nspname       | nspowner |               nspacl                
-------+--------------------+----------+-------------------------------------
    99 | pg_toast           |       10 | 
 12314 | pg_temp_1          |       10 | 
 12315 | pg_toast_temp_1    |       10 | 
    11 | pg_catalog         |       10 | {postgres=UC/postgres,=U/postgres}
  2200 | public             |       10 | {postgres=UC/postgres,=UC/postgres}
 13887 | information_schema |       10 | {postgres=UC/postgres,=U/postgres}
 16404 | u_xifenfei         |       10 | 
(7 rows)


--通过dump 该文件解析数据
<Data> -----
 Item   1 -- Length:    0  Offset:    6 (0x0006)  Flags: REDIRECT
 Item   2 -- Length:   96  Offset: 8096 (0x1fa0)  Flags: NORMAL
COPY: 99        pg_toast
 Item   3 -- Length:    0  Offset:    7 (0x0007)  Flags: REDIRECT
 Item   4 -- Length:   96  Offset: 8000 (0x1f40)  Flags: NORMAL
COPY: 12314     pg_temp_1
 Item   5 -- Length:   96  Offset: 7904 (0x1ee0)  Flags: NORMAL
COPY: 12315     pg_toast_temp_1
 Item   6 -- Length:  141  Offset: 7760 (0x1e50)  Flags: NORMAL
COPY: 11        pg_catalog
 Item   7 -- Length:  141  Offset: 7616 (0x1dc0)  Flags: NORMAL
COPY: 2200      public
 Item   8 -- Length:    0  Offset:    9 (0x0009)  Flags: REDIRECT
 Item   9 -- Length:  141  Offset: 7472 (0x1d30)  Flags: NORMAL
COPY: 13887     information_schema
 Item  10 -- Length:   96  Offset: 7376 (0x1cd0)  Flags: NORMAL
COPY: 16404     u_xifenfei

通过pg_namespace,pg_class信息,可以获取到对象所属的模式关系,基于上述汇总,可以获取到某个模式下面,所有表id和实际存储路径,现在使用pg_filedump进行恢复,还缺少表的列类型信息,通过pg_type和pg_attribute来获取。

获取PostgreSQL表的列名称和类型[编号]信息

--sql查询列信息
postgres=# \d t_tbs
               Table "public.t_tbs"
  Column  | Type | Collation | Nullable | Default 
----------+------+-----------+----------+---------
 oid      | oid  |           |          | 
 spcname  | name |           |          | 
 spcowner | oid  |           |          | 
Tablespace: "tbs_xifenfei"

postgres=# select attrelid,attname,atttypid,attstattarget,attlen,attnum from pg_attribute where attrelid=16408;
 attrelid | attname  | atttypid | attstattarget | attlen | attnum 
----------+----------+----------+---------------+--------+--------
    16408 | tableoid |       26 |             0 |      4 |     -6
    16408 | cmax     |       29 |             0 |      4 |     -5
    16408 | xmax     |       28 |             0 |      4 |     -4
    16408 | cmin     |       29 |             0 |      4 |     -3
    16408 | xmin     |       28 |             0 |      4 |     -2
    16408 | ctid     |       27 |             0 |      6 |     -1
    16408 | oid      |       26 |            -1 |      4 |      1
    16408 | spcname  |       19 |            -1 |     64 |      2
    16408 | spcowner |       26 |            -1 |      4 |      3
(9 rows)

--dump 内容(截取部分)
 Item  11 -- Length:  144  Offset: 1424 (0x0590)  Flags: NORMAL
COPY: 16408     oid     26      -1      4       1
 Item  12 -- Length:  144  Offset: 1280 (0x0500)  Flags: NORMAL
COPY: 16408     spcname 19      -1      64      2
 Item  13 -- Length:  144  Offset: 1136 (0x0470)  Flags: NORMAL
COPY: 16408     spcowner        26      -1      4       3
 Item  14 -- Length:  144  Offset:  992 (0x03e0)  Flags: NORMAL
COPY: 16408     ctid    27      0       6       -1
 Item  15 -- Length:  144  Offset:  848 (0x0350)  Flags: NORMAL
COPY: 16408     xmin    28      0       4       -2
 Item  16 -- Length:  144  Offset:  704 (0x02c0)  Flags: NORMAL
COPY: 16408     cmin    29      0       4       -3
 Item  17 -- Length:  144  Offset:  560 (0x0230)  Flags: NORMAL
COPY: 16408     xmax    28      0       4       -4
 Item  18 -- Length:  144  Offset:  416 (0x01a0)  Flags: NORMAL
COPY: 16408     cmax    29      0       4       -5

PostgreSQL获取类型编号和实际类型名称对应关系

--查询类型编号和实际类型关系
postgres=# select oid,typname from pg_type;
  oid  |                typname                
-------+---------------------------------------
    16 | bool
    17 | bytea
    18 | char
    19 | name
    20 | int8
    21 | int2
    22 | int2vector
    23 | int4
    24 | regproc
    25 | text
    26 | oid
    27 | tid
    28 | xid
    29 | cid
……

--dump 内容(截取部分)
 Item   1 -- Length:  176  Offset: 8016 (0x1f50)  Flags: NORMAL
COPY: 16        bool
 Item   2 -- Length:  176  Offset: 7840 (0x1ea0)  Flags: NORMAL
COPY: 17        bytea
 Item   3 -- Length:  176  Offset: 7664 (0x1df0)  Flags: NORMAL
COPY: 18        char
 Item   4 -- Length:  176  Offset: 7488 (0x1d40)  Flags: NORMAL
COPY: 19        name
 Item   5 -- Length:  176  Offset: 7312 (0x1c90)  Flags: NORMAL
COPY: 20        int8
 Item   6 -- Length:  176  Offset: 7136 (0x1be0)  Flags: NORMAL
COPY: 21        int2
 Item   7 -- Length:  176  Offset: 6960 (0x1b30)  Flags: NORMAL
COPY: 22        int2vector
 Item   8 -- Length:  176  Offset: 6784 (0x1a80)  Flags: NORMAL
COPY: 23        int4
 Item   9 -- Length:  176  Offset: 6608 (0x19d0)  Flags: NORMAL
COPY: 24        regproc
 Item  10 -- Length:  176  Offset: 6432 (0x1920)  Flags: NORMAL
COPY: 25        text
 Item  11 -- Length:  176  Offset: 6256 (0x1870)  Flags: NORMAL
COPY: 26        oid
 Item  12 -- Length:  176  Offset: 6080 (0x17c0)  Flags: NORMAL
COPY: 27        tid
 Item  13 -- Length:  176  Offset: 5904 (0x1710)  Flags: NORMAL
COPY: 28        xid
 Item  14 -- Length:  176  Offset: 5728 (0x1660)  Flags: NORMAL
COPY: 29        cid
 Item  15 -- Length:  176  Offset: 5552 (0x15b0)  Flags: NORMAL
COPY: 30        oidvector

通过pg_class、pg_type和pg_attribute可以获取对象的表的列名称,数据类型等信息。通过以上几个对象,即可获取到pg_filedmp处理所需要的所有信息,然后可以实现批量处理

PostgreSQL恢复系列:pg_filedump基本使用

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:PostgreSQL恢复系列:pg_filedump基本使用

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

当PostgreSQL遇到重大故障,使用各种方法都无法直接启动数据库,可以考虑使用类似oracle dul工具,直接离线方式读取文件进行恢复.这个工具为pg_filedump
pg_filedump安装

[root@xifenfei ~]# yum install pg_filedump_14.x86_64
Loaded plugins: langpacks, ulninfo
Resolving Dependencies
--> Running transaction check
---> Package pg_filedump_14.x86_64 0:14.1-1.rhel7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

======================================================================================================================
 Package                            Arch      Version                 Repository                                 Size
======================================================================================================================
Installing:                                 
 pg_filedump_14                     x86_64    14.1-1.rhel7            pgdg14                                     43 k
                                            
Transaction Summary                         
======================================================================================================================
Install  1 Package

Total download size: 43 k
Installed size: 81 k
Is this ok [y/d/N]: y
Downloading packages:
pg_filedump_14-14.1-1.rhel7.x86_64.rpm                                              |  43 kB  00:00:02     
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : pg_filedump_14-14.1-1.rhel7.x86_64                                                      1/1 
  Verifying  : pg_filedump_14-14.1-1.rhel7.x86_64                                                      1/1 

Installed:
  pg_filedump_14.x86_64 0:14.1-1.rhel7                                                                     

Complete!
-bash-4.2$ pg_filedump 

Version 14.1 (for PostgreSQL 8.x .. 14.x)
Copyright (c) 2002-2010 Red Hat, Inc.
Copyright (c) 2011-2022, PostgreSQL Global Development Group

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 numeric oid real serial smallint smallserial text
        time timestamp timestamptz 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]
Additional functions:
  -m  Interpret file as pg_filenode.map file and print contents (all
      other options will be ignored)

Report bugs to <pgsql-bugs@postgresql.org>

创建测试表

-bash-4.2$ psql
psql (14.3)
Type "help" for help.

postgres=# create table pg_xifenfei(id int,name varchar(100));
CREATE TABLE
postgres=# insert into pg_xifenfei values(1,'www.xifenfei.com');
INSERT 0 1
postgres=# insert into pg_xifenfei values(2,'xienfei_pg_recovery');
INSERT 0 1
postgres=# select * from pg_xifenfei;
 id |        name         
----+---------------------
  1 | www.xifenfei.com
  2 | xienfei_pg_recovery
(2 rows)

postgres=# 

pg_filedump恢复数据

-bash-4.2$ pg_filedump /var/lib/pgsql/14/data/base/14487/16384

*******************************************************************
* PostgreSQL File/Block Formatted Dump Utility
*
* File: /var/lib/pgsql/14/data/base/14487/16384
* Options used: None
*******************************************************************

Block    0 ********************************************************
<Header> -----
 Block Offset: 0x00000000         Offsets: Lower      32 (0x0020)
 Block: Size 8192  Version    4            Upper    8096 (0x1fa0)
 LSN:  logid      0 recoff 0x16299cf0      Special  8192 (0x2000)
 Items:    2                      Free Space: 8064
 Checksum: 0x0000  Prune XID: 0x00000000  Flags: 0x0000 ()
 Length (including item array): 32

<Data> -----
 Item   1 -- Length:   45  Offset: 8144 (0x1fd0)  Flags: NORMAL
 Item   2 -- Length:   48  Offset: 8096 (0x1fa0)  Flags: NORMAL


*** End of File Encountered. Last Block Read: 0 ***

-bash-4.2$ pg_filedump -D int,charn /var/lib/pgsql/14/data/base/14487/16384|grep COPY
COPY: 1 www.xifenfei.com
COPY: 2 xienfei_pg_recovery
-bash-4.2$ pg_filedump -D int,charn /var/lib/pgsql/14/data/base/14487/16384|grep COPY
> |awk '{$1=null;print $0}'>/tmp/pg_xifenfei_rec
-bash-4.2$ sed -i 's/^[ ]*//g' /tmp/pg_xifenfei_rec

导入数据验证

postgres=# truncate table pg_xifenfei;
TRUNCATE TABLE
postgres=# select * from pg_xifenfei;
 id | name 
----+------
(0 rows)
postgres=# copy pg_xifenfei from '/tmp/pg_xifenfei_rec'(DELIMITER ' ');
COPY 2
postgres=# select * from pg_xifenfei;
 id |        name         
----+---------------------
  1 | www.xifenfei.com
  2 | xienfei_pg_recovery
(2 rows)

通过上述简单测试证明,在PG数据库出现极端情况下,可以使用该方法进行最后的数据恢复,减少因为数据丢失带来的损失.

PostgreSQL恢复系列:pg_control异常恢复

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:PostgreSQL恢复系列:pg_control异常恢复

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

在PG中pg_control文件类似oracle数据库的control文件(控制文件),在Oracle中如果该文件丢失/损坏,可以通过alter database create controlfile命令进行创建,对于PG数据库来说也可以通过pg_resetwal命令来实现创建,由于pg_control文件损坏,需要人工指定一些参数完成pg_resetwal相关操作
pg_resetwal 使用说明

-bash-4.2$ pg_resetwal --help
pg_resetwal resets the PostgreSQL write-ahead log.

Usage:
  pg_resetwal [OPTION]... DATADIR

Options:
  -c, --commit-timestamp-ids=XID,XID
                                   set oldest and newest transactions bearing
                                   commit timestamp (zero means no change)
 [-D, --pgdata=]DATADIR            data directory
  -e, --epoch=XIDEPOCH             set next transaction ID epoch
  -f, --force                      force update to be done
  -l, --next-wal-file=WALFILE      set minimum starting location for new WAL
  -m, --multixact-ids=MXID,MXID    set next and oldest multitransaction ID
  -n, --dry-run                    no update, just show what would be done
  -o, --next-oid=OID               set next OID
  -O, --multixact-offset=OFFSET    set next multitransaction offset
  -u, --oldest-transaction-id=XID  set oldest transaction ID
  -V, --version                    output version information, then exit
  -x, --next-transaction-id=XID    set next transaction ID
      --wal-segsize=SIZE           size of WAL segments, in megabytes
  -?, --help                       show this help, then exit

Report bugs to <pgsql-bugs@lists.postgresql.org>.
PostgreSQL home page: <https://www.postgresql.org/>

确认现在业务表记录情况

-bash-4.2$ psql
psql (14.3)
Type "help" for help.

postgres=# select count(1) from ac_event;
 count  
--------
 246266
(1 row)

模拟pg_control文件异常

-bash-4.2$ ps -ef|grep postgres
postgres  37178      1  0 09:58 ?        00:00:00 /usr/pgsql-14/bin/postgres -D /var/lib/pgsql/14/data
postgres  37179  37178  0 09:58 ?        00:00:00 postgres: logger 
postgres  37181  37178  0 09:58 ?        00:00:00 postgres: checkpointer 
postgres  37182  37178  0 09:58 ?        00:00:00 postgres: background writer 
postgres  37183  37178  0 09:58 ?        00:00:00 postgres: walwriter 
postgres  37184  37178  0 09:58 ?        00:00:00 postgres: autovacuum launcher 
postgres  37185  37178  0 09:58 ?        00:00:00 postgres: stats collector 
postgres  37186  37178  0 09:58 ?        00:00:00 postgres: logical replication launcher 
root      41368  41314  0 11:06 pts/1    00:00:00 su - postgres
postgres  41369  41368  0 11:06 pts/1    00:00:00 -bash
postgres  45071  41369  0 12:07 pts/1    00:00:00 ps -ef
postgres  45072  41369  0 12:07 pts/1    00:00:00 grep --color=auto postgres
-bash-4.2$ kill -9 37178
-bash-4.2$ ps -ef|grep postgres
root      41368  41314  0 11:06 pts/1    00:00:00 su - postgres
postgres  41369  41368  0 11:06 pts/1    00:00:00 -bash
postgres  45095  41369  0 12:08 pts/1    00:00:00 ps -ef
postgres  45096  41369  0 12:08 pts/1    00:00:00 grep --color=auto postgres
-bash-4.2$ pwd
/var/lib/pgsql/14/data/global
-bash-4.2$ ls -l pg_control 
-rw-------. 1 postgres postgres 8192 May 30 12:04 pg_control
-bash-4.2$ rm -rf pg_control 
-bash-4.2$ ls -l pg_control 
ls: cannot access pg_control: No such file or directory

PG启动失败

-bash-4.2$ pg_ctl start 
pg_ctl: another server might be running; trying to start server anyway
waiting for server to start....postgres: could not find the database system
Expected to find it in the directory "/var/lib/pgsql/14/data",
but could not open file "/var/lib/pgsql/14/data/global/pg_control": No such file or directory
 stopped waiting
pg_ctl: could not start server
Examine the log output.

创建空pg_control文件启动依旧失败

-bash-4.2$ touch /var/lib/pgsql/14/data/global/pg_control
-bash-4.2$ pg_ctl start 
pg_ctl: another server might be running; trying to start server anyway
waiting for server to start....2022-05-30 12:09:43.953 CST [45215] PANIC:  
   could not read file "global/pg_control": read 0 of 296
 stopped waiting
pg_ctl: could not start server
Examine the log output.

设置next-wal-file
-l, –next-wal-file=WALFILE,这个参数设置下一个新的WAL文件的最小值,这个值可以从$PGDATA/pg_wal目录下去看最后一个WAL 文件,这个文件的id+1即可

-bash-4.2$ pwd
/var/lib/pgsql/14/data/pg_wal
-bash-4.2$ ls -l
total 16384
-rw-------. 1 postgres postgres 16777216 May 30 12:04 000000010000000000000014
drwx------. 2 postgres postgres        6 May 24 02:20 archive_status
-bash-4.2$ 

这个文件+1,-l 000000010000000000000015
设置next-transaction
-x, –next-transaction-id=XID,这个参数设置pg_control中的下一个XID的值,这个值可以从pg_xact目录下的文件中查询

-bash-4.2$ pwd
/var/lib/pgsql/14/data/pg_xact
-bash-4.2$ ls -ltr
total 8
-rw-------. 1 postgres postgres 8192 May 30 12:03 0000

最后一个是0000,那么下一个XID就是0001,然后乘以 1048576 (0×100000),实际上后面直接加5个0就行了。注意,这个值是16进制的。-x 0×000100000
multixact-ids设置
-m, –multixact-ids=MXID1,MXID2,这个参数包含两个部分,MXID1和MXID2,都可以从$PGDATA/pg_multixact/offsets目录下获得。MXID1的值,首先找到最大值,+1,再乘以 65536 (0×10000,相当于后面加4个0)作为这个参数的前半部分。找到最小的值,后面加4个0,作为MXID2的值

-bash-4.2$ pwd
/var/lib/pgsql/14/data/pg_multixact/offsets
-bash-4.2$ ls -ltr
total 8
-rw-------. 1 postgres postgres 8192 May 29 22:06 0000
-bash-4.2$ 

-m 0×00010000, 0×00000000(由于oldest multitransaction ID不能为0,因此后续这个值需要适当调整)
multixact-offset设置
-O, –multixact-offset=OFFSET,这个参数可以从$PGDATA/pg_multixact/members目录下获得。找到最大值,+1,乘以 52352 (0xCC80)

-bash-4.2$ pwd
/var/lib/pgsql/14/data/pg_multixact/members
-bash-4.2$ ls -ltr
total 8
-rw-------. 1 postgres postgres 8192 May 24 02:20 0000

-O 0xCC80
尝试执行pg_resetwal

-bash-4.2$ pg_resetwal -l 000000010000000000000015 -x 0x000100000 -m 0x00010000,0x00000000 -O 0xCC80 $PGDATA
pg_resetwal: error: oldest multitransaction ID (-m) must not be 0

multixact-ids值不对,进行调整后处理
postmaster.pid文件需要清理
由于PG库异常关闭,需要人工清理掉该文件

-bash-4.2$ pg_resetwal -l 000000010000000000000015 -x 0x000100000 -m 0x00020000,0x00010000 -O 0xCC80 $PGDATA
pg_resetwal: error: lock file "postmaster.pid" exists
-bash-4.2$ rm -rf postmaster.pid 

pg_resetwal结果预览

-bash-4.2$ pg_resetwal -l 000000010000000000000015 -x 0x000100000 -m 0x00020000,0x00010000 -O 0xCC80 $PGDATA
pg_resetwal: warning: pg_control exists but is broken or wrong version; ignoring it
Guessed pg_control values:

pg_control version number:            1300
Catalog version number:               202107181
Database system identifier:           7103392535324046312
Latest checkpoint's TimeLineID:       1
Latest checkpoint's full_page_writes: off
Latest checkpoint's NextXID:          0:3
Latest checkpoint's NextOID:          12000
Latest checkpoint's NextMultiXactId:  1
Latest checkpoint's NextMultiOffset:  0
Latest checkpoint's oldestXID:        3
Latest checkpoint's oldestXID's DB:   0
Latest checkpoint's oldestActiveXID:  0
Latest checkpoint's oldestMultiXid:   1
Latest checkpoint's oldestMulti's DB: 0
Latest checkpoint's oldestCommitTsXid:0
Latest checkpoint's newestCommitTsXid:0
Maximum data alignment:               8
Database block size:                  8192
Blocks per segment of large relation: 131072
WAL block size:                       8192
Bytes per WAL segment:                16777216
Maximum length of identifiers:        64
Maximum columns in an index:          32
Maximum size of a TOAST chunk:        1996
Size of a large-object chunk:         2048
Date/time type storage:               64-bit integers
Float8 argument passing:              by value
Data page checksum version:           0


Values to be changed:

First log segment after reset:        000000010000000000000015
NextMultiXactId:                      131072
OldestMultiXid:                       65536
OldestMulti's DB:                     0
NextMultiOffset:                      52352
NextXID:                              1048576
OldestXID:                            3
OldestXID's DB:                       0

If these values seem acceptable, use -f to force reset.

pg_resetwal进行创建pg_control并启动PG

-bash-4.2$ pg_resetwal -l 000000010000000000000015 -x 0x000100000 -m 0x00020000,0x00010000 -O 0xCC80 -f $PGDATA
pg_resetwal: warning: pg_control exists but is broken or wrong version; ignoring it
Write-ahead log reset
-bash-4.2$ pg_ctl start
waiting for server to start....2022-05-30 13:33:28.266 CST [51437] LOG:  
redirecting log output to logging collector process
2022-05-30 13:33:28.266 CST [51437] HINT:  Future log output will appear in directory "log".
 done
server started

验证数据

-bash-4.2$ psql
psql (14.3)
Type "help" for help.

postgres=#  select count(1) from ac_event;
 count  
--------
 245275
(1 row)

这种方法恢复之后,建议理解dump数据,然后导入到新库中

PostgreSQL恢复系列:wal日志丢失恢复

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:PostgreSQL恢复系列:wal日志丢失恢复

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

WAL是Write Ahead Log的简写,和oracle的redo日志类似,存放在$PGDATA/pg_xlog中,10版本以后在$PGDATA/pg_wal目录.在oracle数据库中,如果redo丢失,分为active/current和inactive的redo,分别有不同的处理方式,对于oracle需要实例恢复的redo丢失,需要屏蔽数据库一致性,强制打开数据库,对于PG数据库这部分日志丢失该如何恢复,主要是通过pg_resetwal/pg_resetxlog(10以前版本)命令来实现,这里通过一个测试来验证
创建测试表并强制kill数据库

-bash-4.2$ psql
psql (14.3)
Type "help" for help.

postgres=# create table t_xifenfei as select * from pg_database;
SELECT 4
postgres=# select count(1) from t_xifenfei;
 count 
-------
     4
(1 row)

postgres=# \q
-bash-4.2$ ps -ef|grep post
root       1819      1  0 May28 ?        00:00:00 /usr/libexec/postfix/master -w
postfix    1838   1819  0 May28 ?        00:00:00 qmgr -l -t unix -u
postgres  11102      1  0 05:49 ?        00:00:00 /usr/pgsql-14/bin/postgres -D /var/lib/pgsql/14/data
postgres  11103  11102  0 05:49 ?        00:00:00 postgres: logger 
postgres  11105  11102  0 05:49 ?        00:00:00 postgres: checkpointer 
postgres  11106  11102  0 05:49 ?        00:00:00 postgres: background writer 
postgres  11107  11102  0 05:49 ?        00:00:00 postgres: walwriter 
postgres  11108  11102  0 05:49 ?        00:00:00 postgres: autovacuum launcher 
postgres  11109  11102  0 05:49 ?        00:00:01 postgres: stats collector 
postgres  11110  11102  0 05:49 ?        00:00:00 postgres: logical replication launcher 
root      22743  22300  0 18:26 pts/3    00:00:00 su - postgres
postgres  22744  22743  0 18:26 pts/3    00:00:00 -bash
postgres  22937  22744  0 18:28 pts/3    00:00:00 psql
postgres  22938  11102  0 18:28 ?        00:00:00 postgres: postgres postgres [local] idle
postfix   32623   1819  0 21:10 ?        00:00:00 pickup -l -t unix -u
root      33032  32912  0 21:15 pts/2    00:00:00 su - postgres
postgres  33033  33032  0 21:15 pts/2    00:00:00 -bash
postgres  35210  33033  0 21:51 pts/2    00:00:00 ps -ef
postgres  35211  33033  0 21:51 pts/2    00:00:00 grep --color=auto post
-bash-4.2$ kill -9 11102

删除wal日志

-bash-4.2$ pwd
/var/lib/pgsql/14/data/pg_wal
-bash-4.2$ ls -ltr
total 311296
drwx------. 2 postgres postgres        6 May 24 02:20 archive_status
-rw-------. 1 postgres postgres 16777216 May 28 21:29 000000010000000000000014
-rw-------. 1 postgres postgres 16777216 May 28 21:29 000000010000000000000015
-rw-------. 1 postgres postgres 16777216 May 28 21:29 000000010000000000000016
-rw-------. 1 postgres postgres 16777216 May 28 21:29 000000010000000000000017
-rw-------. 1 postgres postgres 16777216 May 28 21:29 000000010000000000000018
-rw-------. 1 postgres postgres 16777216 May 28 21:29 000000010000000000000019
-rw-------. 1 postgres postgres 16777216 May 28 21:29 00000001000000000000001A
-rw-------. 1 postgres postgres 16777216 May 28 21:29 00000001000000000000001B
-rw-------. 1 postgres postgres 16777216 May 28 21:29 00000001000000000000001C
-rw-------. 1 postgres postgres 16777216 May 28 21:29 00000001000000000000001D
-rw-------. 1 postgres postgres 16777216 May 28 21:29 00000001000000000000001E
-rw-------. 1 postgres postgres 16777216 May 28 21:29 00000001000000000000001F
-rw-------. 1 postgres postgres 16777216 May 28 21:29 000000010000000000000020
-rw-------. 1 postgres postgres 16777216 May 28 21:29 000000010000000000000021
-rw-------. 1 postgres postgres 16777216 May 28 21:29 000000010000000000000022
-rw-------. 1 postgres postgres 16777216 May 28 21:30 000000010000000000000023
-rw-------. 1 postgres postgres 16777216 May 28 21:30 000000010000000000000024
-rw-------. 1 postgres postgres 16777216 May 28 21:30 000000010000000000000025
-rw-------. 1 postgres postgres 16777216 May 29 21:51 000000010000000000000013
-bash-4.2$ rm -rf 0000000100000000000000*
-bash-4.2$ ls
archive_status

查询当时数据库需要的最小wal记录

-bash-4.2$ pg_controldata 
pg_control version number:            1300
Catalog version number:               202107181
Database system identifier:           7100998319216817119
Database cluster state:               in production
pg_control last modified:             Sat 28 May 2022 09:36:11 PM CST
Latest checkpoint location:           0/13692F80
Latest checkpoint's REDO location:    0/13692F48
Latest checkpoint's REDO WAL file:    000000010000000000000013   <===需要的记录
Latest checkpoint's TimeLineID:       1
Latest checkpoint's PrevTimeLineID:   1
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID:          0:17824
Latest checkpoint's NextOID:          32769
Latest checkpoint's NextMultiXactId:  1
Latest checkpoint's NextMultiOffset:  0
Latest checkpoint's oldestXID:        727
Latest checkpoint's oldestXID's DB:   1
Latest checkpoint's oldestActiveXID:  17824
Latest checkpoint's oldestMultiXid:   1
Latest checkpoint's oldestMulti's DB: 1
Latest checkpoint's oldestCommitTsXid:0
Latest checkpoint's newestCommitTsXid:0
Time of latest checkpoint:            Sat 28 May 2022 09:31:41 PM CST

尝试启动PG

-bash-4.2$ pg_ctl start
pg_ctl: another server might be running; trying to start server anyway
waiting for server to start....2022-05-29 21:52:22.926 CST [35270] LOG:  
redirecting log output to logging collector process
2022-05-29 21:52:22.926 CST [35270] HINT:  Future log output will appear in directory "log".
. stopped waiting
pg_ctl: could not start server
Examine the log output.

启动pg失败,查看日志记录

2022-05-29 21:52:22.926 CST [35270] LOG:  starting PostgreSQL 14.3 on x86_64-pc-linux-gnu, 
            compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2022-05-29 21:52:22.927 CST [35270] LOG:  listening on IPv6 address "::1", port 5432
2022-05-29 21:52:22.927 CST [35270] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2022-05-29 21:52:22.929 CST [35270] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2022-05-29 21:52:22.931 CST [35270] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2022-05-29 21:52:22.936 CST [35272] LOG:  database system was interrupted; last known up at 2022-05-28 21:36:11 CST
2022-05-29 21:52:23.049 CST [35272] LOG:  invalid primary checkpoint record
2022-05-29 21:52:23.049 CST [35272] PANIC:  could not locate a valid checkpoint record
2022-05-29 21:52:24.211 CST [35270] LOG:  startup process (PID 35272) was terminated by signal 6: Aborted
2022-05-29 21:52:24.211 CST [35270] LOG:  aborting startup due to startup process failure
2022-05-29 21:52:24.218 CST [35270] LOG:  database system is shut down

错误比较明显,无法定位到有效的checkpoint记录,在oracle里面的意思可以理解为无法进行实例恢复,pg启动失败

重设wal
由于数据库为不一致状态,需要使用-f进行强制重设

-bash-4.2$ pg_resetwal $PGDATA
The database server was not shut down cleanly.
Resetting the write-ahead log might cause data to be lost.
If you want to proceed anyway, use -f to force reset.
-bash-4.2$ pg_resetwal -f $PGDATA
Write-ahead log reset

启动PG成功

-bash-4.2$ pg_ctl start -D $PGDATA
waiting for server to start....2022-05-29 22:01:02.647 CST [37178] LOG:  
redirecting log output to logging collector process
2022-05-29 22:01:02.647 CST [37178] HINT:  Future log output will appear in directory "log".
 done
server started

日志记录

2022-05-29 22:01:02.647 CST [37178] LOG:  starting PostgreSQL 14.3 on x86_64-pc-linux-gnu, 
                    compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2022-05-29 22:01:02.648 CST [37178] LOG:  listening on IPv6 address "::1", port 5432
2022-05-29 22:01:02.648 CST [37178] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2022-05-29 22:01:02.649 CST [37178] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2022-05-29 22:01:02.651 CST [37178] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2022-05-29 22:01:02.653 CST [37180] LOG:  database system was shut down at 2022-05-29 22:00:47 CST
2022-05-29 22:01:02.661 CST [37178] LOG:  database system is ready to accept connections

查看wal日志,产生新记录

-bash-4.2$ pwd
/var/lib/pgsql/14/data/pg_wal
-bash-4.2$ ls -ltr
total 16384
drwx------. 2 postgres postgres        6 May 24 02:20 archive_status
-rw-------. 1 postgres postgres 16777216 May 29 22:01 000000010000000000000014

验证刚刚创建测试表

-bash-4.2$ psql
psql (14.3)
Type "help" for help.

postgres=# select count(1) from t_xifenfei;
ERROR:  relation "t_xifenfei" does not exist
LINE 1: select count(1) from t_xifenfei;
                             ^

由于需要进行实例恢复的wal日志丢失导致这表记录也丢失.由此可见这类操作可能导致数据丢失风险,对于生产环境,需要慎重,