TheLazy.Dev
Portfolio GitHub

Blog

database

GBase 8c Backup and Recovery Based on XID

congcong

Cong Li

Posted on September 2, 2024

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

💖 💪 🙅 🚩
congcong
Cong Li

Posted on September 2, 2024

Join Our Newsletter. No Spam, Only the good stuff.

Sign up to receive the latest update from our blog.

Related

Why we Built a Mini-Language for a Golang Hackathon
go Why we Built a Mini-Language for a Golang Hackathon

November 29, 2024

Mastering AWS Microservices Architecture for High-Traffic Systems: Interview Preparation
aws Mastering AWS Microservices Architecture for High-Traffic Systems: Interview Preparation

November 29, 2024

How to Set Up Authorization in a Bookstore Management System with Go, HTMX, and Permit.io
authorization How to Set Up Authorization in a Bookstore Management System with Go, HTMX, and Permit.io

November 29, 2024

Using CloudEvents in Go
go Using CloudEvents in Go

November 29, 2024

Jupyter Notebooks in Docker
datascience Jupyter Notebooks in Docker

November 29, 2024