openGauss6.0备份恢复大全，都在这里

前言

本文总结了openGauss6.0物理备份和逻辑备份的案例，分享给大家

📣 1.备份概述

数据备份是保护数据安全的重要手段之一，为了更好的保护数据安全，openGauss数据库支持三种备份恢复类型，以及多种备份恢复方案，备份和恢复过程中提供数据的可靠性保障机制。
备份与恢复类型可分为逻辑备份与恢复、物理备份与恢复、闪回恢复。

逻辑备份与恢复：通过逻辑导出对数据进行备份，逻辑备份只能基于备份时刻进行数据转储，所以恢复时也只能恢复到备份时保存的数据。对于故障点和备份点之间的数据，逻辑备份无能为力，逻辑备份适合备份那些很少变化的数据，当这些数据因误操作被损坏时，可以通过逻辑备份进行快速恢复。如果通过逻辑备份进行全库恢复，通常需要重建数据库，导入备份数据来完成，对于可用性要求很高的数据库，这种恢复时间太长，通常不被采用。由于逻辑备份具有平台无关性，所以更为常见的是，逻辑备份被作为一个数据迁移及移动的主要手段。

物理备份与恢复：通过物理文件拷贝的方式对数据库进行备份，以磁盘块为基本单位将数据从主机复制到备机。通过备份的数据文件及归档日志等文件，数据库可以进行完全恢复。物理备份速度快，一般被用作对数据进行备份和恢复，用于全量备份的场景。通过合理规划，可以低成本进行备份与恢复。

闪回恢复：利用回收站的闪回恢复删除的表。数据库的回收站功能类似于windows系统的回收站，将删除的表信息保存到回收站中。利用MVCC机制闪回恢复到指定时间点或者CSN点。

✨ 2.1 gs_dump

gs_dump是openGauss用于导出数据库相关信息的工具，用户可以自定义导出一个数据库或其中的对象（模式、表、视图等），回收站对象除外。支持导出的数据库可以是默认数据库postgres，也可以是自定义数据库。gs_dump工具在进行数据导出时，其他用户可以访问openGauss数据库（读或写），支持导出完整一致的数据。pg_dump只备份数据库集群中的某个数据库的数据，它不会导出角色和表空间相关的信息，因为这些信息是整个数据库集群共用的，不属于某个单独的数据库。

gs_dump支持将数据库信息导出至纯文本格式的SQL脚本文件或其他归档文件中。

✨ 2.2 gs_dump

gs_dumpall是openGauss用于导出所有数据库相关信息工具，导出特点和gs_dump非常相似，导出openGauss数据库的所有数据，包括默认数据库postgres的数据、自定义数据库的数据以及openGauss所有数据库公共的全局对象。gs_dumpall在导出openGauss所有数据库时分为两部分：

gs_dumpall自身对所有数据库公共的全局对象进行导出，包括有关数据库用户和组、表空间以及属性（例如，适用于数据库整体的访问权限）信息。gs_dumpall通过调用gs_dump来完成openGauss中各数据库的SQL脚本文件导出，该脚本文件包含将数据库恢复为其保存时的状态所需要的全部SQL语句。

✨ 2.3 gs_restore

gs_restore是openGauss提供的针对gs_dump导出数据的导入工具。通过此工具可将由gs_dump生成的导出文件进行导入。主要功能包含：
1)导入到数据库：如果连接参数中指定了数据库，则数据将被导入到指定的数据库中。其中，并行导入必须指定连接的密码。导入时生成列会自动更新，并像普通列一样保存。
2)导入到脚本文件：如果未指定导入数据库，则创建包含重建数据库所必须的SQL语句脚本并写入到文件或者标准输出。等效于直接使用gs_dump导出为纯文本格式

✨ 2.4 实战案例

##导出为文本格式：执行gs_dump，导出dumpdb 数据库全量信息，导出的DUMPDB_backup.sql文件格式为纯文本格式。这种的可以编辑，但是不建议。[omm@opengauss ~]$ gs_dump -U jeames -W jeames@123 -f /home/omm/dump/DUMPDB_backup.sql -p 15400 dumpdb -F pgs_dump[port='15400'][dumpdb][2023-04-15 23:51:41]: The total objects number is 439.gs_dump[port='15400'][dumpdb][2023-04-15 23:51:41]: [100.00%] 439 objects have been dumped.gs_dump[port='15400'][dumpdb][2023-04-15 23:51:41]: dump database dumpdb successfullygs_dump[port='15400'][dumpdb][2023-04-15 23:51:41]: total time: 2968 ms
##导出为归档格式1）执行gs_dump，导出dumpdb数据库全量信息，导出的DUMPDB_backup.tar文件格式为tar格式。[omm@opengauss ~]$ gs_dump -U dumpuser -W Gauss_234 -f /home/omm/dump/DUMPDB_backup.tar -p 15400 dumpdb -F tgs_dump[port='15400'][dumpdb][2023-04-15 23:52:56]: The total objects number is 439.gs_dump[port='15400'][dumpdb][2023-04-15 23:52:56]: [100.00%] 439 objects have been dumped.gs_dump[port='15400'][dumpdb][2023-04-15 23:52:56]: dump database dumpdb successfullygs_dump[port='15400'][dumpdb][2023-04-15 23:52:56]: total time: 2902 ms
2）执行gs_dump，导出dumpdb数据库全量信息，导出的DUMPDB_backup.dmp文件格式为自定义归档格式。[omm@opengauss ~]$ gs_dump -U dumpuser -W Gauss_234 -f /home/omm/dump/DUMPDB_backup.dmp -p 15400 dumpdb -F cgs_dump[port='15400'][dumpdb][2023-04-15 23:54:38]: The total objects number is 439.gs_dump[port='15400'][dumpdb][2023-04-15 23:54:39]: [100.00%] 439 objects have been dumped.gs_dump[port='15400'][dumpdb][2023-04-15 23:54:39]: dump database dumpdb successfullygs_dump[port='15400'][dumpdb][2023-04-15 23:54:39]: total time: 2787 ms
3）导出指定schema下面的表执行gs_dump，导出dumpdb数据库的表（或视图、或序列、或外表）对象，例如表test1。[omm@opengauss dump]$ gs_dump -U dumpuser -W Gauss_234 -f /home/omm/dump/test1_bkp.sql -t public.test1 -p 15400 dumpdbgs_dump[port='15400'][dumpdb][2023-04-15 23:56:46]: The total objects number is 423.gs_dump[port='15400'][dumpdb][2023-04-15 23:56:46]: [100.00%] 423 objects have been dumped.gs_dump[port='15400'][dumpdb][2023-04-15 23:56:46]: dump database dumpdb successfullygs_dump[port='15400'][dumpdb][2023-04-15 23:56:46]: total time: 2051 ms
## gs_dumpall导出使用gs_dumpall一次导出openGauss的所有数据库[omm@opengauss ~]$ gs_dumpall -f /home/omm/dump/bkpall.sql -p 15400gs_dump[port='15400'][dbname='dumpdb'][2023-04-15 23:58:00]: The total objects number is 439.gs_dump[port='15400'][dbname='dumpdb'][2023-04-15 23:58:00]: [100.00%] 439 objects have been dumped.gs_dump[port='15400'][dbname='dumpdb'][2023-04-15 23:58:00]: dump database dbname='dumpdb' successfullygs_dump[port='15400'][dbname='dumpdb'][2023-04-15 23:58:00]: total time: 2487 msgs_dump[port='15400'][dbname='mydb'][2023-04-15 23:58:02]: The total objects number is 427.gs_dump[port='15400'][dbname='mydb'][2023-04-15 23:58:02]: [100.00%] 427 objects have been dumped.gs_dump[port='15400'][dbname='mydb'][2023-04-15 23:58:02]: dump database dbname='mydb' successfullygs_dump[port='15400'][dbname='mydb'][2023-04-15 23:58:02]: total time: 2496 msgs_dump[port='15400'][dbname='postgres'][2023-04-15 23:58:05]: The total objects number is 429.gs_dump[port='15400'][dbname='postgres'][2023-04-15 23:58:05]: [100.00%] 429 objects have been dumped.gs_dump[port='15400'][dbname='postgres'][2023-04-15 23:58:05]: dump database dbname='postgres' successfullygs_dump[port='15400'][dbname='postgres'][2023-04-15 23:58:05]: total time: 2532 msgs_dumpall[port='15400'][2023-04-15 23:58:05]: dumpall operation successfulgs_dumpall[port='15400'][2023-04-15 23:58:05]: total time: 7666 ms
3.gs_restore导入在此之前，对于导出的单个数据库，需要新建一个空的数据库进行恢复。[omm@opengauss ~]$ gsql -d postgres -p 15400openGauss=# create database new_dmpdb1;openGauss=# create database new_dmpdb2;openGauss=# create database new_dmpdb3;
1）执行gs_restore，将导出的DUMPDB_backup.tar文件（tar格式）导入到new_dmpdb2数据库。[omm@opengauss ~]$ gs_restore /home/omm/dump/DUMPDB_backup.tar -p 15400 -d new_dmpdb2;
2）恢复指定表数据执行gs_restore，使用自定义归档格式的DUMPDB_backup.dmp文件来导入PUBLIC模式下表test1。[omm@opengauss ~]$ gs_restore /home/omm/dump/DUMPDB_backup.dmp -p 15400 -d new_dmpdb1 -n public -t test1

📣 3.物理备份与恢复

物理备份与恢复适用于数据量大的场景，主要用于全量数据备份恢复，也可对整个数据库中的WAL归档日志和运行日志进行备份。openGauss提供了三种物理备份与恢复相关的工具：gs_backup、gs_basebackup和gs_probackup。三个工具的对比见下图。

✨ 3.1 gs_probackup

gs_probackup工具。它对openGauss 实例进行定期备份。可用于备份单机数据库或者数据库实例主节点，为物理备份。可备份外部目录的内容，如脚本文件、配置文件、日志文件、dump文件等。支持增量备份、定期备份和远程备份。增量备份时间相对于全量备份时间比较短，只需要备份修改的文件。当前默认备份是数据目录，如果表空间不在数据目录，需要手动指定备份的表空间目录进行备份。当前只支持在主机上执行备份。

✨ 3.2 实战案例

1.配置。使用PTRACK增量备份，需在postgresql.conf中手动添加参数“enable_cbm_tracking = on”。-D后面携带的是$PGDATA，需要根据实际安装环境替换。也可以在postgresql.conf中手动添加参数“enable_cbm_tracking = on”，然后重启数据库。[omm@opengauss ~]$ gs_guc reload -D /app/openGauss/install/data/dn -c "enable_cbm_tracking = on"
2.初始化备份路径。会在指定路径下生成backups和wal两个文件夹[omm@opengauss ~]$ gs_probackup init -B /home/omm/probkpINFO: Backup catalog '/home/omm/probkp' successfully inited
[omm@opengauss probkp]$ lldrwx------ 2 omm dbgrp 6 Apr 16 00:26 backupsdrwx------ 2 omm dbgrp 6 Apr 16 00:26 wal
3.初始化备份实例在备份路径_backup-path_内初始化一个新的备份实例，并生成pg_probackup.conf配置文件，该文件保存了指定数据目录_pgdata-path_的gs_probackup设置。[omm@opengauss ~]$ gs_probackup add-instance -B /home/omm/probkp -D /app/openGauss/install/data/dn --instance instance_localINFO: Instance 'instance_local' successfully inited
[root@opengauss ~]# find / -name pg_probackup.conf/home/omm/probkp/backups/instance_local/pg_probackup.conf[root@opengauss ~]# more /home/omm/probkp/backups/instance_local/pg_probackup.conf# Backup instance informationpgdata = /app/openGauss/install/data/dnsystem-identifier = 3423356205360426
4.全备将指定的连接、压缩、日志等相关设置添加到pg_probackup.conf配置文件中[omm@opengauss ~]$ gs_probackup set-config -B /home/omm/probkp --instance=instance_local
显示位于备份目录中的pg_probackup.conf配置文件的内容。#可以通过指定–format=json选项，#以json格式显示。默认情况下，显示为纯文本格式。[omm@opengauss ~]$ gs_probackup show-config -B /home/omm/probkp --instance=instance_local --format json{ "pgdata": "/app/openGauss/install/data/dn", "system-identifier": "3423356205360426", "pgdatabase": "postgres", "archive-timeout": "5min", "log-level-console": "LOG", "log-level-file": "OFF", "log-filename": "pg_probackup.log", "log-rotation-size": "0TB", "log-rotation-age": "0d", "retention-redundancy": "0", "retention-window": "0", "wal-depth": "0", "compress-algorithm": "none", "compress-level": "1", "remote-proto": "ssh", "enable-dss": "false", "instance-id": "-1"}

[omm@opengauss ~]$ gs_probackup backup -B /home/omm/probkp --instance instance_local -d postgres -b FULL -p 15400
INFO: Backup start, gs_probackup version: 2.4.2, instance: instance_local, backup ID: RT6RZG, backup mode: FULL, wal mode: STREAM, remote: false, compress-algorithm: none, compress-level: 1LOG: Backup destination is initializedLOG: This openGauss instance was initialized with data block checksums. Data block corruption will be detectedLOG: Database backup startLOG: started streaming WAL at 0/3000000 (timeline 1)[2023-04-16 10:23:40]: check identify system success[2023-04-16 10:23:40]: send START_REPLICATION 0/3000000 success[2023-04-16 10:23:40]: keepalive message is received[2023-04-16 10:23:40]: keepalive message is receivedINFO: PGDATA size: 668MBINFO: Start transferring data filesLOG: Creating page header map "/home/omm/probkp/backups/instance_local/RT6RZG/page_header_map"[2023-04-16 10:23:46]: keepalive message is received[2023-04-16 10:23:51]: keepalive message is received[2023-04-16 10:23:56]: keepalive message is received[2023-04-16 10:24:01]: keepalive message is receivedINFO: Data files are transferred, time elapsed: 21sINFO: wait for pg_stop_backup()INFO: pg_stop backup() successfully executedLOG: stop_lsn: 0/30001E8LOG: Looking for LSN 0/30001E8 in segment: 000000010000000000000003LOG: Found WAL segment: /home/omm/probkp/backups/instance_local/RT6RZG/database/pg_xlog/000000010000000000000003LOG: Thread [0]: Opening WAL segment "/home/omm/probkp/backups/instance_local/RT6RZG/database/pg_xlog/000000010000000000000003"LOG: Found LSN: 0/30001E8LOG: finished streaming WAL at 0/4000000 (timeline 1)LOG: Getting the Recovery Time from WALLOG: Thread [0]: Opening WAL segment "/home/omm/probkp/backups/instance_local/RT6RZG/database/pg_xlog/000000010000000000000003"INFO: Syncing backup files to diskINFO: Backup files are synced, time elapsed: 0INFO: Validating backup RT6RZGINFO: Backup RT6RZG data files are validINFO: Backup RT6RZG resident size: 684MBINFO: Backup RT6RZG completed
#记住备份ID，恢复的时候需要使用，backup ID:RT6RZG#这里-d 指定要连接的数据库名称。该连接仅用于管理备份进程，因此您可以连接到任何现有的数据库

##查看备份情况[omm@opengauss ~]$ gs_probackup show -B /home/omm/probkp --instance instance_local
5.增备这里我们对数据库进行一些变更后再进行增量备份。[omm@opengauss ~]$ gsql -d dumpdb -U dumpuser -p 15400Password for user dumpuser: Gauss_234
dumpdb=> \d List of relations Schema | Name | Type | Owner | Storage--------+-------+-------+----------+---------------------------------- public | test1 | table | dumpuser | {orientation=row,compression=no} public | test2 | table | dumpuser | {orientation=row,compression=no} public | test3 | table | dumpuser | {orientation=row,compression=no}
dumpdb=> select * from test2; id----(0 rows)
dumpdb=> insert into test2 values(111);INSERT 0 1dumpdb=> select * from test2; id----- 111(1 row)
增量备份gs_probackup backup -B /home/omm/probkp --instance instance_local -d postgres -b PTRACK -p 15400INFO: Backup start, gs_probackup version: 2.4.2, instance: instance_local, backup ID: RT6STR, backup mode: PTRACK, wal mode: STREAM, remote: false, compress-algorithm: none, compress-level: 1LOG: Backup destination is initializedLOG: This openGauss instance was initialized with data block checksums. Data block corruption will be detectedLOG: Database backup startLOG: Latest valid FULL backup: RT6RZGWARNING: Backup RT6ST6 has status: ERROR. Cannot be a parent.INFO: Parent backup: RT6RZGLOG: started streaming WAL at 0/5000000 (timeline 1)[2023-04-16 10:41:51]: check identify system success[2023-04-16 10:41:51]: send START_REPLICATION 0/5000000 success[2023-04-16 10:41:51]: keepalive message is received[2023-04-16 10:41:51]: keepalive message is receivedINFO: PGDATA size: 668MBLOG: Current tli: 1LOG: Parent start_lsn: 0/3000028LOG: start_lsn: 0/5000028INFO: Extracting pagemap of changed blocksINFO: change bitmap start lsn location is 0/3000028INFO: change bitmap end lsn location is 00000000/05000028INFO: Pagemap successfully extracted, time elapsed: 0 secINFO: Start transferring data filesLOG: Creating page header map "/home/omm/probkp/backups/instance_local/RT6STR/page_header_map"INFO: Data files are transferred, time elapsed: 2sINFO: wait for pg_stop_backup()INFO: pg_stop backup() successfully executedLOG: stop_lsn: 0/50001E8LOG: Looking for LSN 0/50001E8 in segment: 000000010000000000000005LOG: Found WAL segment: /home/omm/probkp/backups/instance_local/RT6STR/database/pg_xlog/000000010000000000000005LOG: Thread [0]: Opening WAL segment "/home/omm/probkp/backups/instance_local/RT6STR/database/pg_xlog/000000010000000000000005"LOG: Found LSN: 0/50001E8LOG: finished streaming WAL at 0/6000000 (timeline 1)LOG: Getting the Recovery Time from WALLOG: Thread [0]: Opening WAL segment "/home/omm/probkp/backups/instance_local/RT6STR/database/pg_xlog/000000010000000000000005"INFO: Syncing backup files to diskINFO: Backup files are synced, time elapsed: 0INFO: Validating backup RT6STRINFO: Backup RT6STR data files are validINFO: Backup RT6STR resident size: 273MBINFO: Backup RT6STR completed

#增量备份成功后，我们可以查询到对应的这条记录[omm@opengauss ~]$ gs_probackup show -B /home/omm/probkp --instance instance_local

这里我们对数据库再进行一些变更后再进行增量备份。[omm@opengauss ~]$ gsql -d dumpdb -U dumpuser -p 15400Password for user dumpuser: Gauss_234
dumpdb=> \d List of relations Schema | Name | Type | Owner | Storage--------+-------+-------+----------+---------------------------------- public | test1 | table | dumpuser | {orientation=row,compression=no} public | test2 | table | dumpuser | {orientation=row,compression=no} public | test3 | table | dumpuser | {orientation=row,compression=no}
dumpdb=> select * from test2; id----- 111(1 row)
dumpdb=> insert into test2 values(222);INSERT 0 1dumpdb=> select * from test2; id----- 111 222(2 rows)
增量备份[omm@opengauss ~]$ gs_probackup backup -B /home/omm/probkp --instance instance_local -d postgres -b PTRACK -p 15400

## 将增量备份和全量备份合并，将指定的增量备份与其父完全备份之间的所有增量备份合并到父完全备份。父完全备份将接收所有合并的数据，而已合并的增量备份将作为冗余被删除并所有增量备份到全备中，只需要指定最后一个增量备份的idgs_probackup merge -B /home/omm/probkp --instance instance_local -i RT6TNMgs_probackup merge -B /home/omm/probkp --instance instance_local -i RNABP0
[omm@opengauss ~]$ gs_probackup merge -B /home/omm/probkp --instance instance_local -i RT6TNMINFO: Merge startedINFO: Merging backup RT6TNM with parent chainINFO: Validate parent chain for backup RT6TNMINFO: Validating backup RT6RZGINFO: Backup RT6RZG data files are validINFO: Validating backup RT6STRINFO: Backup RT6STR data files are validINFO: Validating backup RT6TNMINFO: Backup RT6TNM data files are validLOG: Restore directories and symlinks... in /home/omm/probkp/backups/instance_local/RT6RZG/databaseINFO: Start merging backup filesLOG: Creating page header map "/home/omm/probkp/backups/instance_local/RT6RZG/page_header_map_tmp"INFO: Backup files are successfully merged, time elapsed: 7sINFO: Delete: RT6STR 2023-04-16 10:41:53+08INFO: Delete: RT6TNM 2023-04-16 10:59:49+08LOG: Rename /home/omm/probkp/backups/instance_local/RT6RZG to /home/omm/probkp/backups/instance_local/RT6TNMINFO: Rename merged full backup RT6RZG to RT6TNMINFO: Validating backup RT6TNMINFO: Backup RT6TNM data files are validINFO: Merge of backup RT6TNM completed

6.误删除这里我们直接将dumpdb删除，然后使用合并后的备份文件恢复[omm@opengauss ~]$ gsql -d postgres -p 15400openGauss=# drop database dumpdb;
7.恢复。## 使用restore子命令前，应先停止gaussdb进程。[omm@opengauss ~]$ gs_ctl stop -D /app/openGauss/install/data/dn[2023-04-16 11:08:28.601][33829][][gs_ctl]: gs_ctl stopped ,datadir is /app/openGauss/install/data/dnwaiting for server to shut down.... doneserver stopped
## 执行restore恢复到误删前的数据状态。[omm@opengauss ~]$ cd /app/openGauss/install/data/dn[omm@opengauss ~]$ rm -rf *[omm@opengauss ~]$ gs_probackup restore -B /home/omm/probkp --instance instance_local -D /app/openGauss/install/data/dnLOG: Restore begin.LOG: there is no file tablespace_mapLOG: check tablespace directories of backup RT6TNMLOG: check external directories of backup RT6TNMINFO: Validating backup RT6TNMINFO: Backup RT6TNM data files are validLOG: Thread [1]: Opening WAL segment "/home/omm/probkp/backups/instance_local/RT6TNM/database/pg_xlog/000000010000000000000007"INFO: Backup RT6TNM WAL segments are validINFO: Backup RT6TNM is valid.INFO: Restoring the database from backup at 2023-04-16 10:59:46+08LOG: there is no file tablespace_mapLOG: Restore directories and symlinks... in /app/openGauss/install/data/dnINFO: Start restoring backup files. PGDATA size: 684MBLOG: Start thread 1INFO: Backup files are restored. Transfered bytes: 1095MB, time elapsed: 3sINFO: Restore incremental ratio (less is better): 160% (1095MB/684MB)INFO: Syncing restored files to diskINFO: Restored backup files are synced, time elapsed: 0INFO: Restore of backup RT6TNM completed.
8.启动数据库登陆[omm@opengauss ~]$ gs_ctl start -D /app/openGauss/install/data/dn[omm@opengauss dn]$ gsql -d dumpdb -U dumpuser -p 15400Password for user dumpuser:gsql ((openGauss 5.0.0 build a07d57c3) compiled at 2023-03-29 03:07:56 commit 0 last mr )Non-SSL connection (SSL connection is recommended when requiring high-security)Type "help" for help.
dumpdb=> \d List of relations Schema | Name | Type | Owner | Storage--------+-------+-------+----------+---------------------------------- public | test1 | table | dumpuser | {orientation=row,compression=no} public | test2 | table | dumpuser | {orientation=row,compression=no} public | test3 | table | dumpuser | {orientation=row,compression=no}(3 rows)
dumpdb=> select * from test2; id----- 111 222(2 rows)

注意：以上备份的全部被恢复了

8.清理备份[omm@opengauss ~]$ gs_probackup del-instance -B /home/omm/probkp --instance instance_localINFO: Delete: RT6TNM 2023-04-16 10:59:49+08INFO: Delete: RT6ST6 1970-01-01 08:00:00+08INFO: Delete: RT6RNQ 1970-01-01 08:00:00+08INFO: Delete: RT6R6I 1970-01-01 08:00:00+08INFO: Delete: RT6150 1970-01-01 08:00:00+08INFO: Delete: RT60SX 1970-01-01 08:00:00+08INFO: Delete: RT60OY 1970-01-01 08:00:00+08INFO: Delete: RT60LI 1970-01-01 08:00:00+08INFO: Instance 'instance_local' successfully deleted


其他命令1.查看版本[omm@opengauss ~]$ gs_probackup versiongs_probackup (openGauss 5.0.0 build a07d57c3) compiled at 2023-03-29 03:07:56 commit 0 last mr
2.远程备份远程备份需要添加--remote-host参数,连接参数：-d,-p,-U,-Wgs_probackup backup -B /backup --instance OG2 -b FULL -d postgres -p 15400 -remote-host 10.0.0.101 -U jack -W Test@123