Shulou(Shulou.com)06/01 Report--

Author: Tang Cheng, Chief Database architect of Waldorf Technology

The raising of a question

Someone asked whether the data just deleted in the PostgreSQL database could be recovered.

Or further, if you want to do a series of updates, deletions, inserts in a transaction, and then regret the transaction, can you return to the previous state?

Of course, if the database has a backup, it can be recovered directly from the backed-up data. This article discusses whether it can be restored without a backup.

Theoretical analysis.

From the principle of multi-version implementation of PostgreSQL, this is possible. Because the multi-version principle of PostgreSQL is that old data is not deleted:

For deleting data, simply change the xmax on the line to the current transaction id

For the update operation, simply change the previous xmax to the current transaction id and insert a new row, while the xmin on the new line is set to the current transaction id

The status of the transaction is recorded in commit log. If the transaction commits, just change the corresponding transaction status in commit log to "committed status (TRANSACTION_STATUS_COMMITTED)". If the transaction rolls back, change the transaction status in commit log to "transaction rollback (TRANSACTION_STATUS_ABORTED)".

So in theory, as long as the state of the newly committed transaction in commit log is changed from "TRANSACTION_STATUS_COMMITTED" to "TRANSACTION_STATUS_ABORTED", the original transaction will be invalidated and can return to the previous state of the transaction.

However, the premise of this recovery is that the old version of the data has not been cleaned up by the vacuum garbage collection process, and cannot be recovered if the old version of the data has been cleaned up by the vacuum garbage collection process. So if you stop the database immediately after deleting the data, the data can be recovered before the autovacuum process cleans up the old version of the data.

But can data be recovered simply by changing the state of the transaction in commit log? The answer is also no, it's not that simple, because multiple versions of visibility judgment are determined not only by the state of the transaction in the commit log, but also by the hint information in the t_infomask status bit. If hint has indicated that the transaction on that line has been committed, you no longer need to go to commit log to see the status of the transaction. The main purpose of this feature is to improve performance, because the visibility of rows is determined in clog, while only eight blocks in clog are cached in shared memory, so it would be inefficient to determine that each row is looking for clog. For the details of this section, you can see my other blog: the role of the t_infomask field in the visibility judgment of rows in PostgreSQL.

So in order to recover the data, you also need to clear the hint flag bits in the t_infomask status of each line in the corresponding table file before the data can be recovered.

Tools for recovery

Because the whole recovery process is complicated, I wrote a tool called pg_fix, which is put on github: https://github.com/osdba/pg_fix, for everyone to study and use.

First, you can use this tool to query the status of rows in the data file of a table:

Use this tool to clean up the hint information in the t_infomask in the table's data file, and check the hint status on the row before cleaning up the t_maskinfo state:

Then execute the following command to clear the hint status on the line:

After cleaning up, we will look at the t_infomask status on the line:

The methods to query and change the state of a transaction are as follows:

The command to query the status of the transaction xid=11 is as follows:

The command to modify the state of the transaction xid=11 is as follows:

The value after-s indicates what state you want to change the transaction to. There are four kinds of transaction status values, which are 0: 3, meaning as follows:

# define TRANSACTION_STATUS_IN_PROGRESS 0x00

# define TRANSACTION_STATUS_COMMITTED 0x01

# define TRANSACTION_STATUS_ABORTED 0x02

# define TRANSACTION_STATUS_SUB_COMMITTED 0x03

Of course, the above use of the pg_fix tool to directly modify the data in the table and the state of the transaction in commit log must be the case when the database stops.

In addition, the main purpose of this article is to study some of the principles of PostgreSQL, so these operations usually do not take to the production database to try!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.