Repairing a Ledger Database

Prerequisites

You are an audit administrator or a role that has the AUDITADMIN permission.
The database is running properly, and a series of addition, deletion, and modification operations are performed on the tamper-proof database to ensure that operation records are generated in the ledger for query.

Context

The current ledger database mechanism is as follows: The global blockchain table is stored on the CN, and data on each CN is independent. User history tables are stored on DNs and record data changes in the tamper-proof tables on DNs. Therefore, when data redistribution is triggered, data in the tamper-proof table may be inconsistent with that in the user history table. In this case, you need to use the ledger_hist_repair(text, text) API to restore the user history table on a specified DN. After the fault is rectified, the result of calling the historical table verification API on the current DN is true. When CNs are removed or repaired, data in the global blockchain table may be lost or inconsistent with that in the user history table. In this case, you need to use the ledger_gchain_repair(text, text) API to restore the global blockchain table in the entire cluster. After the fault is rectified, the result of calling the global blockchain table verification API is true.
The API for repairing a user history table is pg_catalog.ledger_hist_repair. To repair the table, run the following command:
```
SELECT pg_catalog.ledger_hist_repair(schema_name text,table_name text);
```
If the repair is successful, the function returns the hash increment of the user history table during the repair.

Note: When performing flashback DROP on a user table, you can use this function to repair the names of the user table and user history table. For details, see Repairing the Names of a User table and User History Table.
The API for repairing the global blockchain table is pg_catalog.ledger_gchain_repair. To repair the table, run the following command:
```
SELECT pg_catalog.ledger_gchain_repair(schema_name text,table_name text);
```
If the repair is successful, the function returns the total hash value of the specified table in the global blockchain table.

Restoring Data in the User Table and Global Blockchain Table

The omm user is used as an example. The procedure is as follows:

Run EXECUTE DIRECT to restore a history table on a DN.

      
           gaussdb=# EXECUTE DIRECT ON (datanode1) 'select pg_catalog.ledger_hist_repair(''ledgernsp'', ''usertable'');';

The query result is as follows:

 ledger_hist_repair
--------------------
 84e8bfc3b974e9cf
(1 row)

The query result indicates that the user history table on the datanode1 node is successfully repaired. The hash increment of the user history table is 84e8bfc3b974e9cf.

Connect to the CN to repair the global blockchain table.

      
           gaussdb=# SELECT pg_catalog.ledger_gchain_repair('ledgernsp', 'usertable');

The query result is as follows:

 ledger_gchain_repair
----------------------
 a41714001181a294
(1 row)

The command output indicates that the global blockchain table of the current cluster is successfully repaired and a piece of repair data is inserted into the current CN. The hash value of the data is a41714001181a294.

Repairing the Names of a User table and User History Table

The flashback DROP function has been enabled by using the enable_recyclebin and recyclebin_retention_time parameters to repair the names of user tables and user history tables. The following is an example:

Perform flashback DROP on a user table. Use ledger_hist_repair to repair the names of the user table and user history table.

-- Perform flashback DROP on the user table and use the ledger_hist_repair API to repair the name of the user history table.
gaussdb=# CREATE TABLE ledgernsp.tab2(a int, b text);
NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'a' as the distribution column by default.
HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'rec_num' as the distribution column by default.
HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
CREATE TABLE
gaussdb=# DROP TABLE ledgernsp.tab2;
DROP TABLE
gaussdb=# SELECT rcyrelid, rcyname, rcyoriginname FROM gs_recyclebin;
 rcyrelid |           rcyname            |    rcyoriginname
----------+------------------------------+---------------------
    32838 | BIN$39B523388046$55C8400==$0 | tab2
    32846 | BIN$39B52338804E$55C90E8==$0 | gs_hist_tab2_index
    32843 | BIN$39B52338804B$55C96A0==$0 | ledgernsp_tab2_hist
    32841 | BIN$39B523388049$55C9EE0==$0 | pg_toast_32838
(4 rows)
-- Perform flashback DROP on the user table.
gaussdb=# TIMECAPSULE TABLE ledgernsp.tab2 TO BEFORE DROP;
TimeCapsule Table
-- Use the ledger_hist_repair API to repair the name of the user history table.
gaussdb=# SELECT ledger_hist_repair('ledgernsp', 'tab2');
 ledger_hist_repair
--------------------
 0000000000000000
(1 row)

gaussdb=# \d+ ledgernsp.tab2;
                          Table "ledgernsp.tab2"
   Column    |  Type   | Modifiers | Storage  | Stats target | Description
-------------+---------+-----------+----------+--------------+-------------
 a           | integer |           | plain    |              |
 b           | text    |           | extended |              |
 hash_1d2d14 | hash16  |           | plain    |              |
Has OIDs: no
Distribute By: HASH(a)
Location Nodes: ALL DATANODES
Options: orientation=row, compression=no, storage_type=USTORE, segment=off, toast.storage_type=USTORE, toast.toast_storage_type=enhanced_toast
History table name: ledgernsp_tab2_hist

-- Perform flashback DROP on the user table and use the ledger_hist_repair API to repair the name of the user table.
gaussdb=# CREATE TABLE ledgernsp.tab3(a int, b text);
NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'a' as the distribution column by default.
HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'rec_num' as the distribution column by default.
HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
CREATE TABLE
gaussdb=# DROP TABLE ledgernsp.tab3;
DROP TABLE
gaussdb=# SELECT rcyrelid, rcyname, rcyoriginname FROM gs_recyclebin;
 rcyrelid |           rcyname            |    rcyoriginname
----------+------------------------------+---------------------
    32952 | BIN$80B6233880B8$FECFF98==$0 | tab3
    32960 | BIN$80B6233880C0$FED0C98==$0 | gs_hist_tab3_index
    32957 | BIN$80B6233880BD$FED1250==$0 | ledgernsp_tab3_hist
    32955 | BIN$80B6233880BB$FED1A00==$0 | pg_toast_32952
(4 rows)
-- Perform flashback DROP on the user history table.
gaussdb=# TIMECAPSULE TABLE blockchain.ledgernsp_tab3_hist TO BEFORE DROP;
TimeCapsule Table
-- Obtain the rcyname corresponding to the user table in the recycle bin and use the ledger_hist_repair API to repair the name of the user table.
gaussdb=# SELECT ledger_hist_repair('ledgernsp', 'BIN$80B6233880B8$FECFF98==$0');
 ledger_hist_repair
--------------------
 0000000000000000
(1 row)

gaussdb=# \d+ ledgernsp.tab3;
                          Table "ledgernsp.tab3"
   Column    |  Type   | Modifiers | Storage  | Stats target | Description
-------------+---------+-----------+----------+--------------+-------------
 a           | integer |           | plain    |              |
 b           | text    |           | extended |              |
 hash_7a0c87 | hash16  |           | plain    |              |
Has OIDs: no
Distribute By: HASH(a)
Location Nodes: ALL DATANODES
Options: orientation=row, compression=no, storage_type=USTORE, segment=off, toast.storage_type=USTORE, toast.toast_storage_type=enhanced_toast
History table name: ledgernsp_tab3_hist

-- Drop the table.
gaussdb=# DROP TABLE ledgernsp.tab2 PURGE;
DROP TABLE
gaussdb=# DROP TABLE ledgernsp.tab3 PURGE;
DROP TABLE