mog_xlogdump User Guide

Introduction

mog_xlogdump is an offline parsing tool for wal logs independently developed by Enmo. It is mainly used in the active-standby cluster scenario, when the database is permanently down and cannot be recovered, reversely analyze the database that cannot be started, and then recover the data that is not synchronized at the end of the wal log in the cluster.

R&D Background

In MogDB primary/standby high availability cluster with one primary database and multiple standby databases, using asynchronous logical replication scenario, when the primary shuts down and its transaction is committed, the data of the transaction operation has been written to the wal log. After the primary is down, the standby will generate incomplete data segment logs because it cannot send to the standby. Therefore, after the primary is down, there is data loss and no logical alignment between the standby and the primary. So there is a risk of data loss in the primary/standby cluster composed of its standby databases and the data in the actual business.

During the recovery of the primary database, the cluster composed of the standby database will have business data written. At this time, when the primary database is restored immediately, the segment number, start and end position of the incomplete data segment at the end of the wal log of the primary database are the same as those in the standby database. The inconsistency will also cause the data lost when the primary database shuts down and cannot be restored to the standby database.

Scenario

In a MogDB high-availability cluster, when the primary database is down, the walbuffer is triggered to write to the wal log when the walbuffer is filled with a certain percentage, or when the checkpoint or commit occurs. Due to the database downtime, the logically synchronized WalSender thread stops sending logs, and the standby database receives incomplete data segment wal logs. At this time, you need to use the flashback tool to read the data blocks in the wal log of the primary database, and decode the SQL statement corresponding to the data operation, so that the DBA can analyze whether the data is valuable and restore it to the standby database.

Principle

The tool uses two mechanisms, one is the header parsing of the wal log, and the other is the logical replication mechanism.

The implementation steps are mainly divided into three steps:

1. Read the wal log file and parse the header.

2. Read the data in turn, and de-decode the data.

3. According to the different data types of the data, different types of function outputs are called back.

Supported Table Types for Parsing

Partitioned and normal tables are currently supported.

Supported Data Types for Parsing

bool, bytea, char, name, int8, int2, int, text, oid, tid, xid, cid, xid32, clob, float4, float8, money, inet, varchar, numeric, int4;

注意：Note: Since mog_xlogdump is an offline wal parsing tool, it does not currently support large data types (clob, etc.) that require toast data. The next version will support offline parsing of toast table files.

Installation

Visit MogDB official website download page to download the corresponding version of the toolkit, and put the tool in the bin directory of the MogDB installation path. As shown below, toolkits-xxxxxx.tar.gz is the toolkit that contains mog_xlogdump.

Instructions for Use

mog_xlogdump is a tool for parsing and displaying MogDB 2.1 or later WAL logs. Auxiliary tool designed to help DBAs analyze and debug database problems.

The mog_xlogdump parsing tool currently does not support column table. (The research found that the column-stored table will generate two corresponding CUDesc and delta tables in cstore mode. CUDesc is the metadata of the column-stored table, and delta is the temporary table of the column-stored table, which is a row-stored table. These two tables (CUDesc, delta) will be written in the wal log. Although the wal log can parse the corresponding delta table, the table is controlled by the table attribute threshold deltarow_threshold. The default is 100, that is, less than 100 pieces of data will be written in the delta table. Write directly to the cu file)

Note: To write the delta table in the column-store table, you need to enable the parameter enable_delta_store = on in postgres.conf.

How to Use

mog_xlogdump [OPTION]... [STARTSEG [ENDSEG]]

Options

• -b, --bkp-details

  Details of output file blocks. (By default, the id of the block, rel, fork, blk, and lastlsn is displayed, and this parameter will display the Block Image)

• -B, --bytea_output

  Specify the display format of bytea type decoding output, there are binary and character formats

• -c --connectinfo

  Specify a connect string URL, such as postgres://user:password@ip:port/dbname

• -e, --end=RECPTR

  Specify the end position for parsing the wal log, LSN number

• -f, --follow

  Indicates that when the specified wal log is parsed to the end, continue parsing to the next file

• -n, --limit=N

  Specify the number of output data records

• -o, --oid=OID

  Specifies the OID of the inverse decoding table

• -p, --path=PATH

  Specify the wal log storage directory

• -R, --Rel=Relation

  Specifies the data type of the inverse decoding table

• -r, --rmgr=RMGR

  Show only the contents of records generated by the explorer

• -s, --start=RECPTR

  Specify the starting position for parsing the wal log, LSN number

• -T, --CTimeZone_ENV

  Specify the time zone, the default is UTC.

• -t, --timeline=TLI

  Specify the timeline to start reading the wal log

• -V, --version

  show version number

• -w, --write-FPW

  Display the information written on the full page, use with -b

• -x, --xid=XID

  Output the record with the specified transaction ID

• -z, --stats

  Output statistics of inserted records

• -v, --verbose

  show verbose

• -?, --help

  show help information and exit

Use Case 1

Scenario

When the primary database is down and cannot be recovered, the standby database can be connected normally. At this time, the wal log sent by the primary database may contain tens of thousands of table data operations, and the mog_xlogdump tool needs to start and end according to the specified -s, -e (starting and the ending lsn position), parse out all data operations of the table.

Instruction

mog_xlogdump -c <Standby connect string> -s <starting lsn> -e <ending lsn> <primary node wal log file>

Parse Settings

Note: The main purpose is to record old data in the wal log, that is, the data tuple before the update operation is modified, and the data deleted by the delete operation.

1. Set the wal_level in the database configuration file postgres.conf to the logical level.
2. Alter table: alter table table_name replica identity full;

Result

Output the wal log data parsing result in json format. The tuple display format is 'column name':'data'

{'table_name':'xxx','schema_name':'yyy','action':'insert','tuple':{'name':'xx','id':'ss'}}

Example

The red box is the old data that will be parsed according to the parsing settings. If there is no setting, the old data of update and delete will not be parsed.

The standby connect URL after -c is postgres://test:Test123456@172.16.0.44:5003/postgres

• postgres://

  connect string tag header

• test

  connect username

• Test123456

  The password of the connect user

• 172.16.0.44

  The IP address of the standby node

• 5003

  Standby connect port

• postgres

  The database name of the connect standby node

• db_p/pg_xlog/000000010000000000000004

  The wal log file of primary node

Use Case 2

Scenario

When the primary database is down and cannot be recovered, and the standby database can be connected normally, the user may only pay attention to a few tables (individual tables) in the database. The mog_xlogdump tool can parse the table data of the specified oid according to the parameters -o and -R. For example, -o specifies the oid of the table, and -R specifies the field type of the table.

Instruction

Create a table, write data and modify it, and use the mog_xlogdump tool to parse the Wal log.

create table t2(id int, money money,inet inet,bool bool,numeric numeric ,text text);
insert into t2 values(1, 24.241, '192.168.255.132', true, 3.1415926, 'ljfsodfo29892ifj');
insert into t2 values(2, 928.8271, '10.255.132.101', false, 3.1415926, 'vzvzcxwf2424@');
update t2 set id=111, money=982.371 where id =2;
delete from t2 where id=1;

postgres=# select * from t2;
 id | money |   inet    | bool | numeric |    text    
----+---------+-----------------+------+-----------+------------------
 1 | $24.24 | 192.168.255.132 | t  | 3.1415926 | ljfsodfo29892ifj
 2 | $928.83 | 10.255.132.101 | f  | 3.1415926 | vzvzcxwf2424@
(2 rows)

postgres=# update t2 set id=111, money=982.371 where id =2;
Postgres=# delete from t2 where id=1;
postgres=# select * from t2;
 id |  money  |   inet   | bool | numeric |   text   
-----+-------------+----------------+------+-----------+---------------
 111 | $982,371.00 | 10.255.132.101 | f  | 3.1415926 | vzvzcxwf2424@
 
(1 rows)

Parse Settings

Same as use case 1, set wal_level and alter table.

Result

./mog_xlogdump -o 16394 -R int,money,inet,bool,numeric,text ./db_p/pg_xlog/000000010000000000000004 
'insert','tuple':{'(null)':'1','(null)':'$24.24','(null)':'192.168.255.132','(null)':true,'(null)':'3.1415926','(null)':'ljfsodfo29892ifj'}}
'insert','tuple':{'(null)':'2','(null)':'$928.83','(null)':'10.255.132.101','(null)':false,'(null)':'3.1415926','(null)':'vzvzcxwf2424@'}}
'update','old_tuple':{'(null)':'2','(null)':'$928.83','(null)':'10.255.132.101','(null)':false,'(null)':'3.1415926','(null)':'vzvzcxwf2424@'},'new_tuple':{'(null)':'111','(null)':'$982,371.00','(null)':'10.255.132.101','(null)':false,'(null)':'3.1415926','(null)':'vzvzcxwf2424@'}}
'delete','tuple':{'(null)':'1','(null)':'$24.24','(null)':'192.168.255.132','(null)':true,'(null)':'3.1415926','(null)':'ljfsodfo29892ifj'}}

Note: Due to the change of the output format, the table name, schema name and column name are queried on the standby node according to the -c connect string, but because the original -o, -R designation of the table oid and field type is completely offline, Therefore, information such as table name, schema name, and column name cannot be obtained, so use -o and -R to parse offline. The table name and schema name are not displayed, and the column name is displayed as null.

mog_xlogdump -o <table OID> -R <the data type of the table> -s <starting LSN> -e <ending LSN> Wal log file

The tool also retains the original functionality of pg_xlogdump.

Use Case 3

Scenario

If you want to see the header data content of the wal log, or to count some related information of the wal log, please use the mog_xlogdump original function.

Instruction

1. header information

   ./mog_xlogdump -n 10 <wal log file>

   -n 10 indicates that only 10 rows of data are displayed.

2. Statistics

   ./mog_xlogdump -z <wal log file>

Results

• Result 1

• Result 2