Updated on 2025-05-29 GMT+08:00

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

  1. Repair a specified user history table.

    1
    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.

  2. Repair a specified global blockchain table.

    1
    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