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

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

• 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

(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"

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"

(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;

(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

(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

• 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

• 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

(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

(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

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

View the backup as shown in the following image:

(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

(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

The following information will be returned:

(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

Check the port in the configuration file:

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

(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

The following information will be returned:

(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

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

View the backup as shown in the following image:

(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

(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/

(4) View the Backup

(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'

(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

Check the port in the configuration file:

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

(7) Start the Database

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

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