当数据库崩溃或希望回退到数据库之前的某一状态时，openGauss的即时恢复功能（Point-In-Time Recovery，简称PITR）可以支持恢复到备份归档数据之后的任意时间点。常规恢复为全实例恢复，但是有时仅需要表空间级别的恢复，本案例演示表空间恢复方法。
前提条件
全库备份文件：base.tar.gz,16725.tar.gz,16726.tar.gz;
归档的wal日志文件，归档备份目录/ogarchive
新搭建的单机实例
全量数据准备
创建用户 create user zone identified by 'zhou0815FANG'; 创建表空间 CREATE TABLESPACE tb1 LOCATION '/home/omm/og_tb1'; CREATE TABLESPACE tb3 LOCATION '/home/omm/og_tb3'; 创建数据库 create database db1 owner zone tablespace tb1; create database db3 owner zone tablespace tb3; 连接db1 创建schema,表和数据 gsql -d db1 -p 26000 -Uzone create schema sch1 ; create table sch1.test1(id int); insert into sch1.test1 select generate_series(1,500000); db1=> insert into sch1.test1 select generate_series(1,500000); INSERT 0 500000 db1=> insert into sch1.test1 select generate_series(1,500000); INSERT 0 500000 db1=> insert into sch1.test1 select generate_series(1,500000); INSERT 0 500000 db1=> insert into sch1.test1 select generate_series(1,500000); INSERT 0 500000 select count(*) from sch1.test1; db1=> select count(*) from sch1.test1; count -------- 2000000 (1 row) 连接db3创建schema,表和数据 gsql -d db3 -p 26000 -Uzone create schema sch3 ; create table sch3.test3(id int); insert into sch3.test3 select generate_series(1,500000); db3=> insert into sch3.test3 select generate_series(1,500000); INSERT 0 500000 select count(*) from sch3.test3; db3=> select count(*) from sch3.test3; count -------- 500000 (1 row) |
创建备份
[omm@ogpri ~]$ ./backup.sh 2022-01-11 11:37:17 LOG: Backup starting ############################################### ALTER SYSTEM SET 2022-01-11 11:37:17 LOG: gs_basebackup starting(/home/omm/ogbackup/20220111) INFO: The starting position of the xlog copy of the full build is: 0/F56FAD8. The slot minimum LSN is: 0/F56FAD8. [2022-01-11 11:37:18]:transaction log start point: 0/F56FAD8 [2022-01-11 11:37:18]:begin build tablespace list [2022-01-11 11:37:18]:finish build tablespace list 495086/495086 kB (100%), 3/3 tablespaces [2022-01-11 11:37:40]:transaction log end point: 0/10000178 [2022-01-11 11:37:40]:gs_basebackup: fetching MOT checkpoint gs_basebackup: no mot checkpoint exists [2022-01-11 11:37:41]:gs_basebackup: base backup completed [2022-01-11 11:37:41]:gs_basebackup: base backup successfully 2022-01-11 11:37:41 LOG: gs_basebackup completed |
增量数据准备
再次插入数据，模拟备份完成以后，插入的数据是否能够恢复
[omm@ogsta ~]$ gsql -d db1 -p 26000 -Uzone Password for user zone: gsql ((openGauss 2.1.0 build 590b0f8e) compiled at 2021-09-30 14:29:04 commit 0 last mr ) Non-SSL connection (SSL connection is recommended when requiring high-security) Type "help" for help. db1=> insert into sch1.test1 select generate_series(1,500000); INSERT 0 500000 db1=# select count(*) from sch1.test1; count --------- 2500000 (1 row) db1=# \q |
关闭数据库
openGauss=# checkpoint; CHECKPOINT openGauss=# select pg_switch_xlog(); pg_switch_xlog ---------------- 0/21644620 (1 row) gs_ctl -D /opt/huawei/install/data/dn stop |
恢复准备
以下操作均在新的实例中操作，操作用户为omm
在进行异机恢复时，需要创建一个新的单实例，把归档文件和数据备份文件都传送到新的实例下相同的目录中，如果目录不存在需要手动创建。本案例中把源端的归档文件放入到实例的/ogarchive目录中，数据备份文件放在/home/omm/recovery_base目录中
删除数据文件目录并新建
[omm@ogpri data]$ rm -rf dn [omm@ogpri data]$ mkdir dn [omm@ogpri data]$ ll total 0 drwx------ 2 omm dbgrp 6 Jan 11 13:44 dn [omm@ogpri data]$ |
基础备份恢复
[omm@ogpri ]$ cd /home/omm/recovery_base/ [omm@ogsta ]$ gunzip base.tar.gz [omm@ogsta ]$ gs_tar -D /opt/huawei/install/data/dn -F base.tar |
表空间恢复
查看表空间映射。我们的目的是只恢复 og_tb1 表空间，og_tb3 表空间不管，因此需要对表空间所在的数据文件目录进行特殊处理。 [omm@ogpri dn]$ more /opt/huawei/install/data/dn/tablespace_map 16725 /home/omm/og_tb1 16726 /home/omm/og_tb3 删除表空间目录，并新建 [omm@ogpri ~]$ rm -rf og_tb1 [omm@ogpri ~]$ rm -rf og_tb3 [omm@ogpri ~]$ mkdir og_tb1 [omm@ogpri ~]$ mkdir og_tb3 清空数据文件目录中的pg_xlog pg_tblspc [omm@ogpri ~]$ cd /opt/huawei/install/data/dn/ [omm@ogpri dn]$ rm -rf pg_xlog/ [omm@ogpri dn]$ rm -rf pg_tblspc/ [omm@ogpri dn]$ mkdir pg_xlog [omm@ogpri dn]$ mkdir pg_tblspc 设置软连接，该软连接只针对需要恢复的表空间 即16725 [omm@ogpri dn]$ cd pg_tblspc/ [omm@ogpri pg_tblspc]$ ln -s /home/omm/og_tb1 16725 解压表空间数据压缩文件，由于只恢复og_tb1，因此可以直接从备份目录16725.tar 文件进行恢复 [omm@ogpri ~]$ cd /home/omm/ogbackup/20220111/ [omm@ogpri 20220111]$ ls 16725.tar.gz 16726.tar.gz backup.done base.tar [omm@ogpri 20220111]$ gunzip 16725.tar.gz [omm@ogpri 20220111]$ gs_tar -D /home/omm/og_tb1 -F 16725.tar 复制pg_wal日志 [omm@ogsta pg_xlog]$ cp /ogarchive/* /opt/huawei/install/data/dn/pg_xlog/ [omm@ogsta pg_xlog]$ 修改配置文件 [omm@ogsta dn]$ vi recovery.conf restore_command = 'cp /ogarchive/%f %p' |
数据库启动
gs_ctl -D /opt/huawei/install/data/dn start |
查看启动日志
该启动日志是由于数据备份的源段为集群，因此集群中存在主从节点的信息。在把数据恢复到单节点是，需要修改postgresql.conf 配置文件中的replconninfo1信息
2022-01-12 10:25:08.448 [unknown] [unknown] localhost 139657809295104 0[0:0#0] 0 [BACKEND] LOG: The heartbeat server has not been started. 2022-01-12 10:25:08.448 [unknown] [unknown] localhost 139657809295104 0[0:0#0] 0 [BACKEND] LOG: heartbeat thread shut down 2022-01-12 10:25:08.451 [unknown] [unknown] localhost 139657809295104 0[0:0#0] 0 [BACKEND] LOG: heartbeat thread started 2022-01-12 10:25:08.451 [unknown] [unknown] localhost 139657809295104 0[0:0#0] 0 [BACKEND] LOG: could not bind IPv4 socket: Is another instance already running on port 26005? If not, wait a few seconds and retry. 2022-01-12 10:25:08.451 [unknown] [unknown] localhost 139657809295104 0[0:0#0] 0 [BACKEND] LOG: could not create Ha listen socket for ReplConnInfoArr[1]"192.168.56.227:26005" 2022-01-12 10:25:08.451 [unknown] [unknown] localhost 139657809295104 0[0:0#0] 0 [BACKEND] LOG: The heartbeat server has not been started. 2022-01-12 10:25:08.451 [unknown] [unknown] localhost 139657809295104 0[0:0#0] 0 [BACKEND] LOG: heartbeat thread shut down |
数据验证
db1 数据恢复成功
[omm@ogtest dn]$ gsql -d postgres -p 26000 gsql ((openGauss 2.1.0 build 590b0f8e) compiled at 2021-09-30 14:29:04 commit 0 last mr ) Non-SSL connection (SSL connection is recommended when requiring high-security) Type "help" for help. openGauss=# \c db1 Non-SSL connection (SSL connection is recommended when requiring high-security) You are now connected to database "db1" as user "omm". db1=# select count(*) from sch1.test1; count --------- 2500000 (1 row) db1=# \c db3 FATAL: database "db3" does not exist DETAIL: The database subdirectory "pg_tblspc/16726/PG_9.2_201611171_dn_6001_6002/16728" is missing. Previous connection kept db1=# \q |
