DEV Community

GBASE Database
GBASE Database

Posted on

GBASE数据库 | GBase 8c Remote Backup and Restore Practice

#database

GBase 8c database (GBase数据库) allows users to perform full and incremental backups and restores of clusters via a backup server. It also provides the built-in gs_probackup tool for remote backup and restore operations, supporting flexible backup strategies such as incremental backups, scheduled backups, and remote backups and restores.

The gs_probackup tool is used to manage backup and recovery for GBase 8c databases. With this tool, regular backups can be scheduled to ensure that data is recoverable in case of network or machine failures. In addition to backing up the data in the database, it can also back up external directories, such as script files, configuration files, log files, and dumps. This article primarily focuses on configuring a remote backup strategy for GBase 8c databases using the gs_probackup tool.

0. Environment Setup

In this example, based on gs_probackup, the GBase 8c database cluster is backed up to a standby server and restored to a specified directory.

Environment Information:

  • Database server IP: 172.16.71.58
  • Backup server IP: 172.16.200.108

1. Backup Deployment

(1) Configure Passwordless SSH Login for the gbase System User Between the Database and Backup Servers

  • On the database server 172.16.71.58, log in and configure passwordless login: 
  [root@gbase8c-iicp-db01 ~]# su - gbase
  [gbase@gbase8c-iicp-db01 ~]$ ssh-keygen -t rsa
  [gbase@gbase8c-iicp-db01 ~]$ ssh-copy-id -i gbase@172.16.200.108
Enter fullscreen mode Exit fullscreen mode
  • On the backup server 172.16.200.108, log in and configure passwordless login: 
  [root@ZSC-GB8C-NODE1 ~]# su - gbase
  [gbase@ZSC-GB8C-NODE1 ~]$ ssh-keygen -t rsa
  [gbase@ZSC-GB8C-NODE1 ~]$ ssh-copy-id -i gbase@172.16.71.58
Enter fullscreen mode Exit fullscreen mode

(2) Modify Database Parameters and Configure Archive Log Directory

  • On the database server 172.16.71.58, modify the parameters: 
  [root@gbase8c-iicp-db01 ~]# su - gbase
  [gbase@gbase8c-iicp-db01 ~]$ gs_guc reload -N all -I all -c "archive_mode=on"
  [gbase@gbase8c-iicp-db01 ~]$ gs_guc reload -N all -I all -c "archive_timeout=1800"
  [gbase@gbase8c-iicp-db01 ~]$ gs_guc reload -N all -I all -c "archive_command = 'scp %p gbase@172.16.200.108:/data/mpp/backup/zhck/archive_dir/%f'"
  [gbase@gbase8c-iicp-db01 ~]$ gs_guc reload -N all -I all -c "enable_cbm_tracking=on"
Enter fullscreen mode Exit fullscreen mode

Parameters:

  • archive_mode: Determines whether archiving is enabled.

    • Possible values: on (enable), off (disable)
    • Default: off

  • archive_timeout: Defines the archive timeout period.

    • Range: Integer (0–1073741823) in seconds. 0 disables this feature.
    • Default: 0

  • archive_command: Defines the archive WAL log command. It is recommended to use an absolute path.

    • Default: disabled (feature disabled)

  • enable_cbm_tracking: This parameter must be enabled for incremental backups. Disabling it can cause backup failure.

    • Possible values: on (enable tracking), off (disable tracking)
    • Default: off

(3) Enable Remote Database Access on the Backup Server

  • On the database server 172.16.71.58, enable remote access for the backup server: 
  [root@gbase8c-iicp-db01 ~]# su - gbase
  [gbase@gbase8c-iicp-db01 ~]$ gs_guc reload -N all -I all -h "host replication all 172.16.200.108/32 md5"
Enter fullscreen mode Exit fullscreen mode

(4) Create and Grant Backup User Permissions

  • On the backup server 172.16.200.108, test the database login and create a backup user: 
  [root@gbase8c-iicp-db01 ~]# su - gbase
  [gbase@gbase8c-iicp-db01 ~]$ gsql -d postgres -h 172.16.71.58
  postgres=# create user dba with password 'gbase,123' sysadmin;
  postgres=# grant all privileges to dba;
Enter fullscreen mode Exit fullscreen mode

(5) Create Archive Directory on the Backup Server

  • On the backup server 172.16.200.108, create the backup archive directory: 
  [root@gbase8c-iicp-db01 ~]# su - gbase
  [gbase@ZSC-GB8C-NODE1 ~]# mkdir -p /data/mpp/backup/zhck/iicp
Enter fullscreen mode Exit fullscreen mode

(6) Initialize Backup

  • Switch to the gbase user and initialize the backup directory: 
  [root@gbase8c-iicp-db01 ~]# su - gbase
  [gbase@ZSC-GB8C-NODE1 ~]# gs_probackup init -B /data/mpp/backup/zhck/iicp
Enter fullscreen mode Exit fullscreen mode
  • Add a backup instance and generate the pg_probackup.conf configuration file: 
  [gbase@ZSC-GB8C-NODE1 ~]# gs_probackup add-instance -B /data/mpp/backup/zhck/iicp -D /home/gbase/data/dn1 --instance=gbase_zhck --remote-host=172.16.71.58 --remote-user=gbase
Enter fullscreen mode Exit fullscreen mode
  • Set retention policy for backups (e.g., 30 days): 
  [gbase@ZSC-GB8C-NODE1 ~]# gs_probackup set-config -B /data/mpp/backup/zhck/iicp --instance=gbase_zhck --retention-redundancy=30 --retention-window=30
Enter fullscreen mode Exit fullscreen mode

(7) Full Backup

  • Perform a full backup: 
  [root@gbase8c-iicp-db01 ~]# su - gbase
  [gbase@ZSC-GB8C-NODE1 ~]# cd /data/mpp/backup/zhck
  [gbase@ZSC-GB8C-NODE1 ~]# gs_probackup backup -B /data/mpp/backup/zhck/iicp -b FULL -h 172.16.71.58 -p 15432 -U gbase -W 'gbase,123' --instance=gbase_zhck --delete-expired -d postgres --remote-host=172.16.71.58 --remote-user=gbase
Enter fullscreen mode Exit fullscreen mode

(8) Incremental Backup

  • Perform an incremental backup: 
  [root@gbase8c-iicp-db01 ~]# su - gbase
  [gbase@ZSC-GB8C-NODE1 ~]# cd /data/mpp/backup/zhck
  [gbase@ZSC-GB8C-NODE1 ~]# gs_probackup backup -B /data/mpp/backup/zhck/iicp -b PTRACK -h 172.16.71.58 -p 15432 -U gbase -W 'gbase,123' --instance=gbase_zhck --delete-expired -d postgres --remote-host=172.16.71.58 --remote-user=gbase
Enter fullscreen mode Exit fullscreen mode

2. Data Recovery in GBase 8c Database

In this example, we recover data to a separate directory on the current host. In a production environment, if the cluster experiences incorrect operations or data corruption due to other factors, it is not recommended to perform the recovery directly on the original cluster. This could potentially cause secondary damage to the database. It is advised to restore data to another node, verify the data, and then import the data back into the production cluster.

2.1 Full Recovery

(1) View the Backup

[root@gbase8c-iicp-db01 ~]# su - gbase
[gbase@ZSC-GB8C-NODE1 ~]# cd /data/mpp/backup/zhck
[gbase@ZSC-GB8C-NODE1 ~]# gs_probackup show -B /data/mpp/backup/zhck/iicp
Enter fullscreen mode Exit fullscreen mode

View the backup as shown in the following image:

Image description

(2) Create a Recovery Directory on the Database Server

It is recommended to restore the backup to a different server, such as a pre-release or near-production environment.

Log in to the database server 172.16.71.58 and create a recovery directory:

[root@gbase8c-iicp-db01 ~]# su - gbase
[gbase@ZSC-GB8C-NODE1 ~]# mkdir /data/mpp/backup/zhck/restore_test -p
Enter fullscreen mode Exit fullscreen mode

(3) Restore to a Full Backup at 2024-12-04 19:04:47 on Database Server 172.16.71.58

[gbase@ZSC-GB8C-NODE1 ~]# gs_probackup restore -B /data/mpp/backup/zhck/iicp -D /data/mpp/backup/zhck/restore_test -l=SNYUPG --instance=gbase_zhck --remote-host=172.16.71.58 --remote-user=gbase
Enter fullscreen mode Exit fullscreen mode

The following information will be returned:

Image description

(4) Change the Database Port to Avoid Conflicts

Log in to the database server 172.16.71.58:

[root@gbase8c-iicp-db01 ~]# su - gbase
[gbase@ZSC-GB8C-NODE1 ~]# cd /data/mpp/backup/zhck/restore_test
Enter fullscreen mode Exit fullscreen mode

Check the port in the configuration file:

[gbase@gbase8c-iicp-db01 restore_test]$ cat postgresql.conf
...
port = 15432
...
Enter fullscreen mode Exit fullscreen mode

(5) Start the Database

Log in to the database server 172.16.71.58:

[root@gbase8c-iicp-db01 ~]# su - gbase
[gbase@gbase8c-iicp-db01 restore_test]$ gs_ctl start -D /data/mpp/backup/zhck/restore_test
Enter fullscreen mode Exit fullscreen mode

The following information will be returned:

Image description

(6) Log in to the Database to Verify the Data

Log in to the database server 172.16.71.58:

[root@gbase8c-iicp-db01 ~]# su - gbase
[gbase@gbase8c-iicp-db01 restore_test]$ gsql -d postgres -p 15432 -r
Enter fullscreen mode Exit fullscreen mode

Image description

2.2 Point-in-Time Incremental Recovery

Similarly, restore to a separate directory on the current host. In production environments, data recovery should not be performed directly on the original cluster, as this may lead to further damage. It is advisable to restore the data to another node, verify it, and then import the data into the production cluster.

(1) View the Backup

[root@gbase8c-iicp-db01 ~]# su - gbase
[gbase@ZSC-GB8C-NODE1 ~]# cd /data/mpp/backup/zhck
[gbase@ZSC-GB8C-NODE1 ~]# gs_probackup show -B /data/mpp/backup/zhck/iicp
Enter fullscreen mode Exit fullscreen mode

View the backup as shown in the following image:

Image description

(2) Merge Incremental Backup SNYUUR to SNYUPG

[gbase@ZSC-GB8C-NODE1 zhck]$ gs_probackup merge -B /data/mpp/backup/zhck/iicp --instance=gbase_zhck -i SNYUUR --progres
Enter fullscreen mode Exit fullscreen mode

(3) Copy the Archive Logs to the Backup's WAL Directory

This step may be required depending on your backup configuration. If the WAL archive directory is already set to archive to the physical backup's WAL directory, you can skip this step.

[gbase@ZSC-GB8C-NODE1 gbase_zhck]$ cp /data/mpp/backup/zhck/archive_dir/* /data/mpp/backup/zhck/iicp/wal/gbase_zhck/
Enter fullscreen mode Exit fullscreen mode

(4) View the Backup

Image description

(5) Restore to the Specific Time Point 2024-12-04 19:00:00 on the Database Server 172.16.71.58 to the /data/mpp/backup/zhck/restore_test Directory

[root@gbase8c-iicp-db01 ~]# su - gbase
[gbase@ZSC-GB8C-NODE1 ~]# cd /data/mpp/backup/zhck
[gbase@ZSC-GB8C-NODE1 zhck]$ gs_probackup restore -B /data/mpp/backup/zhck/iicp -D /data/mpp/backup/zhck/restore_test --instance=gbase_zhck --remote-host=172.16.71.58 --remote-user=gbase --recovery-target-time='2024-12-04 19:00:00'
Enter fullscreen mode Exit fullscreen mode

(6) Change the Database Port to Avoid Conflicts

Log in to the database server 172.16.71.58:

[root@gbase8c-iicp-db01 ~]# su - gbase
[gbase@ZSC-GB8C-NODE1 ~]# cd /data/mpp/backup/zhck/restore_test
Enter fullscreen mode Exit fullscreen mode

Check the port in the configuration file:

[gbase@gbase8c-iicp-db01 restore_test]$ cat postgresql.conf
...
port = 15432
...
Enter fullscreen mode Exit fullscreen mode

(7) Start the Database

[gbase@gbase8c-iicp-db01 restore_test]$ gs_ctl start -D /data/mpp/backup/zhck/restore_test
Enter fullscreen mode Exit fullscreen mode

(8) Log in to the Database to Verify the Data

Log in to the database server 172.16.71.58. Once data verification is complete, the recovery is finished.

Conclusion

In summary, GBase 8c's backup and recovery functions play a critical role in ensuring data security and reliability. To meet the diverse needs of users and different scenarios, the product offers flexible backup strategies and media options. By choosing GBase 8c, users can enjoy the benefits of its backup and recovery features, ensuring data security, reliability, and the stable operation of the database system.

Top comments (0)

Read next

thekbbohara profile image

Install Arch with swapfile baby.

Kb Bohara -

dialoma profile image

My Blogging Dawn

Diego López -

infra_insider profile image

Terraform Smarter, Not Harder: The Power of Modular Infrastructure as Code

Jared Engelkemier -

ivicac profile image

ALways nit. to hear nice words....

Ivica Čardić -