GBase 8c Backup and Recovery Based on XID

With the widespread application of database technology across various industries and fields, issues such as human errors, malicious damage, system instability, and storage medium failures can lead to significant data loss. Such losses can cause substantial harm to both businesses and individuals. Therefore, database recovery is crucial. Database recovery involves using technical methods to restore invisible or malfunctioning data files to a working state. Below, we’ll perform an experimental exercise on backup and recovery based on XID (Transaction ID) in GBase 8c.

In GBase 8c, XID is a unique identifier for a transaction, primarily used for tracking and managing transaction commits and rollbacks in the database.

0. Prepare the Environment

Simulate a Full Backup:

gs_probackup backup -B /home/gbase/backup/ -b full -p 15400 -U test -W xxxxx --instance hostname -d postgres

Simulate New Changes in the Database:

CREATE DATABASE pit;
CREATE TABLE test(id INT);
INSERT INTO test VALUES(1), (11), (111);
CREATE TABLE ccc(id INT);
INSERT INTO ccc VALUES(1), (11), (111);
SELECT * FROM ccc;
SELECT * FROM test;
DROP DATABASE pit;

Assume a drop operation was mistakenly executed.

Let's outline the recovery steps:

1. Ensure Current Redo Logs

Check the current redo logs by executing:

   SELECT pg_xlogfile_name(pg_current_xlog_location());

Example output:

   pg_xlogfile_name
   --------------------------
   00000001000000000000001F

Then, locate the log file:

   ll /opt/database/install/data/dn/pg_xlog/00000001000000000000001F

2. Switch Archive Logs

Check switchable archive logs:

   SELECT pg_switch_xlog();

Current log location:

   SELECT pg_xlogfile_name(pg_current_xlog_location());

Based on the returned information:

• Drop operations are stored in the file 00000001000000000000001F.
• After switching archive logs, check if the file 00000001000000000000001F exists on the backup server.

3. Stop the Current Damaged Database

Stop the GBase 8c database:

   gs_om -t stop

4. Restore Full Backup

Copy the backup file to the data directory and restore it:

   cp /home/gbase/backup/DB-2024xxx.tar.gz /data
   cd /data
   tar xf DB-2024xxx.tar.gz
   mv DB-2024xxx.tar.gz /opt/database/install/data/dn

5. Restore Archive System and Locate Drop Point

Create the directory and copy the archive logs:

   mkdir -p /opt/database/install/data/dn/pg_xlog/
   cp archive/* /opt/database/install/data/dn/pg_xlog/
   pg_xlogdump /opt/database/install/data/dn/pg_xlog/00000001000000000000001F

Example output:

   rmgr: Heap        len (rec/tot):     59/    59, tx:      22849, lsn: 0/5F00A1B8, prev 0/5F00A178, desc: INSERT off 3 flags 0x08, blkref #0: rel 1663/40976/40980 blk 0
   rmgr: Transaction len (rec/tot):     46/    46, tx:      22849, lsn: 0/5F00A1F8, prev 0/5F00A1B8, desc: COMMIT 2021-10-29 16:59:33.596676 CST
   rmgr: Standby     len (rec/tot):     50/    50, tx:          0, lsn: 0/5F00A228, prev 0/5F00A1F8, desc: RUNNING_XACTS nextXid 22850 latestCompletedXid 22849 oldestRunningXid 22850
   rmgr: Heap2       len (rec/tot):     60/    60, tx:      22850, lsn: 0/5F00A260, prev 0/5F00A228, desc: NEW_CID rel 1664/0/1262; tid 0/12; cmin: 4294967295, cmax: 0, combo: 4294967295
   rmgr: Heap        len (rec/tot):     54/    54, tx:      22850, lsn: 0/5F00A2A0, prev 0/5F00A260, desc: DELETE off 12 flags 0x00 KEYS_UPDATED , blkref #0: rel 1664/0/1262 blk 0
   rmgr: Standby     len (rec/tot):     54/    54, tx:          0, lsn: 0/5F00A2D8, prev 0/5F00A2A0, desc: RUNNING_XACTS nextXid 22851 latestCompletedXid 22849 oldestRunningXid 22850; 1 xacts: 22850
   rmgr: Standby     len (rec/tot):     54/    54, tx:          0, lsn: 0/5F00A310, prev 0/5F00A2D8, desc: RUNNING_XACTS nextXid 22851 latestCompletedXid 22849 oldestRunningXid 22850; 1 xacts: 22850
   rmgr: XLOG        len (rec/tot):    114/   114, tx:          0, lsn: 0/5F00A348, prev 0/5F00A310, desc: CHECKPOINT_ONLINE redo 0/5F00A310; tli 13; prev tli 13; fpw true; xid 0:22851; oid 49168; multi 1; offset 0; oldest xid 478 in DB 1; oldest multi 1 in DB 1; oldest/newest commit timestamp xid: 0/0; oldest running xid 22850; online
   rmgr: Database    len (rec/tot):     38/    38, tx:      22850, lsn: 0/5F00A3C0, prev 0/5F00A348, desc: DROP dir 1663/40976

Modify postgresql.auto.conf to set recovery_target_xid for the recovery point (note: switch the original drop archive):

   vim /opt/database/install/data/dn/postgresql.auto.conf

Edit:

   # Do not edit this file manually!
   # It will be overwritten by the ALTER SYSTEM command.
   restore_command = 'cp /data/archive/%f %p'
   recovery_target_xid = '22849'
   #recovery_target = 'immediate'

6. Start the Database

After completing the above steps, start the GBase 8c database:

   gs_om -t start

Log into the database:

   gsql -d postgres -r -p 15400

Verify if the data has been restored. Once confirmed, execute:

   SELECT pg_xlog_replay_resume();

Example output:

   pg_controldata /opt/database/install/data/dn | grep 'Database cluster state'

7. Restore the Cluster

Manually restore and rebuild the cluster:

   mv /opt/database/install/data/dn /data/gbase500/database/install/data/dn1
   mkdir /opt/database/install/data/dn
   cp /data/gbase500/database/install/data/dn1/postgresql.conf /opt/database/install/data/dn
   gs_ctl build -D /opt/database/install/data/dn -b full -M standby

8. Verify Cluster Status

Check the cluster status and log into the database to view the internal status:

   gs_om -t status --detail
   gsql -d postgres -r -p 15400

   SELECT * FROM pg_stat_replication;
   SELECT * FROM pg_stat_activity;

This completes the full recovery process based on XID.