云数据库 GAUSSDB-修复账本数据库:恢复用户表和用户历史表名称

时间:2024-11-13 10:05:08

恢复用户表和用户历史表名称

已通过enable_recyclebin参数和recyclebin_retention_time参数开启闪回DROP功能,恢复用户表和用户历史表名称。示例如下:

  • DROP用户表,对用户表执行闪回DROP。使用ledger_hist_repair对用户表、用户历史表进行表名恢复。
    -- 对用户表执行闪回drop,使用ledger_hist_repair对用户历史表进行表名恢复。
    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)
    -- 对用户表执行闪回drop。
    gaussdb=# TIMECAPSULE TABLE ledgernsp.tab2 TO BEFORE DROP;
    TimeCapsule Table
    -- 使用ledger_hist_repair恢复用户历史表表名。
    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
    
    -- 对用户表执行闪回drop,使用ledger_hist_repair对用户表进行表名恢复。
    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)
    -- 对用户历史表执行闪回drop。
    gaussdb=# TIMECAPSULE TABLE blockchain.ledgernsp_tab3_hist TO BEFORE DROP;
    TimeCapsule Table
    -- 拿到回收站中用户表对应的rcyname,使用ledger_hist_repair恢复用户表表名。
    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
    
    -- 删除表。
    gaussdb=# DROP TABLE ledgernsp.tab2 PURGE;
    DROP TABLE
    gaussdb=# DROP TABLE ledgernsp.tab3 PURGE;
    DROP TABLE
support.huaweicloud.com/fg-gaussdb-dist-v8/gaussdb-18-0025.html