数据库备份对于日常运维至关重要，通过备份恢复工具gs_probackup可以高效、安全、便利的管理数据库日常备份恢复。
本文将演示openGauss数据库使用gs_probackup工具连接master或standby节点在本地或远程如何备份。
1.主从环境搭建
首先在虚拟机快速搭建主从：
主库初始化
gs_initdb --nodename=node1 \ --pgdata=data_master \ --encoding=UTF-8 \ --dbcompatibility='A' \ --username=pw \ --pwpasswd=Mog@2024
主库主要配置参数如下：
vi data_master/postgresql.conf port=6430 listen_addresses = '0.0.0.0'
pg_hba.conf增加如下配置：
host all all 0/0 sha256 host replication all 0/0 sha256 host replication all 0/0 sha256
启动主库
gs_ctl start -D data_master
创建远程备份用户
gsql -r -p 6430 -W 'Mog@2024' -U pw postgres postgres=# create user backup SYSADMIN PASSWORD 'Pwd@2024'; CREATE ROLE
备份用户需要有SYSADMIN权限。
关闭主库
gs_ctl stop -D data_master
postgresql.conf文件增加流复制相关配置：
vi data_master/postgresql.conf remote_read_mode=non_authentication replconninfo1 = 'localhost=192.168.20.200 localport=6432 localheartbeatport =6434 localservice=6435 remotehost=192.168.20.200 remoteport=5432 remoteheartbeatport=5434 remoteservice=5435'
本文在一台虚拟机搭建主从，需要保证使用不同的端口号
再次启动主库，以-M primary模式启动
gs_ctl start -D data_master -M primary
使用gs_basebackup搭建从库
mkdir data_standby chmod 700 data_standby gs_basebackup -D data_standby -h 192.168.20.200 -p 6430 -U backup
修改从库配置：
vi data_standby/postgresql.conf port=5430 listen_addresses = '0.0.0.0' unix_socket_directory='/home/panwei/data_standby' remote_read_mode=non_authentication replconninfo1 = 'localhost=192.168.20.200 localport=5432 localheartbeatport =5434 localservice=5435 remotehost=192.168.20.200 remoteport=6432 remoteheartbeatport=6434 remoteservice=6435'
注意第一个参数，主端口不能冲突，replconninfo1按照镜像映射修改。
启动从库，，以-M standby模式启动
gs_ctl start -D data_standby -M standby
至此主从搭建完成，可以使用gs_ctl query或om命令查看主从状态。
创建测试表，插入数据，便于后面备份恢复演示。
gsql -r -p 6430 -W 'Mog@2024' -U pw postgres postgres=# create table test(id int); CREATE TABLE postgres=# insert into test select generate_series(1,10); INSERT 0 10
2.使用gs_probackup进行备份测试
2.1 初始化备份路径
gs_probackup init -B /tmp/data_backup
2.2 添加实例
- 本地实例，连接master读写节点
gs_probackup add-instance -B /tmp/data_backup \
-D /home/mog/data_master \
--instance=local_master
- 本地实例，连接standby只读节点
gs_probackup add-instance -B /tmp/data_backup \ -D /home/mog/data_standby \ --instance=local_standby
- 远程实例，连接master读写节点
gs_probackup add-instance -B /tmp/data_backup \ -D /home/mog/data_master \ --instance=remote_master \ --remote-proto=ssh --remote-host=192.168.20.200 \ --remote-user=mog --remote-port=22
- 远程实例，连接standby只读节点
gs_probackup add-instance -B /tmp/data_backup \ -D /home/mog/data_standby \ --instance=remote_standby \ --remote-proto=ssh --remote-host=192.168.20.200 \ --remote-user=mog --remote-port=22
远程备份需要配置SSH互信，参考命令如下：
ssh-keygen -t rsa ssh-copy-id -i .ssh/id_rsa.pub mog@192.168.20.200
2.3 本地连接master进行备份
gs_probackup backup -b full \ --instance=local_master \ -B /tmp/data_backup \ -D /home/mog/data_master \ -h 127.0.0.1 -p 6430 -d postgres -U backup -W 'Pwd@2024'
看到如下信息，提示备份成功：
... ... INFO: Syncing backup files to disk INFO: Backup files are synced, time elapsed: 0 INFO: Validating backup SNJU97 INFO: Backup SNJU97 data files are valid INFO: Backup SNJU97 resident size: 673MB INFO: Backup SNJU97 completed
2.4 本地连接standby进行备份
gs_probackup backup -b full \ --instance=local_standby \ -B /tmp/data_backup \ -D /home/mog/data_standby \ -h 127.0.0.1 -p 5430 -d postgres -U backup -W 'Pwd@2024'
看到如下信息，提示备份成功：
... ... INFO: Syncing backup files to disk INFO: Backup files are synced, time elapsed: 0 INFO: Validating backup SNJUAE INFO: Backup SNJUAE data files are valid INFO: Backup SNJUAE resident size: 689MB INFO: Backup SNJUAE completed
2.5 远程连接master进行备份
gs_probackup backup -b full \ --instance=remote_master \ -B /tmp/data_backup \ -D /home/mog/data_master \ --remote-host=192.168.20.200 --remote-user=mog --remote-port=22 \ -h 192.168.20.200 -p 6430 -d postgres -U backup -W 'Pwd@2024'
2.6 远程连接standby进行备份
gs_probackup backup -b full \ --instance=remote_standby \ -B /tmp/data_backup \ -D /home/mog/data_standby \ --remote-host=192.168.20.200 --remote-user=mog --remote-port=22 \ -h 192.168.20.200 -p 5430 -d postgres -U backup -W 'Pwd@2024'
3.常见错误
- 备份时未指定连接相关参数
gs_probackup backup -b full -B /tmp/data_backup \ -D /home/mog/data_master \ --instance=local_master
关键错误信息如下：
ERROR: could not connect to database postgres: connect to server failed: No such file or directory
ERROR: could not connect to database postgres: connect to server failed: Connection refused
需要增加如下连接参数
gs_probackup backup -b full --instance=local_master \
-B /tmp/data_backup \
-D /home/mog/data_master \
-h 127.0.0.1 -p 6430 -d postgres -U backup -W 'Pwd@2024'
- 备份用户权限不够
如果backup用户没有sysadmin权限会提示如下信息
ERROR: query failed: ERROR: must be system admin to run a backup. If in operation mode, you can also be operator admin to run a backup.
CONTEXT: referenced column: pg_start_backup
query was: SELECT pg_catalog.pg_start_backup($1, $2)4.备份注意事项
- 备份用户需要设置sysadmin权限
- 备份需要显式指定连接参数-h -U -d -W
- 远程备份需要显式指定remote-host、remote-port选项
