openGauss 数据库物理备份与恢复

1. 检查数据库归档模式,如未归档需开启归档

openGauss=# select name,setting from pg_settings where name like 'archive%' or name='wal_level';
      name       |       setting        
-----------------+----------------------
 archive_command | cp %p /archivelog/%f
 archive_dest    | 
 archive_mode    | on
 archive_timeout | 900
 wal_level       | hot_standby
(5 rows)

2. 创建备份数据目录、备份归档目录

[root@og1 ~]# mkdir /backup
[root@og1 ~]# chown omm.dbgrp /backup/
[root@og1 ~]# mkdir /backuparch
[root@og1 ~]# chown omm.dbgrp /backuparch/

3. 进行数据备份

[omm@og1 ~]$ gs_basebackup -D /backup -p 15400 -P -l dbbackup20220220     
INFO:  The starting position of the xlog copy of the full build is: 0/E000028. The slot minimum LSN is: 0/0.
[2022-02-20 18:09:44]:begin build tablespace list
[2022-02-20 18:09:44]:finish build tablespace list
[2022-02-20 18:09:44]:begin get xlog by xlogstream
[2022-02-20 18:09:44]: check identify system success                                                
[2022-02-20 18:09:44]: send START_REPLICATION 0/E000000 success                                     
[2022-02-20 18:09:44]: keepalive message is received                                                
[2022-02-20 18:09:44]: keepalive message is received                                                
[2022-02-20 18:09:47]: keepalive message is received                                                
[2022-02-20 18:09:50]: keepalive message is received                                                
[2022-02-20 18:09:50]: keepalive message is received                                                
[2022-02-20 18:09:53]: keepalive message is received                                                
[2022-02-20 18:09:56]: keepalive message is received                                                
386544/386544 kB (100%), 3/3 tablespaces
[2022-02-20 18:10:01]:gs_basebackup: base backup successfully
查看备份数据
[omm@og1 dn]$ ls /backup
backup_label  gswlm_userinfo.cfg  pg_ctl.lock      pg_hba.conf.lock  pg_multixact  pg_snapshots  PG_VERSION           postgresql.conf.lock  server.key.rand
base          mot.conf            pg_errorinfo     pg_ident.conf     pg_notify     pg_stat_tmp   pg_xlog              server.crt            undo
cacert.pem    pg_clog             pg_hba.conf      pg_llog           pg_replslot   pg_tblspc     postgresql.conf      server.key
global        pg_csnlog           pg_hba.conf.bak  pg_location       pg_serial     pg_twophase   postgresql.conf.bak  server.key.cipher
[omm@og1 dn]$ 

4. 模拟数据损坏

删除数据目录下所有文件
[omm@og1 ~]$ cd /opt/huawei/install/data/dn/
[omm@og1 dn]$ ls
base           gs_gazelle.conf     pg_csnlog     pg_hba.conf.bak   pg_location   pg_serial     pg_twophase      postgresql.conf.bak   server.crt         undo
cacert.pem     gswlm_userinfo.cfg  pg_ctl.lock   pg_hba.conf.lock  pg_multixact  pg_snapshots  PG_VERSION       postgresql.conf.lock  server.key
gaussdb.state  mot.conf            pg_errorinfo  pg_ident.conf     pg_notify     pg_stat_tmp   pg_xlog          postmaster.opts       server.key.cipher
global         pg_clog             pg_hba.conf   pg_llog           pg_replslot   pg_tblspc     postgresql.conf  postmaster.pid        server.key.rand
[omm@og1 dn]$ rm -rf *
如下连接数据库以及启动数据库都失败
[omm@og1 ~]$  gsql -d postgres -p 15400 -r                                                                      
failed to connect Unknown:15400.
[omm@og1 dn]$ gs_om -t start
Starting cluster.
=========================================
=========================================
[GAUSS-53600]: Can not start the database, the cmd is source /home/omm/.bashrc; python3 '/opt/huawei/install/om/script/local/StartInstance.py' -U omm -R /opt/huawei/install/app -t 300 --security-mode=off,  Error:
[FAILURE] og1:
[GAUSS-50201] : The /opt/huawei/install/data/dn/postgresql.conf does not exist..

5. 恢复数据库

将备份目录数据拷贝到原数据目录
[omm@og1 dn]$ cp -r /backup/* .
[omm@og1 dn]$ ls
backup_label  gswlm_userinfo.cfg  pg_ctl.lock      pg_hba.conf.lock  pg_multixact  pg_snapshots  PG_VERSION           postgresql.conf.lock  server.key.rand
base          mot.conf            pg_errorinfo     pg_ident.conf     pg_notify     pg_stat_tmp   pg_xlog              server.crt            undo
cacert.pem    pg_clog             pg_hba.conf      pg_llog           pg_replslot   pg_tblspc     postgresql.conf      server.key
global        pg_csnlog           pg_hba.conf.bak  pg_location       pg_serial     pg_twophase   postgresql.conf.bak  server.key.cipher

5. 重启数据库

[omm@og1 dn]$ gs_om -t stop
Stopping cluster.
=========================================
Successfully stopped cluster.
=========================================
End stop cluster.
[omm@og1 dn]$ gs_om -t start
Starting cluster.
=========================================
[SUCCESS] og1
2022-02-20 19:00:19.668 62121f43.1 [unknown] 140056112825280 [unknown] 0 dn_6001 01000  0 [BACKEND] WARNING:  could not create any HA TCP/IP sockets
2022-02-20 19:00:19.671 62121f43.1 [unknown] 140056112825280 [unknown] 0 dn_6001 01000  0 [BACKEND] WARNING:  Failed to initialize the memory protect for g_instance.attr.attr_storage.cstore_buffers (1024 Mbytes) or shared memory (3649 Mbytes) is larger.
=========================================
Successfully started.

[omm@og1 ~]$  gsql -d postgres -p 15400 -r                                                                      
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=# select pg_switch_xlog();
 pg_switch_xlog 
----------------
 0/F000288
(1 row)

openGauss=# select * from emp;
 EMPLOYEE_ID | FIRST_NAME | LAST_NAME |  SALARY  
-------------+------------+-----------+----------
         100 | ticle      | xie       | 10000.00
         101 | tina       | wang      | 12000.00
(2 rows)

openGauss=# select * from test\;
openGauss-# select * from test;
 id | age 
----+-----
  1 |   1
  2 |   2
  3 |   3
  4 |   4
  5 |   5
(5 rows)