XlogMiner Enhancements Released and Renamed to WalMiner

XlogMiner is a tool for parsing executed SQL statements from PostgreSQL's WAL and can generate corresponding undo SQL statements. The open source project address is https://github.com/HighgoSoftware/XLogMiner. This version has a large usage limit, you need to set the wal level to logical, and you need to set the table to IDENTITY FULL mode. This can exacerbate the bloat of wal and reduce database performance.

To cater to the changes in the PG log name, XlogMiner is now renamed to WalMiner. The new open source address is temporarily at https://gitee.com/movead/XLogMiner.

Release Enhancements

The analyse source of the old version of the tool xlogminer is the "change data" in current wal record. It does not get data from the page in FPW for parsing of wal records.

The new version of walminer can not only parse the FPW of the current wal record, but also record and redo all FPWs that appear during the parsing process. So walminer can resolve low-level wal and you don't need to set the table to IDENTITY FULL mode.

Additionally:

1.WalMiner supports parsing any wal log level above the minimum level.

2.No need to set the table to IDENTITY FULL mode.

3.Increase the resolution of the wal record modified by the system table.

4.Fix a bug that failed to parse in other databases after a relfilenode change occurred.

New restrictions brought by WalMiner

Walminer can fully parse out all wal records after the first checkpoint in the given wal. The delete and update records before the first checkpoint may fail to parse, as follows:

UPDATE "public"."t1" SET VALUES(NULL) (NOTICE:wal is not enought.);

If you need to resolve this record, you only need to add some earlier wal segment.

The specific usage is viewed in the README.EN.MD in the open source code.

limits

1. This version only parses DML statements and does not process DDL statements. Future changes: The parsing of DDL statements has been put into the todolist, which may gradually support various DDL statements.
2. The DML statemes would NOT be parsed out when the below DDL related operations were executed: Deleting/Truncating table, table space modification and column type modification etcs. Response: It is recommended to save a data dictionary to ensure that the history wal log can be parsed before performing the table structure change. Future changes: Now I have added the ability to save the data dictionary in walminer.
3. The parsing result is depending on the latest database dictionary. For example, after user1 created table t1, the table owner was modified to user2, then all the parsing results related to table t1 will be marked with user2. Response: It is recommended to save a data dictionary to ensure that the history wal log can be parsed before performing the table structure change. Future changes: Now I have added the ability to save the data dictionary in walminer.
4. The "ctid" attribute is the value of the change "at that time". If there are "ctid" changes due to vacuum or other operations, this value will be inaccurate. We need use this value to determine the corresponding undo tuples when the rows of data are duplicate, it does not mean that you can execute such undo statements directly.
5. If the DDL statement "drop" was executed, all related column value will be decoded as "encode('AD976BC56F',hex)" before this DDL execution.
6. Can only parse the wal file consistent with the data dictionary timeline
7. WalMiner is an individual product and has not been fully tested.

contact me

If you find bugs or have good suggestions, you can contact me via email (lchch1990@sina.cn).