GBase 8c Backup and Recovery Based on XID

Cong Li - Sep 2 - - Dev Community

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
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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());
Enter fullscreen mode Exit fullscreen mode

Example output:

   pg_xlogfile_name
   --------------------------
   00000001000000000000001F
Enter fullscreen mode Exit fullscreen mode

Then, locate the log file:

   ll /opt/database/install/data/dn/pg_xlog/00000001000000000000001F
Enter fullscreen mode Exit fullscreen mode

2. Switch Archive Logs

Check switchable archive logs:

   SELECT pg_switch_xlog();
Enter fullscreen mode Exit fullscreen mode

Current log location:

   SELECT pg_xlogfile_name(pg_current_xlog_location());
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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'
Enter fullscreen mode Exit fullscreen mode

6. Start the Database

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

   gs_om -t start
Enter fullscreen mode Exit fullscreen mode

Log into the database:

   gsql -d postgres -r -p 15400
Enter fullscreen mode Exit fullscreen mode

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

   SELECT pg_xlog_replay_resume();
Enter fullscreen mode Exit fullscreen mode

Example output:

   pg_controldata /opt/database/install/data/dn | grep 'Database cluster state'
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode 
   SELECT * FROM pg_stat_replication;
   SELECT * FROM pg_stat_activity;
Enter fullscreen mode Exit fullscreen mode

This completes the full recovery process based on XID.

Image
Movavi Video Editor Plus 25 Crack With Activation Key [2025]
Image
Z Miejsca na Marzenia: Jak Stworzyć Idealne Mieszkanie
Image
What Is The Evolution Of Asbestos Attorney Mesothelioma
Image
Macro Recorder 5.9.0 Crack + License Key Free Download
Image
Supercharge Your Productivity Up to 50% with a Free Online Invoice Generator

invoice, receiptgenerator, invoicegenerator
Image
Empower Healthcare with Effortless Automation
Image
12 Companies Are Leading The Way In Bifold Door Repair
Image
The Biggest Problem With Bi Fold Door Repair Near Me, And How You Can Solve It
Image
Office Tool Plus 16.0.18129.20030 Crack + Product Key [2025]
Image
The Benefits of Choosing Vinyl Dog Muzzles
Image
Boost Your Coding Skills: GitHub Repos That Every Developer Should Bookmark in 2025

programming, beginners, learning, github
Image
Join Us for a Free Webinar: Build a Trade Confirmation Document with Creator Templates!

go, programming, webinar, tradeconfirmationdocument
Image
What Attorneys For Asbestos Exposure Experts Want You To Know
Image
How To Explain Asbestos Exposure Attorney To A Five-Year-Old
Image
10 Things We All We Hate About 3 Seater Chaise
Image
JAT Task 3 JIRA
Image
Why Remote Car Key Repair Is Fastly Changing Into The Most Popular Trend In 2023
Image
7 Days to Die 21 Crack Free Download [Mac + Windows]
Image
The Most Hilarious Complaints We've Seen About Sectional Sofa With Chaise
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Terabox Video Player