标签云
asm恢复 bbed bootstrap$ dul In Memory kcbzib_kcrsds_1 kccpb_sanity_check_2 kfed MySQL恢复 ORA-00312 ORA-00607 ORA-00704 ORA-01110 ORA-01555 ORA-01578 ORA-08103 ORA-600 2131 ORA-600 2662 ORA-600 2663 ORA-600 3020 ORA-600 4000 ORA-600 4137 ORA-600 4193 ORA-600 4194 ORA-600 16703 ORA-600 kcbzib_kcrsds_1 ORA-600 KCLCHKBLK_4 ORA-15042 ORA-15196 ORACLE 12C oracle dul ORACLE PATCH Oracle Recovery Tools oracle加密恢复 oracle勒索 oracle勒索恢复 oracle异常恢复 Oracle 恢复 ORACLE恢复 ORACLE数据库恢复 oracle 比特币 OSD-04016 YOUR FILES ARE ENCRYPTED 勒索恢复 比特币加密文章分类
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (102)
- 数据库 (1,655)
- DB2 (22)
- MySQL (71)
- Oracle (1,519)
- Data Guard (51)
- EXADATA (8)
- GoldenGate (21)
- ORA-xxxxx (158)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (14)
- ORACLE 21C (3)
- Oracle 23ai (7)
- Oracle ASM (65)
- Oracle Bug (8)
- Oracle RAC (52)
- Oracle 安全 (6)
- Oracle 开发 (28)
- Oracle 监听 (28)
- Oracle备份恢复 (553)
- Oracle安装升级 (90)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (76)
- PostgreSQL (18)
- PostgreSQL恢复 (6)
- SQL Server (27)
- SQL Server恢复 (8)
- TimesTen (7)
- 达梦数据库 (2)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (37)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (20)
-
最近发表
- RAC默认服务配置优先节点
- Oracle 19c RAC 替换私网操作
- 监听报TNS-12541 TNS-12560 TNS-00511错误
- drop tablespace xxx including contents恢复
- Linux 8 修改网卡名称
- 如何修改集群的公网信息(包括 VIP) (Doc ID 1674442.1)
- 如何在 oracle 集群环境下修改私网信息 (Doc ID 2103317.1)
- ORA-600 [kcvfdb_pdb_set_clean_scn: cleanckpt] 相关bug
- ORA-600 krhpfh_03-1210故障处理
- 19c库启动报ORA-600 kcbzib_kcrsds_1
- DBMS_SESSION.set_context提示ORA-01031问题解决
- redo写丢失导致ORA-600 kcrf_resilver_log_1故障
- 硬件故障导致ORA-01242 ORA-01122等错误
- 200T 数据库非归档无备份恢复
- 利用flashback快速恢复failover 的备库
- [comingback2022@cock.li].eking和[tsai.shen@mailfence.com].faust扩展名勒索病毒数据库可以完美恢复
- opatch auto 出现unable to get oracle owner for 错误
- Oracle 23ai 表和视图的列最多支持到4096个
- 断电引起redo和数据文件不一致故障恢复
- ORA-03113: 通信通道的文件结尾
标签归档:pg解析wal
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' Persional 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日志