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

When an exception occurs or a table is damaged, you need to use the ledger_gchain_repair(text, text) API to restore the global blockchain table or use the ledger_hist_repair(text, text) API to restore the user history table. After the restoration, the result of calling the global blockchain table or user history 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

Repair a specified user history table.

      
           gaussdb=# 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 current node is successfully repaired. The hash increment of the user history table is 84e8bfc3b974e9cf.

Repair a specified 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 query result indicates that the global blockchain table is successfully repaired and a piece of repair data is inserted. The hash value 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);
CREATE TABLE
gaussdb=# DROP TABLE ledgernsp.tab2;
DROP TABLE
gaussdb=# SELECT rcyrelid, rcyname, rcyoriginname FROM gs_recyclebin;
 rcyrelid |           rcyname            |    rcyoriginname
----------+------------------------------+---------------------
    16717 | BIN$38242338414D$42EB978==$0 | tab2
    16725 | BIN$382423384155$42EC678==$0 | gs_hist_tab2_index
    16722 | BIN$382423384152$42ECC30==$0 | ledgernsp_tab2_hist
    16720 | BIN$382423384150$42ED3E0==$0 | pg_toast_16717
(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=# TIMECAPSULE TABLE ledgernsp.tab2 TO BEFORE DROP;
TimeCapsule 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
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);
CREATE TABLE
gaussdb=# DROP TABLE ledgernsp.tab3;
DROP TABLE
gaussdb=# SELECT rcyrelid, rcyname, rcyoriginname FROM gs_recyclebin;
 rcyrelid |           rcyname            |    rcyoriginname
----------+------------------------------+---------------------
    17574 | BIN$44A4233844A6$B18E7A0==$0 | tab3
    17582 | BIN$44A4233844AE$B18F488==$0 | gs_hist_tab3_index
    17579 | BIN$44A4233844AB$B18FA40==$0 | ledgernsp_tab3_hist
    17577 | BIN$44A4233844A9$B190208==$0 | pg_toast_17574
(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$44A4233844A6$B18E7A0==$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
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