gpbackup备份和gprestore恢复实操大全

叶同学专栏 2022-06-18

1822

环境信息

产品	版本
Greenplum	gp5.19.0
gpbackup	1.12.1

观察的备份对象

类型	名称	数量
heap表	tmp_heap	100000
ao表:	tmp_ao	100000
分区表	tmp_par	20000
视图	v_pgbench_accounts
外部表	ext_pgbench_accounts
用户	yejf

gpbackup使用语法

gpbackup [flags]

flags参数列表

参数名称	说明
--backup-dir string	备份的目录
--compression-level int	压缩级别(1~9可选)，默认1
--data-only	只备份数据，不备份元数据信息
--dbname string	指定数据库名
--debug	debug模式
--exclude-schema strings	排除指定的schema，多个的话使用多项
--exclude-table strings	排除指定的表名，多个的话使用多项
--exclude-table-file string	排除指定表列表的文件
--from-timestamp string	用于增量备份时指定的时间戳
--include-schema strings	备份指定的schema，多个的话使用多项
--include-table stringArray	备份指定的表名，多个的话使用多项
--include-table-file string	备份指定表列表的文件
--incremental	增量备份，只支持ao表有个性的增量备份
--jobs int	并行数，默认1
--leaf-partition-data	对于分区表，每个子分区备份成一个文件而不是一个分区表一个备份文件
--metadata-only	只备份元数据信息，不备份数据
--no-compression	备份数据不进行压缩
--single-data-file	备份的数据生成一个文件而不是一个表一个备份文件
--with-stats	备份内容包括统计信息

gprestore使用语法

 gprestore [flags]

flags参数列表

参数名称	说明
--backup-dir string	备份的目录
--create-db	压缩级别(1~9可选)，默认1
--data-only	只恢复数据，不恢复元数据信息
--debug	debug模式
--exclude-schema strings	排除指定的schema，多个的话使用多项
--exclude-table strings	排除指定的表名，多个的话使用多项
--exclude-table-file string	排除指定表列表的文件
--include-schema strings	恢复指定的schema，多个的话使用多项
--include-table stringArray	恢复指定的表名，多个的话使用多项
--include-table-file string	恢复指定表列表的文件
--jobs int	并行数，默认1
--metadata-only	只恢复元数据信息
--on-error-continue	备份数据不进行压缩
--redirect-db string	备份的数据生成一个文件而不是一个表一个备份文件
--timestamp string	恢复的timestamp，格式是YYYYMMDDHHMMSS
--with-globals	恢复全局信息(用户、表空间那些，不包括模式和表)
--with-stats	恢复内容包括统计信息

测试例子

gpbackup备份分析

对数据库进行正常的备份命令

[gpadmin@nna ~]$ gpbackup --backup-dir data/gpbackup/ --dbname test --jobs 2
20210713:14:58:48 gpbackup:gpadmin:nna:002334-[INFO]:-Starting backup of database test
20210713:14:58:49 gpbackup:gpadmin:nna:002334-[INFO]:-Backup Timestamp = 20210713145848
20210713:14:58:49 gpbackup:gpadmin:nna:002334-[INFO]:-Backup Database = test
20210713:14:58:49 gpbackup:gpadmin:nna:002334-[INFO]:-Gathering table state information
20210713:14:58:49 gpbackup:gpadmin:nna:002334-[INFO]:-Acquiring ACCESS SHARE locks on tables
Locks acquired:  8  8 [================================================================] 100.00% 0s
20210713:14:58:49 gpbackup:gpadmin:nna:002334-[INFO]:-Gathering additional table metadata
20210713:14:58:49 gpbackup:gpadmin:nna:002334-[INFO]:-Metadata will be written to data/gpbackup/gpseg-1/backups/20210713/20210713145848/gpbackup_20210713145848_metadata.sql
20210713:14:58:49 gpbackup:gpadmin:nna:002334-[INFO]:-Writing global database metadata
20210713:14:58:49 gpbackup:gpadmin:nna:002334-[INFO]:-Global database metadata backup complete
20210713:14:58:49 gpbackup:gpadmin:nna:002334-[INFO]:-Writing pre-data metadata
20210713:14:58:49 gpbackup:gpadmin:nna:002334-[INFO]:-Pre-data metadata backup complete
20210713:14:58:49 gpbackup:gpadmin:nna:002334-[INFO]:-Writing post-data metadata
20210713:14:58:49 gpbackup:gpadmin:nna:002334-[INFO]:-Post-data metadata backup complete
20210713:14:58:49 gpbackup:gpadmin:nna:002334-[INFO]:-Writing data to file
Tables backed up:  7  7 [==============================================================] 100.00% 0s
20210713:14:58:50 gpbackup:gpadmin:nna:002334-[INFO]:-Skipped data backup of 1 external/foreign table(s).
20210713:14:58:50 gpbackup:gpadmin:nna:002334-[INFO]:-See home/gpadmin/gpAdminLogs/gpbackup_20210713.log for a complete list of skipped tables.
20210713:14:58:50 gpbackup:gpadmin:nna:002334-[INFO]:-Data backup complete
20210713:14:58:51 gpbackup:gpadmin:nna:002334-[INFO]:-Found neither usr/local/greenplum-db/./bin/gp_email_contacts.yaml nor home/gpadmin/gp_email_contacts.yaml
20210713:14:58:51 gpbackup:gpadmin:nna:002334-[INFO]:-Email containing gpbackup report data/gpbackup/gpseg-1/backups/20210713/20210713145848/gpbackup_20210713145848_report will not be sent
20210713:14:58:51 gpbackup:gpadmin:nna:002334-[INFO]:-Backup completed successfully

需要记住Timestamp = 20210713145848，恢复时会用到

执行完成后，会在各节点上生成对应的备份文件,备份名称格式是gpbackup_content_timestamp_oid.gz

[gpadmin@nna ~]$ gpssh -f allhosts -e 'ls -l data/gpbackup/*/backups/20210713/20210713145848'
[dn1] ls -l data/gpbackup/*/backups/20210713/20210713145848
[dn1] data/gpbackup/gpseg0/backups/20210713/20210713145848:
[dn1] 总用量 960
[dn1] -rw-------. 1 gpadmin gpadmin     20 7月  13 14:58 gpbackup_0_20210713145848_24577.gz
[dn1] -rw-------. 1 gpadmin gpadmin     96 7月  13 14:58 gpbackup_0_20210713145848_24580.gz
[dn1] -rw-------. 1 gpadmin gpadmin 795175 7月  13 14:58 gpbackup_0_20210713145848_24583.gz
[dn1] -rw-------. 1 gpadmin gpadmin     27 7月  13 14:58 gpbackup_0_20210713145848_24586.gz
[dn1] -rw-------. 1 gpadmin gpadmin  73736 7月  13 14:58 gpbackup_0_20210713145848_24595.gz
[dn1] -rw-------. 1 gpadmin gpadmin  74187 7月  13 14:58 gpbackup_0_20210713145848_24604.gz
[dn1] -rw-------. 1 gpadmin gpadmin  14912 7月  13 14:58 gpbackup_0_20210713145848_24612.gz
[dn1] 
[dn1] data/gpbackup/gpseg1/backups/20210713/20210713145848:
[dn1] 总用量 952
[dn1] -rw-------. 1 gpadmin gpadmin     20 7月  13 14:58 gpbackup_1_20210713145848_24577.gz
[dn1] -rw-------. 1 gpadmin gpadmin     97 7月  13 14:58 gpbackup_1_20210713145848_24580.gz
[dn1] -rw-------. 1 gpadmin gpadmin 795090 7月  13 14:58 gpbackup_1_20210713145848_24583.gz
[dn1] -rw-------. 1 gpadmin gpadmin     27 7月  13 14:58 gpbackup_1_20210713145848_24586.gz
[dn1] -rw-------. 1 gpadmin gpadmin  70151 7月  13 14:58 gpbackup_1_20210713145848_24595.gz
[dn1] -rw-------. 1 gpadmin gpadmin  71649 7月  13 14:58 gpbackup_1_20210713145848_24604.gz
[dn1] -rw-------. 1 gpadmin gpadmin  14825 7月  13 14:58 gpbackup_1_20210713145848_24612.gz
[dn2] ls -l data/gpbackup/*/backups/20210713/20210713145848
[dn2] data/gpbackup/gpseg2/backups/20210713/20210713145848:
[dn2] 总用量 980
[dn2] -rw-------. 1 gpadmin gpadmin     20 7月  13 14:58 gpbackup_2_20210713145848_24577.gz
[dn2] -rw-------. 1 gpadmin gpadmin     96 7月  13 14:58 gpbackup_2_20210713145848_24580.gz
[dn2] -rw-------. 1 gpadmin gpadmin 794907 7月  13 14:58 gpbackup_2_20210713145848_24583.gz
[dn2] -rw-------. 1 gpadmin gpadmin     30 7月  13 14:58 gpbackup_2_20210713145848_24586.gz
[dn2] -rw-------. 1 gpadmin gpadmin  86957 7月  13 14:58 gpbackup_2_20210713145848_24595.gz
[dn2] -rw-------. 1 gpadmin gpadmin  85181 7月  13 14:58 gpbackup_2_20210713145848_24604.gz
[dn2] -rw-------. 1 gpadmin gpadmin  14931 7月  13 14:58 gpbackup_2_20210713145848_24612.gz
[dn2] 
[dn2] data/gpbackup/gpseg3/backups/20210713/20210713145848:
[dn2] 总用量 984
[dn2] -rw-------. 1 gpadmin gpadmin     20 7月  13 14:58 gpbackup_3_20210713145848_24577.gz
[dn2] -rw-------. 1 gpadmin gpadmin     99 7月  13 14:58 gpbackup_3_20210713145848_24580.gz
[dn2] -rw-------. 1 gpadmin gpadmin 795280 7月  13 14:58 gpbackup_3_20210713145848_24583.gz
[dn2] -rw-------. 1 gpadmin gpadmin     31 7月  13 14:58 gpbackup_3_20210713145848_24586.gz
[dn2] -rw-------. 1 gpadmin gpadmin  87351 7月  13 14:58 gpbackup_3_20210713145848_24595.gz
[dn2] -rw-------. 1 gpadmin gpadmin  87042 7月  13 14:58 gpbackup_3_20210713145848_24604.gz
[dn2] -rw-------. 1 gpadmin gpadmin  14952 7月  13 14:58 gpbackup_3_20210713145848_24612.gz
[nna] ls -l data/gpbackup/*/backups/20210713/20210713145848
[nna] 总用量 24
[nna] -r--r--r--. 1 gpadmin gpadmin  790 7月  13 14:58 gpbackup_20210713145848_config.yaml
[nna] -r--r--r--. 1 gpadmin gpadmin 4687 7月  13 14:58 gpbackup_20210713145848_metadata.sql
[nna] -r--r--r--. 1 gpadmin gpadmin 1480 7月  13 14:58 gpbackup_20210713145848_report
[nna] -r--r--r--. 1 gpadmin gpadmin 6138 7月  13 14:58 gpbackup_20210713145848_toc.yaml

在master的备份目录下，可以看到备份的总信息

[gpadmin@nna 20210713145848]$ cat gpbackup_20210713145848_report
Greenplum Database Backup Report

Timestamp Key: 20210713145848
GPDB Version: 5.19.0 build commit:7f5d6a22614522e47fa1020933e0a231a122b00a
gpbackup Version: 1.12.1

Database Name: test
Command Line: gpbackup --backup-dir data/gpbackup/ --dbname test --jobs 2
Compression: gzip
Plugin Executable: None
Backup Section: All Sections
Object Filtering: None
Includes Statistics: No
Data File Format: Multiple Data Files Per Segment
Incremental: False

Start Time: 2021-07-13 14:58:48
End Time: 2021-07-13 14:58:51
Duration: 0:00:03

Backup Status: Success

Database Size: 219 MB
Count of Database Objects in Backup:
Aggregates                   0
Casts                        0
Constraints                  3
Conversions                  0
Database GUCs                0
Extensions                   0
Functions                    0
Indexes                      3
Operator Classes             0
Operator Families            0
Operators                    0
Procedural Languages         0
Protocols                    1
Resource Groups              2
Resource Queues              1
Roles                        2
Rules                        0
Schemas                      1
Sequences                    0
Tables                       8
Tablespaces                  0
Text Search Configurations   0
Text Search Dictionaries     0
Text Search Parsers          0
Text Search Templates        0
Triggers                     0
Types                        0
Views                        0

以及备份对象的明细

[gpadmin@nna 20210713145848]$ cat gpbackup_20210713145848_config.yaml
backupdir: data/gpbackup/
backupversion: 1.12.1
compressed: true
databasename: test
databaseversion: 5.19.0 build commit:7f5d6a22614522e47fa1020933e0a231a122b00a
dataonly: false
deleted: false
excluderelations: []
excludeschemafiltered: false
excludeschemas: []
excludetablefiltered: false
includerelations: []
includeschemafiltered: false
includeschemas: []
includetablefiltered: false
incremental: false
leafpartitiondata: false
metadataonly: false
plugin: ""
restoreplan:
- timestamp: "20210713145848"
  tablefqns:
  - public.pgbench_history
  - public.pgbench_tellers
  - public.pgbench_accounts
  - public.pgbench_branches
  - public.tmp_heap
  - public.tmp_ao
  - public.tmp_par
  - public.ext_pgbench_accounts
singledatafile: false
timestamp: "20210713145848"
withstatistics: false

因为没有使用--leaf-partition-data
参数，所以分区表跟其它表一样，备份成了一个文件，查看表的oid

test=# select gp_segment_id,oid,relfilenode
test-# from gp_dist_random('pg_class') 
test-# where relname='tmp_par'
test-# order by gp_segment_id ;
 gp_segment_id |  oid  | relfilenode 
---------------+-------+-------------
             0 | 24612 |       24589
             1 | 24612 |       24589
             2 | 24612 |       24589
             3 | 24612 |       24589

对比节点的记录数以及文件记录数一致，而且备份出来的文件是以逗号分隔开的

[gpadmin@nna ~]$ PGOPTIONS="-c gp_session_role=utility" psql -h dn1 -p 6000 test
psql (8.3.23)
Type "help" for help.

test=# select count(*) from tmp_par;
 count 
-------
  5245
(1 row)

[gpadmin@dn1 20210713145848]$ guznip gpbackup_0_20210713145848_24612.gz
[gpadmin@dn1 20210713145848]$ wc -l gpbackup_0_20210713145848_24612
5245 gpbackup_0_20210713145848_24612
[gpadmin@dn1 20210713145848]$ tail -5  gpbackup_0_20210713145848_24612
4980,04,201912
4984,04,201912
4988,04,201912
4993,04,201912
4997,04,201912

gprestore恢复数据

恢复指定表

恢复表tmp_heap到temp数据库，注意恢复到另外库时需要先创建对应的库

[gpadmin@nna 20210713145848]$ createdb temp
[gpadmin@nna 20210713145848]$ gprestore --redirect-db temp  --include-table public.tmp_heap --backup-dir data/gpbackup/ --timestamp 20210713145848
20210713:15:32:08 gprestore:gpadmin:nna:002948-[INFO]:-Restore Key = 20210713145848
20210713:15:32:09 gprestore:gpadmin:nna:002948-[INFO]:-Restoring pre-data metadata
Pre-data objects restored:  6  6 [=====================================================] 100.00% 0s
20210713:15:32:09 gprestore:gpadmin:nna:002948-[INFO]:-Pre-data metadata restore complete
Tables restored:  1  1 [===============================================================] 100.00% 0s
20210713:15:32:10 gprestore:gpadmin:nna:002948-[INFO]:-Data restore complete
20210713:15:32:10 gprestore:gpadmin:nna:002948-[INFO]:-Restoring post-data metadata
20210713:15:32:10 gprestore:gpadmin:nna:002948-[INFO]:-Post-data metadata restore complete
20210713:15:32:10 gprestore:gpadmin:nna:002948-[INFO]:-Found neither usr/local/greenplum-db/./bin/gp_email_contacts.yaml nor home/gpadmin/gp_email_contacts.yaml
20210713:15:32:10 gprestore:gpadmin:nna:002948-[INFO]:-Email containing gprestore report data/gpbackup/gpseg-1/backups/20210713/20210713145848/gprestore_20210713145848_20210713153208_report will not be sent
20210713:15:32:10 gprestore:gpadmin:nna:002948-[INFO]:-Restore completed successfully

验证恢复无问题

[gpadmin@nna ~]$ psql temp
psql (8.3.23)
Type "help" for help.

temp=# select count(*) from tmp_heap;
 count  
--------
 100000
(1 row)

再执行恢复，提示表已存在，需要删除表后才能恢复

[gpadmin@nna 20210713145848]$ gprestore --redirect-db temp  --include-table public.tmp_heap --backup-dir data/gpbackup/ --timestamp 20210713145848
20210713:15:33:39 gprestore:gpadmin:nna:002982-[INFO]:-Restore Key = 20210713145848
20210713:15:33:39 gprestore:gpadmin:nna:002982-[CRITICAL]:-Relation public.tmp_heap already exists
20210713:15:33:39 gprestore:gpadmin:nna:002982-[INFO]:-Found neither usr/local/greenplum-db/./bin/gp_email_contacts.yaml nor home/gpadmin/gp_email_contacts.yaml
20210713:15:33:39 gprestore:gpadmin:nna:002982-[INFO]:-Email containing gprestore report data/gpbackup/gpseg-1/backups/20210713/20210713145848/gprestore_20210713145848_20210713153339_report will not be sent

恢复数据到指定库

直接恢复所有数据到temp库

[gpadmin@nna 20210713145848]$ gprestore --redirect-db temp --backup-dir data/gpbackup/ --timestamp 20210713145848 --on-error-continue
20210713:15:37:01 gprestore:gpadmin:nna:003031-[INFO]:-Restore Key = 20210713145848
20210713:15:37:01 gprestore:gpadmin:nna:003031-[INFO]:-Restoring pre-data metadata
Pre-data objects restored:  0  20 [-------------------------------------------------------]   0.00%20210713:15:37:02 gprestore:gpadmin:nna:003031-[ERROR]:-Encountered 1 errors during metadata restore; see log file home/gpadmin/gpAdminLogs/gprestore_20210713.log for a list of failed statements.
Pre-data objects restored:  20  20 [===================================================] 100.00% 0s
20210713:15:37:02 gprestore:gpadmin:nna:003031-[INFO]:-Pre-data metadata restore complete
Tables restored:  5  7 [===============================================>------------------]  71.43%20210713:15:37:05 gprestore:gpadmin:nna:003031-[ERROR]:-Encountered 1 errors during table data restore; see log file home/gpadmin/gpAdminLogs/gprestore_20210713.log for a list of table errors.
Tables restored:  7  7 [===============================================================] 100.00% 2s
20210713:15:37:05 gprestore:gpadmin:nna:003031-[INFO]:-Data restore complete
20210713:15:37:05 gprestore:gpadmin:nna:003031-[INFO]:-Restoring post-data metadata
Post-data objects restored:  3  3 [====================================================] 100.00% 0s
20210713:15:37:05 gprestore:gpadmin:nna:003031-[INFO]:-Post-data metadata restore complete
20210713:15:37:05 gprestore:gpadmin:nna:003031-[INFO]:-Found neither usr/local/greenplum-db/./bin/gp_email_contacts.yaml nor home/gpadmin/gp_email_contacts.yaml
20210713:15:37:05 gprestore:gpadmin:nna:003031-[INFO]:-Email containing gprestore report data/gpbackup/gpseg-1/backups/20210713/20210713145848/gprestore_20210713145848_20210713153701_report will not be sent

注意这里使用了--on-error-continue
参数，会忽略错误，导致tmp_heap表数据翻倍了

temp=# select count(*) from tmp_heap;
 count  
--------
 200000
(1 row)

在恢复日志中还可以看到以下内容

20210713:15:37:05 gprestore:gpadmin:nna:003031-[DEBUG]:-Error loading data into table public.tmp_par: ERROR: command error message: cat: data/gpbackup/gpseg0/backups
/20210713/20210713145848/gpbackup_0_20210713145848_24612.gz: No such file or directory  (seg0 192.168.43.130:6000 pid=2462) (SQLSTATE 22P04)
20210713:15:37:05 gprestore:gpadmin:nna:003031-[ERROR]:-Encountered 1 errors during table data restore; see log file home/gpadmin/gpAdminLogs/gprestore_20210713.log
for a list of table errors.

是因为之前备份文件gpbackup_0_20210713145848_24612.gz解压过了，恢复时还需要压缩回去，现在再恢复一下tmp_par表，操作如下


[gpadmin@dn1 20210713145848]$ pwd
/data/gpbackup/gpseg0/backups/20210713/20210713145848
[gpadmin@dn1 20210713145848]$ gzip gpbackup_0_20210713145848_24612
[gpadmin@dn1 20210713145848]$ ll gpbackup_0_20210713145848_24612*
-rw-------. 1 gpadmin gpadmin 11605 Jul 13 14:58 gpbackup_0_20210713145848_24612.gz

--数据库删除表
[gpadmin@nna ~]$ psql temp -tc "drop table tmp_par"
DROP TABLE

--指定表恢复
[gpadmin@nna ~]$ gprestore --redirect-db temp  --include-table public.tmp_par --backup-dir data/gpbackup/ --timestamp 20210713145848
20210713:15:42:39 gprestore:gpadmin:nna:003111-[INFO]:-Restore Key = 20210713145848
20210713:15:42:39 gprestore:gpadmin:nna:003111-[INFO]:-Restoring pre-data metadata
Pre-data objects restored:  6  6 [=====================================================] 100.00% 0s
20210713:15:42:40 gprestore:gpadmin:nna:003111-[INFO]:-Pre-data metadata restore complete
Tables restored:  1  1 [===============================================================] 100.00% 0s
20210713:15:42:40 gprestore:gpadmin:nna:003111-[INFO]:-Data restore complete
20210713:15:42:40 gprestore:gpadmin:nna:003111-[INFO]:-Restoring post-data metadata
20210713:15:42:40 gprestore:gpadmin:nna:003111-[INFO]:-Post-data metadata restore complete
20210713:15:42:40 gprestore:gpadmin:nna:003111-[INFO]:-Found neither usr/local/greenplum-db/./bin/gp_email_contacts.yaml nor home/gpadmin/gp_email_contacts.yaml
20210713:15:42:40 gprestore:gpadmin:nna:003111-[INFO]:-Email containing gprestore report data/gpbackup/gpseg-1/backups/20210713/20210713145848/gprestore_20210713145848_20210713154239_report will not be sent
20210713:15:42:40 gprestore:gpadmin:nna:003111-[INFO]:-Restore completed successfully

--验证无问题
temp=# select count(*) from tmp_par;
 count 
-------
 21000
(1 row)

temp=# \c test
You are now connected to database "test" as user "gpadmin".
test=# select count(*) from tmp_par;
 count 
-------
 21000
(1 row)

使用`single-data-file`
备份

使用--single-data-file
参数后，不支持--jobs
参数了

[gpadmin@nna ~]$ gpbackup --backup-dir data/gpbackup/ --dbname test --single-data-file --no-compression
20210713:15:52:18 gpbackup:gpadmin:nna:003364-[INFO]:-Starting backup of database test
20210713:15:52:18 gpbackup:gpadmin:nna:003364-[INFO]:-Backup Timestamp = 20210713155218
20210713:15:52:18 gpbackup:gpadmin:nna:003364-[INFO]:-Backup Database = test
20210713:15:52:18 gpbackup:gpadmin:nna:003364-[INFO]:-Gathering table state information
20210713:15:52:18 gpbackup:gpadmin:nna:003364-[INFO]:-Acquiring ACCESS SHARE locks on tables
Locks acquired:  8  8 [================================================================] 100.00% 0s
20210713:15:52:18 gpbackup:gpadmin:nna:003364-[INFO]:-Gathering additional table metadata
20210713:15:52:19 gpbackup:gpadmin:nna:003364-[INFO]:-Metadata will be written to data/gpbackup/gpseg-1/backups/20210713/20210713155218/gpbackup_20210713155218_metadata.sql
20210713:15:52:19 gpbackup:gpadmin:nna:003364-[INFO]:-Writing global database metadata
20210713:15:52:19 gpbackup:gpadmin:nna:003364-[INFO]:-Global database metadata backup complete
20210713:15:52:19 gpbackup:gpadmin:nna:003364-[INFO]:-Writing pre-data metadata
20210713:15:52:19 gpbackup:gpadmin:nna:003364-[INFO]:-Pre-data metadata backup complete
20210713:15:52:19 gpbackup:gpadmin:nna:003364-[INFO]:-Writing post-data metadata
20210713:15:52:19 gpbackup:gpadmin:nna:003364-[INFO]:-Post-data metadata backup complete
20210713:15:52:21 gpbackup:gpadmin:nna:003364-[INFO]:-Writing data to file
Tables backed up:  7  7 [==============================================================] 100.00% 1s
20210713:15:52:22 gpbackup:gpadmin:nna:003364-[INFO]:-Skipped data backup of 1 external/foreign table(s).
20210713:15:52:22 gpbackup:gpadmin:nna:003364-[INFO]:-See home/gpadmin/gpAdminLogs/gpbackup_20210713.log for a complete list of skipped tables.
20210713:15:52:22 gpbackup:gpadmin:nna:003364-[INFO]:-Data backup complete
20210713:15:52:23 gpbackup:gpadmin:nna:003364-[INFO]:-Found neither usr/local/greenplum-db/./bin/gp_email_contacts.yaml nor home/gpadmin/gp_email_contacts.yaml
20210713:15:52:23 gpbackup:gpadmin:nna:003364-[INFO]:-Email containing gpbackup report data/gpbackup/gpseg-1/backups/20210713/20210713155218/gpbackup_20210713155218_report will not be sent
20210713:15:52:24 gpbackup:gpadmin:nna:003364-[INFO]:-Backup completed successfully

每个节点实例备份出来的数据都是一个文件了，并且由一个yaml记录备份文件中各表起始位置

[gpadmin@dn1 20210713155218]$ ll -tr
total 27872
-r--r--r--. 1 gpadmin gpadmin      367 Jul 13 15:52 gpbackup_0_20210713155218_toc.yaml
-rw-rw-r--. 1 gpadmin gpadmin 28536046 Jul 13 15:52 gpbackup_0_20210713155218
[gpadmin@dn1 20210713155218]$ cat gpbackup_0_20210713155218_toc.yaml 
dataentries:
  24577:
    startbyte: 0
    endbyte: 0
  24580:
    startbyte: 0
    endbyte: 201
  24583:
    startbyte: 201
    endbyte: 23997424
  24586:
    startbyte: 23997424
    endbyte: 23997434
  24595:
    startbyte: 23997434
    endbyte: 26220986
  24604:
    startbyte: 26220986
    endbyte: 28458746
  24612:
    startbyte: 28458746
    endbyte: 28536046
[gpadmin@dn1 20210713155218]$ wc -l gpbackup_0_20210713155218
301744 gpbackup_0_20210713155218

也可以恢复指定表

[gpadmin@nna ~]$ createdb temp
[gpadmin@nna ~]$ gprestore --redirect-db temp  --include-table public.tmp_heap --backup-dir data/gpbackup/ --timestamp 20210713155218
20210713:15:57:30 gprestore:gpadmin:nna:003446-[INFO]:-Restore Key = 20210713155218
20210713:15:57:31 gprestore:gpadmin:nna:003446-[INFO]:-Restoring pre-data metadata
Pre-data objects restored:  6  6 [=====================================================] 100.00% 0s
20210713:15:57:31 gprestore:gpadmin:nna:003446-[INFO]:-Pre-data metadata restore complete
Tables restored:  1  1 [===============================================================] 100.00% 2s
20210713:15:57:34 gprestore:gpadmin:nna:003446-[INFO]:-Data restore complete
20210713:15:57:34 gprestore:gpadmin:nna:003446-[INFO]:-Restoring post-data metadata
20210713:15:57:34 gprestore:gpadmin:nna:003446-[INFO]:-Post-data metadata restore complete
20210713:15:57:34 gprestore:gpadmin:nna:003446-[INFO]:-Found neither usr/local/greenplum-db/./bin/gp_email_contacts.yaml nor home/gpadmin/gp_email_contacts.yaml
20210713:15:57:34 gprestore:gpadmin:nna:003446-[INFO]:-Email containing gprestore report data/gpbackup/gpseg-1/backups/20210713/20210713155218/gprestore_20210713155218_20210713155730_report will not be sent
20210713:15:57:35 gprestore:gpadmin:nna:003446-[INFO]:-Restore completed successfully[gpadmin@nna ~]$ createdb temp
[gpadmin@nna ~]$ gprestore --redirect-db temp  --include-table public.tmp_heap --backup-dir data/gpbackup/ --timestamp 20210713155218
20210713:15:57:30 gprestore:gpadmin:nna:003446-[INFO]:-Restore Key = 20210713155218
20210713:15:57:31 gprestore:gpadmin:nna:003446-[INFO]:-Restoring pre-data metadata
Pre-data objects restored:  6  6 [=====================================================] 100.00% 0s
20210713:15:57:31 gprestore:gpadmin:nna:003446-[INFO]:-Pre-data metadata restore complete
Tables restored:  1  1 [===============================================================] 100.00% 2s
20210713:15:57:34 gprestore:gpadmin:nna:003446-[INFO]:-Data restore complete
20210713:15:57:34 gprestore:gpadmin:nna:003446-[INFO]:-Restoring post-data metadata
20210713:15:57:34 gprestore:gpadmin:nna:003446-[INFO]:-Post-data metadata restore complete
20210713:15:57:34 gprestore:gpadmin:nna:003446-[INFO]:-Found neither usr/local/greenplum-db/./bin/gp_email_contacts.yaml nor home/gpadmin/gp_email_contacts.yaml
20210713:15:57:34 gprestore:gpadmin:nna:003446-[INFO]:-Email containing gprestore report data/gpbackup/gpseg-1/backups/20210713/20210713155218/gprestore_20210713155218_20210713155730_report will not be sent
20210713:15:57:35 gprestore:gpadmin:nna:003446-[INFO]:-Restore completed successfully

[gpadmin@nna ~]$ psql temp
psql (8.3.23)
Type "help" for help.

temp=# select count(*) from tmp_heap;
 count  
--------
 100000
(1 row)

使用`--leaf-partition-data`
备份

[gpadmin@nna ~]$ gpbackup --backup-dir data/gpbackup/ --dbname test --jobs 2  --include-table public.tmp_par --leaf-partition-data  --no-compression
20210713:16:01:19 gpbackup:gpadmin:nna:003548-[INFO]:-Starting backup of database test
20210713:16:01:20 gpbackup:gpadmin:nna:003548-[INFO]:-Backup Timestamp = 20210713160119
20210713:16:01:20 gpbackup:gpadmin:nna:003548-[INFO]:-Backup Database = test
20210713:16:01:20 gpbackup:gpadmin:nna:003548-[INFO]:-Gathering table state information
20210713:16:01:20 gpbackup:gpadmin:nna:003548-[INFO]:-Acquiring ACCESS SHARE locks on tables
Locks acquired:  5  5 [================================================================] 100.00% 0s
20210713:16:01:20 gpbackup:gpadmin:nna:003548-[INFO]:-Gathering additional table metadata
20210713:16:01:20 gpbackup:gpadmin:nna:003548-[INFO]:-Metadata will be written to data/gpbackup/gpseg-1/backups/20210713/20210713160119/gpbackup_20210713160119_metadata.sql
20210713:16:01:20 gpbackup:gpadmin:nna:003548-[INFO]:-Writing pre-data metadata
20210713:16:01:20 gpbackup:gpadmin:nna:003548-[INFO]:-Pre-data metadata backup complete
20210713:16:01:20 gpbackup:gpadmin:nna:003548-[INFO]:-Writing post-data metadata
20210713:16:01:21 gpbackup:gpadmin:nna:003548-[INFO]:-Post-data metadata backup complete
20210713:16:01:21 gpbackup:gpadmin:nna:003548-[INFO]:-Writing data to file
Tables backed up:  4  4 [==============================================================] 100.00% 0s
20210713:16:01:21 gpbackup:gpadmin:nna:003548-[INFO]:-Data backup complete
20210713:16:01:22 gpbackup:gpadmin:nna:003548-[INFO]:-Found neither usr/local/greenplum-db/./bin/gp_email_contacts.yaml nor home/gpadmin/gp_email_contacts.yaml
20210713:16:01:22 gpbackup:gpadmin:nna:003548-[INFO]:-Email containing gpbackup report data/gpbackup/gpseg-1/backups/20210713/20210713160119/gpbackup_20210713160119_report will not be sent
20210713:16:01:22 gpbackup:gpadmin:nna:003548-[INFO]:-Backup completed successfully

成功备份后，在master上看到备份的信息，显示备份的表1个，yaml文件有显示备份的子分区表

[gpadmin@nna 20210713160119]$ cat gpbackup_20210713160119_report
Greenplum Database Backup Report

Timestamp Key: 20210713160119
GPDB Version: 5.19.0 build commit:7f5d6a22614522e47fa1020933e0a231a122b00a
gpbackup Version: 1.12.1

Database Name: test
Command Line: gpbackup --backup-dir data/gpbackup/ --dbname test --jobs 2 --include-table public.tmp_par --leaf-partition-data --no-compression
Compression: None
Plugin Executable: None
Backup Section: All Sections
Object Filtering: Include Table Filter
Includes Statistics: No
Data File Format: Multiple Data Files Per Segment
Incremental: False

Start Time: 2021-07-13 16:01:19
End Time: 2021-07-13 16:01:22
Duration: 0:00:03

Backup Status: Success

Count of Database Objects in Backup:
Constraints                  0
Conversions                  0
Indexes                      0
Rules                        0
Sequences                    0
Tables                       1
Triggers                     0
Views                        0


[gpadmin@nna 20210713160119]$ cat gpbackup_20210713160119_config.yaml
backupdir: data/gpbackup/
backupversion: 1.12.1
compressed: false
databasename: test
databaseversion: 5.19.0 build commit:7f5d6a22614522e47fa1020933e0a231a122b00a
dataonly: false
deleted: false
excluderelations: []
excludeschemafiltered: false
excludeschemas: []
excludetablefiltered: false
includerelations:
- public.tmp_par
includeschemafiltered: false
includeschemas: []
includetablefiltered: true
incremental: false
leafpartitiondata: true
metadataonly: false
plugin: ""
restoreplan:
- timestamp: "20210713160119"
  tablefqns:
  - public.tmp_par_1_prt_p01
  - public.tmp_par_1_prt_p02
  - public.tmp_par_1_prt_p03
  - public.tmp_par_1_prt_p99
singledatafile: false
timestamp: "20210713160119"
withstatistics: false

模拟表膨胀情况下备份

模拟数据，tmp_head是严重膨胀的表，tmp_heap_bak是无膨胀的表

test=# select count(*) from tmp_heap;
 count  
--------
 100000
(1 row)

test=# select count(*) from tmp_heap_bak;
 count  
--------
 100000
(1 row)

test=# select pg_size_pretty(pg_relation_size(lower('public.tmp_heap')));
 pg_size_pretty 
----------------
 63 MB
(1 row)

test=# select pg_size_pretty(pg_relation_size(lower('public.tmp_heap_bak')));
 pg_size_pretty 
----------------
 13 MB
(1 row)

用gpbackup命令进行备份两个表

[gpadmin@nna ~]$ gpbackup --backup-dir data/gpbackup/ --dbname test --jobs 2  --include-table public.tmp_heap --include-table public.tmp_heap_bak --no-compression
20210713:16:17:45 gpbackup:gpadmin:nna:003605-[INFO]:-Starting backup of database test
20210713:16:17:45 gpbackup:gpadmin:nna:003605-[INFO]:-Backup Timestamp = 20210713161745
20210713:16:17:45 gpbackup:gpadmin:nna:003605-[INFO]:-Backup Database = test
20210713:16:17:45 gpbackup:gpadmin:nna:003605-[INFO]:-Gathering table state information
20210713:16:17:45 gpbackup:gpadmin:nna:003605-[INFO]:-Acquiring ACCESS SHARE locks on tables
Locks acquired:  2  2 [================================================================] 100.00% 0s
20210713:16:17:45 gpbackup:gpadmin:nna:003605-[INFO]:-Gathering additional table metadata
20210713:16:17:45 gpbackup:gpadmin:nna:003605-[INFO]:-Metadata will be written to data/gpbackup/gpseg-1/backups/20210713/20210713161745/gpbackup_20210713161745_metadata.sql
20210713:16:17:45 gpbackup:gpadmin:nna:003605-[INFO]:-Writing pre-data metadata
20210713:16:17:46 gpbackup:gpadmin:nna:003605-[INFO]:-Pre-data metadata backup complete
20210713:16:17:46 gpbackup:gpadmin:nna:003605-[INFO]:-Writing post-data metadata
20210713:16:17:46 gpbackup:gpadmin:nna:003605-[INFO]:-Post-data metadata backup complete
20210713:16:17:46 gpbackup:gpadmin:nna:003605-[INFO]:-Writing data to file
Tables backed up:  2  2 [==============================================================] 100.00% 0s
20210713:16:17:46 gpbackup:gpadmin:nna:003605-[INFO]:-Data backup complete
20210713:16:17:47 gpbackup:gpadmin:nna:003605-[INFO]:-Found neither usr/local/greenplum-db/./bin/gp_email_contacts.yaml nor home/gpadmin/gp_email_contacts.yaml
20210713:16:17:47 gpbackup:gpadmin:nna:003605-[INFO]:-Email containing gpbackup report data/gpbackup/gpseg-1/backups/20210713/20210713161745/gpbackup_20210713161745_report will not be sent

查看备份出来的文件，大小差不多

[gpadmin@dn1 20210713161745]$ pwd
/data/gpbackup/gpseg0/backups/20210713/20210713161745
[gpadmin@dn1 20210713161745]$ du -sh *
2.5M    gpbackup_0_20210713161745_24595
2.3M    gpbackup_0_20210713161745_24758

恢复到相同实例数但是不同机器数量

gpbackup进行备份

当前集群配置是2台segment，每台segment是2个primary

[gpadmin@nna ~]$ gpbackup --backup-dir data/gpbackup/ --dbname test --jobs 2
20210714:09:32:47 gpbackup:gpadmin:nna:005000-[INFO]:-Starting backup of database test
20210714:09:32:48 gpbackup:gpadmin:nna:005000-[INFO]:-Backup Timestamp = 20210714093247
20210714:09:32:48 gpbackup:gpadmin:nna:005000-[INFO]:-Backup Database = test
20210714:09:32:48 gpbackup:gpadmin:nna:005000-[INFO]:-Gathering table state information
20210714:09:32:48 gpbackup:gpadmin:nna:005000-[INFO]:-Acquiring ACCESS SHARE locks on tables
Locks acquired:  10  10 [==============================================================] 100.00% 0s
20210714:09:32:48 gpbackup:gpadmin:nna:005000-[INFO]:-Gathering additional table metadata
20210714:09:32:48 gpbackup:gpadmin:nna:005000-[INFO]:-Metadata will be written to data/gpbackup/gpseg-1/backups/20210714/20210714093247/gpbackup_20210714093247_metadata.sql
20210714:09:32:48 gpbackup:gpadmin:nna:005000-[INFO]:-Writing global database metadata
20210714:09:32:48 gpbackup:gpadmin:nna:005000-[INFO]:-Global database metadata backup complete
20210714:09:32:48 gpbackup:gpadmin:nna:005000-[INFO]:-Writing pre-data metadata
20210714:09:32:48 gpbackup:gpadmin:nna:005000-[INFO]:-Pre-data metadata backup complete
20210714:09:32:48 gpbackup:gpadmin:nna:005000-[INFO]:-Writing post-data metadata
20210714:09:32:48 gpbackup:gpadmin:nna:005000-[INFO]:-Post-data metadata backup complete
20210714:09:32:48 gpbackup:gpadmin:nna:005000-[INFO]:-Writing data to file
Tables backed up:  9  9 [==============================================================] 100.00% 0s
20210714:09:32:49 gpbackup:gpadmin:nna:005000-[INFO]:-Skipped data backup of 1 external/foreign table(s).
20210714:09:32:49 gpbackup:gpadmin:nna:005000-[INFO]:-See home/gpadmin/gpAdminLogs/gpbackup_20210714.log for a complete list of skipped tables.
20210714:09:32:49 gpbackup:gpadmin:nna:005000-[INFO]:-Data backup complete
20210714:09:32:50 gpbackup:gpadmin:nna:005000-[INFO]:-Found neither usr/local/greenplum-db/./bin/gp_email_contacts.yaml nor home/gpadmin/gp_email_contacts.yaml
20210714:09:32:50 gpbackup:gpadmin:nna:005000-[INFO]:-Email containing gpbackup report data/gpbackup/gpseg-1/backups/20210714/20210714093247/gpbackup_20210714093247_report will not be sent
20210714:09:32:50 gpbackup:gpadmin:nna:005000-[INFO]:-Backup completed successfully

重新初始化集群的配置

postgres=# select * from gp_segment_configuration ;
 dbid | content | role | preferred_role | mode | status | port | hostname | address | replication_port 
------+---------+------+----------------+------+--------+------+----------+---------+------------------
    1 |      -1 | p    | p              | s    | u      | 5432 | nna      | nna     |                 
    2 |       0 | p    | p              | s    | u      | 6000 | dn1      | dn1     |                 
    3 |       1 | p    | p              | s    | u      | 6001 | dn1      | dn1     |                 
    4 |       2 | p    | p              | s    | u      | 6002 | dn1      | dn1     |                 
    5 |       3 | p    | p              | s    | u      | 6003 | dn1      | dn1     |

把dn2服务器上的备份拷贝到dn1上

[gpadmin@dn2 gpbackup]$ scp -r gpseg2 dn1:/data/gpbackup
[gpadmin@dn2 gpbackup]$ scp -r gpseg3 dn1:/data/gpbackup

[gpadmin@dn1 gpbackup]$ ll
total 0
drwxrwxr-x. 3 gpadmin gpadmin 21 Jul 14 09:32 gpseg0
drwxrwxr-x. 3 gpadmin gpadmin 21 Jul 14 09:32 gpseg1
drwxrwxr-x. 3 gpadmin gpadmin 21 Jul 14 09:57 gpseg2

gprestore恢复

使用 --create-db 参数表示自动创建数据库
使用--with-globals 参数表示恢复全局元数据信息

[gpadmin@nna ~]$ gprestore --backup-dir data/gpbackup/ --timestamp 20210714093247 --create-db --with-globals
20210714:10:01:24 gprestore:gpadmin:nna:019221-[INFO]:-Restore Key = 20210714093247
20210714:10:01:25 gprestore:gpadmin:nna:019221-[INFO]:-Restoring global metadata
Global objects restored:  19  19 [=====================================================] 100.00% 1s
20210714:10:01:26 gprestore:gpadmin:nna:019221-[INFO]:-Global database metadata restore complete
20210714:10:01:26 gprestore:gpadmin:nna:019221-[INFO]:-Restoring pre-data metadata
Pre-data objects restored:  27  27 [===================================================] 100.00% 0s
20210714:10:01:27 gprestore:gpadmin:nna:019221-[INFO]:-Pre-data metadata restore complete
Tables restored:  9  9 [===============================================================] 100.00% 3s
20210714:10:01:31 gprestore:gpadmin:nna:019221-[INFO]:-Data restore complete
20210714:10:01:31 gprestore:gpadmin:nna:019221-[INFO]:-Restoring post-data metadata
Post-data objects restored:  3  3 [====================================================] 100.00% 1s
20210714:10:01:33 gprestore:gpadmin:nna:019221-[INFO]:-Post-data metadata restore complete
20210714:10:01:33 gprestore:gpadmin:nna:019221-[INFO]:-Found neither usr/local/greenplum-db/./bin/gp_email_contacts.yaml nor home/gpadmin/gp_email_contacts.yaml
20210714:10:01:33 gprestore:gpadmin:nna:019221-[INFO]:-Email containing gprestore report data/gpbackup/gpseg-1/backups/20210714/20210714093247/gprestore_20210714093247_20210714100124_report will not be sent
20210714:10:01:33 gprestore:gpadmin:nna:019221-[INFO]:-Restore completed successfully

检查数据以及元数据正常

test=# select count(*) from tmp_heap;
 count  
--------
 100000
(1 row)

test=# select count(*) from tmp_ao;
 count  
--------
 100000
(1 row)
test=# \du+ yejf
                  List of roles
 Role name | Attributes | Member of | Description 
-----------+------------+-----------+-------------
 yejf      |            | {}        |

恢复到相同实例数的新集群

再重新初始化集群，此时配置是2台服务器，一台2个primary

postgres=# select * from gp_segment_configuration ;
 dbid | content | role | preferred_role | mode | status | port | hostname | address | replication_port 
------+---------+------+----------------+------+--------+------+----------+---------+------------------
    1 |      -1 | p    | p              | s    | u      | 5432 | nna      | nna     |                 
    2 |       0 | p    | p              | s    | u      | 6000 | dn1      | dn1     |                 
    4 |       2 | p    | p              | s    | u      | 6000 | dn2      | dn2     |                 
    3 |       1 | p    | p              | s    | u      | 6001 | dn1      | dn1     |                 
    5 |       3 | p    | p              | s    | u      | 6001 | dn2      | dn2     |

恢复数据库，正常，并且验证数据也正常

[gpadmin@nna ~]$ gprestore --backup-dir data/gpbackup/ --timestamp 20210714093247 --create-db --with-globals
20210714:10:09:28 gprestore:gpadmin:nna:022857-[INFO]:-Restore Key = 20210714093247
20210714:10:09:29 gprestore:gpadmin:nna:022857-[INFO]:-Restoring global metadata
Global objects restored:  19  19 [=====================================================] 100.00% 1s
20210714:10:09:30 gprestore:gpadmin:nna:022857-[INFO]:-Global database metadata restore complete
20210714:10:09:30 gprestore:gpadmin:nna:022857-[INFO]:-Restoring pre-data metadata
Pre-data objects restored:  27  27 [===================================================] 100.00% 0s
20210714:10:09:31 gprestore:gpadmin:nna:022857-[INFO]:-Pre-data metadata restore complete
Tables restored:  9  9 [===============================================================] 100.00% 2s
20210714:10:09:34 gprestore:gpadmin:nna:022857-[INFO]:-Data restore complete
20210714:10:09:34 gprestore:gpadmin:nna:022857-[INFO]:-Restoring post-data metadata
Post-data objects restored:  3  3 [====================================================] 100.00% 0s
20210714:10:09:35 gprestore:gpadmin:nna:022857-[INFO]:-Post-data metadata restore complete
20210714:10:09:35 gprestore:gpadmin:nna:022857-[INFO]:-Found neither usr/local/greenplum-db/./bin/gp_email_contacts.yaml nor home/gpadmin/gp_email_contacts.yaml
20210714:10:09:35 gprestore:gpadmin:nna:022857-[INFO]:-Email containing gprestore report data/gpbackup/gpseg-1/backups/20210714/20210714093247/gprestore_20210714093247_20210714100928_report will not be sent
20210714:10:09:35 gprestore:gpadmin:nna:022857-[INFO]:-Restore completed successfully

恢复到不同实例数的集群（实例数比原来的少）

全局信息恢复成功，但恢复数据时失败，然后停止恢复了

[gpadmin@nna ~]$  gprestore --backup-dir data/gpbackup/ --timestamp 20210714093247 --create-db --debug
20210714:10:57:38 gprestore:gpadmin:nna:025651-[INFO]:-Restore Key = 20210714093247
20210714:10:57:38 gprestore:gpadmin:nna:025651-[DEBUG]:-Gathering information on backup directories
20210714:10:57:38 gprestore:gpadmin:nna:025651-[DEBUG]:-Verifying backup directories exist
20210714:10:57:38 gprestore:gpadmin:nna:025651-[DEBUG]:-Metadata will be restored from data/gpbackup/gpseg-1/backups/20210714/20210714093247/gpbackup_20210714093247_metadata.sql
20210714:10:57:39 gprestore:gpadmin:nna:025651-[INFO]:-Creating database
20210714:10:57:39 gprestore:gpadmin:nna:025651-[INFO]:-Database creation complete for: test
20210714:10:57:39 gprestore:gpadmin:nna:025651-[INFO]:-Restoring pre-data metadata
20210714:10:57:39 gprestore:gpadmin:nna:025651-[DEBUG]:-Pre-data objects restored:  10% (3/27)
20210714:10:57:39 gprestore:gpadmin:nna:025651-[DEBUG]:-Pre-data objects restored:  20% (6/27)
20210714:10:57:39 gprestore:gpadmin:nna:025651-[DEBUG]:-Pre-data objects restored:  30% (9/27)
20210714:10:57:39 gprestore:gpadmin:nna:025651-[DEBUG]:-Pre-data objects restored:  40% (11/27)
20210714:10:57:39 gprestore:gpadmin:nna:025651-[DEBUG]:-Pre-data objects restored:  50% (14/27)
20210714:10:57:40 gprestore:gpadmin:nna:025651-[DEBUG]:-Pre-data objects restored:  60% (17/27)
20210714:10:57:40 gprestore:gpadmin:nna:025651-[DEBUG]:-Pre-data objects restored:  70% (19/27)
20210714:10:57:40 gprestore:gpadmin:nna:025651-[DEBUG]:-Pre-data objects restored:  80% (22/27)
20210714:10:57:40 gprestore:gpadmin:nna:025651-[DEBUG]:-Pre-data objects restored:  90% (25/27)
20210714:10:57:40 gprestore:gpadmin:nna:025651-[DEBUG]:-Pre-data objects restored:  100% (27/27)
20210714:10:57:40 gprestore:gpadmin:nna:025651-[INFO]:-Pre-data metadata restore complete
20210714:10:57:40 gprestore:gpadmin:nna:025651-[DEBUG]:-Verifying backup file count
20210714:10:57:40 gprestore:gpadmin:nna:025651-[DEBUG]:-Restoring data from backup with timestamp: 20210714093247
20210714:10:57:40 gprestore:gpadmin:nna:025651-[DEBUG]:-Reading data for table public.pgbench_history from file (table 1 of 9)
20210714:10:57:40 gprestore:gpadmin:nna:025651-[DEBUG]:-Reading data for table public.pgbench_tellers from file (table 2 of 9)
20210714:10:57:40 gprestore:gpadmin:nna:025651-[CRITICAL]:-Expected to restore 100 rows to table public.pgbench_tellers, but restored 50 instead
github.com/greenplum-db/gpbackup/restore.restoreDataFromTimestamp
        tmp/build/5f8239f8/go/src/github.com/greenplum-db/gpbackup/restore/data.go:161
github.com/greenplum-db/gpbackup/restore.restoreData
        tmp/build/5f8239f8/go/src/github.com/greenplum-db/gpbackup/restore/restore.go:237
github.com/greenplum-db/gpbackup/restore.DoRestore
        tmp/build/5f8239f8/go/src/github.com/greenplum-db/gpbackup/restore/restore.go:148
main.main.func1
        tmp/build/5f8239f8/go/src/github.com/greenplum-db/gpbackup/gprestore.go:23
github.com/greenplum-db/gpbackup/vendor/github.com/spf13/cobra.(*Command).execute
        tmp/build/5f8239f8/go/src/github.com/greenplum-db/gpbackup/vendor/github.com/spf13/cobra/command.go:766
github.com/greenplum-db/gpbackup/vendor/github.com/spf13/cobra.(*Command).ExecuteC
        tmp/build/5f8239f8/go/src/github.com/greenplum-db/gpbackup/vendor/github.com/spf13/cobra/command.go:852
github.com/greenplum-db/gpbackup/vendor/github.com/spf13/cobra.(*Command).Execute
        tmp/build/5f8239f8/go/src/github.com/greenplum-db/gpbackup/vendor/github.com/spf13/cobra/command.go:800
main.main
        tmp/build/5f8239f8/go/src/github.com/greenplum-db/gpbackup/gprestore.go:27
runtime.main
        usr/local/go/src/runtime/proc.go:198
runtime.goexit
        usr/local/go/src/runtime/asm_amd64.s:2361
20210714:10:57:40 gprestore:gpadmin:nna:025651-[INFO]:-Found neither usr/local/greenplum-db/./bin/gp_email_contacts.yaml nor home/gpadmin/gp_email_contacts.yaml
20210714:10:57:40 gprestore:gpadmin:nna:025651-[INFO]:-Email containing gprestore report data/gpbackup/gpseg-1/backups/20210714/20210714093247/gprestore_20210714093247_20210714105738_report will not be sent
20210714:10:57:40 gprestore:gpadmin:nna:025651-[DEBUG]:-Beginning cleanup
20210714:10:57:40 gprestore:gpadmin:nna:025651-[DEBUG]:-Cleanup complete

彩参数--on-error-continue
恢复

可以恢复，数据相应减少一半

[gpadmin@nna ~]$  gprestore --backup-dir data/gpbackup/ --timestamp 20210714093247 --create-db --debug --on-error-continue
20210714:10:58:56 gprestore:gpadmin:nna:025681-[INFO]:-Restore Key = 20210714093247
20210714:10:58:56 gprestore:gpadmin:nna:025681-[DEBUG]:-Gathering information on backup directories
20210714:10:58:56 gprestore:gpadmin:nna:025681-[DEBUG]:-Verifying backup directories exist
20210714:10:58:57 gprestore:gpadmin:nna:025681-[DEBUG]:-Metadata will be restored from data/gpbackup/gpseg-1/backups/20210714/20210714093247/gpbackup_20210714093247_metadata.sql
20210714:10:58:57 gprestore:gpadmin:nna:025681-[INFO]:-Creating database
20210714:10:58:58 gprestore:gpadmin:nna:025681-[INFO]:-Database creation complete for: test
20210714:10:58:58 gprestore:gpadmin:nna:025681-[INFO]:-Restoring pre-data metadata
20210714:10:58:58 gprestore:gpadmin:nna:025681-[DEBUG]:-Pre-data objects restored:  10% (3/27)
20210714:10:58:58 gprestore:gpadmin:nna:025681-[DEBUG]:-Pre-data objects restored:  20% (6/27)
20210714:10:58:58 gprestore:gpadmin:nna:025681-[DEBUG]:-Pre-data objects restored:  30% (9/27)
20210714:10:58:58 gprestore:gpadmin:nna:025681-[DEBUG]:-Pre-data objects restored:  40% (11/27)
20210714:10:58:58 gprestore:gpadmin:nna:025681-[DEBUG]:-Pre-data objects restored:  50% (14/27)
20210714:10:58:58 gprestore:gpadmin:nna:025681-[DEBUG]:-Pre-data objects restored:  60% (17/27)
20210714:10:58:58 gprestore:gpadmin:nna:025681-[DEBUG]:-Pre-data objects restored:  70% (19/27)
20210714:10:58:58 gprestore:gpadmin:nna:025681-[DEBUG]:-Pre-data objects restored:  80% (22/27)
20210714:10:58:58 gprestore:gpadmin:nna:025681-[DEBUG]:-Pre-data objects restored:  90% (25/27)
20210714:10:58:58 gprestore:gpadmin:nna:025681-[DEBUG]:-Pre-data objects restored:  100% (27/27)
20210714:10:58:58 gprestore:gpadmin:nna:025681-[INFO]:-Pre-data metadata restore complete
20210714:10:58:58 gprestore:gpadmin:nna:025681-[DEBUG]:-Verifying backup file count
20210714:10:58:59 gprestore:gpadmin:nna:025681-[DEBUG]:-Restoring data from backup with timestamp: 20210714093247
20210714:10:58:59 gprestore:gpadmin:nna:025681-[DEBUG]:-Reading data for table public.pgbench_history from file (table 1 of 9)
20210714:10:58:59 gprestore:gpadmin:nna:025681-[DEBUG]:-Reading data for table public.pgbench_tellers from file (table 2 of 9)
20210714:10:58:59 gprestore:gpadmin:nna:025681-[DEBUG]:-Expected to restore 100 rows to table public.pgbench_tellers, but restored 50 instead
20210714:10:58:59 gprestore:gpadmin:nna:025681-[DEBUG]:-Reading data for table public.pgbench_accounts from file (table 3 of 9)
20210714:10:59:00 gprestore:gpadmin:nna:025681-[DEBUG]:-Expected to restore 1000000 rows to table public.pgbench_accounts, but restored 499999 instead
20210714:10:59:00 gprestore:gpadmin:nna:025681-[DEBUG]:-Reading data for table public.pgbench_branches from file (table 4 of 9)
20210714:10:59:00 gprestore:gpadmin:nna:025681-[DEBUG]:-Expected to restore 10 rows to table public.pgbench_branches, but restored 4 instead
20210714:10:59:00 gprestore:gpadmin:nna:025681-[DEBUG]:-Reading data for table public.tmp_heap from file (table 5 of 9)
20210714:10:59:00 gprestore:gpadmin:nna:025681-[DEBUG]:-Expected to restore 100000 rows to table public.tmp_heap, but restored 53528 instead
20210714:10:59:00 gprestore:gpadmin:nna:025681-[DEBUG]:-Reading data for table public.tmp_ao from file (table 6 of 9)
20210714:10:59:01 gprestore:gpadmin:nna:025681-[DEBUG]:-Expected to restore 100000 rows to table public.tmp_ao, but restored 53528 instead
20210714:10:59:01 gprestore:gpadmin:nna:025681-[DEBUG]:-Reading data for table public.tmp_par from file (table 7 of 9)
20210714:10:59:01 gprestore:gpadmin:nna:025681-[DEBUG]:-Expected to restore 21000 rows to table public.tmp_par, but restored 10490 instead
20210714:10:59:01 gprestore:gpadmin:nna:025681-[DEBUG]:-Reading data for table public.tmp_heap_bak from file (table 8 of 9)
20210714:10:59:01 gprestore:gpadmin:nna:025681-[DEBUG]:-Expected to restore 100000 rows to table public.tmp_heap_bak, but restored 50001 instead
20210714:10:59:01 gprestore:gpadmin:nna:025681-[DEBUG]:-Reading data for table public.tmp from file (table 9 of 9)
20210714:10:59:01 gprestore:gpadmin:nna:025681-[ERROR]:-Encountered 7 errors during table data restore; see log file home/gpadmin/gpAdminLogs/gprestore_20210714.log for a list of table errors.
20210714:10:59:01 gprestore:gpadmin:nna:025681-[INFO]:-Data restore complete
20210714:10:59:01 gprestore:gpadmin:nna:025681-[INFO]:-Restoring post-data metadata
20210714:10:59:01 gprestore:gpadmin:nna:025681-[DEBUG]:-Post-data objects restored:  30% (1/3)
20210714:10:59:01 gprestore:gpadmin:nna:025681-[DEBUG]:-Post-data objects restored:  60% (2/3)
20210714:10:59:01 gprestore:gpadmin:nna:025681-[DEBUG]:-Post-data objects restored:  100% (3/3)
20210714:10:59:01 gprestore:gpadmin:nna:025681-[INFO]:-Post-data metadata restore complete
20210714:10:59:01 gprestore:gpadmin:nna:025681-[INFO]:-Found neither usr/local/greenplum-db/./bin/gp_email_contacts.yaml nor home/gpadmin/gp_email_contacts.yaml
20210714:10:59:01 gprestore:gpadmin:nna:025681-[INFO]:-Email containing gprestore report data/gpbackup/gpseg-1/backups/20210714/20210714093247/gprestore_20210714093247_20210714105856_report will not be sent
20210714:10:59:01 gprestore:gpadmin:nna:025681-[DEBUG]:-Beginning cleanup
20210714:10:59:01 gprestore:gpadmin:nna:025681-[DEBUG]:-Cleanup complete

验证数据少了一半

test=# select count(*) from tmp_heap;
 count 
-------
 53528
(1 row)

test=# select count(*) from tmp_ao;
 count 
-------
 53528
(1 row)

模拟DD存储的备份恢复

dd挂载到本地存储，使用gbackup备份后，每台节点服务器看到的dd存储是所有实例的目录数据

备份过程

[gpadmin@nna ~]$ gpbackup --backup-dir data/gpbackup/ --dbname test --no-compression --jobs 2
20210723:16:54:30 gpbackup:gpadmin:nna:002465-[INFO]:-Starting backup of database test
20210723:16:54:31 gpbackup:gpadmin:nna:002465-[INFO]:-Backup Timestamp = 20210723165430
20210723:16:54:31 gpbackup:gpadmin:nna:002465-[INFO]:-Backup Database = test
20210723:16:54:31 gpbackup:gpadmin:nna:002465-[INFO]:-Gathering table state information
20210723:16:54:31 gpbackup:gpadmin:nna:002465-[INFO]:-Acquiring ACCESS SHARE locks on tables
Locks acquired:  10  10 [==============================================================] 100.00% 0s
20210723:16:54:31 gpbackup:gpadmin:nna:002465-[INFO]:-Gathering additional table metadata
20210723:16:54:31 gpbackup:gpadmin:nna:002465-[INFO]:-Metadata will be written to data/gpbackup/gpseg-1/backups/20210723/20210723165430/gpbackup_20210723165430_metadata.sql
20210723:16:54:31 gpbackup:gpadmin:nna:002465-[INFO]:-Writing global database metadata
20210723:16:54:31 gpbackup:gpadmin:nna:002465-[INFO]:-Global database metadata backup complete
20210723:16:54:31 gpbackup:gpadmin:nna:002465-[INFO]:-Writing pre-data metadata
20210723:16:54:31 gpbackup:gpadmin:nna:002465-[INFO]:-Pre-data metadata backup complete
20210723:16:54:31 gpbackup:gpadmin:nna:002465-[INFO]:-Writing post-data metadata
20210723:16:54:32 gpbackup:gpadmin:nna:002465-[INFO]:-Post-data metadata backup complete
20210723:16:54:32 gpbackup:gpadmin:nna:002465-[INFO]:-Writing data to file
Tables backed up:  9  9 [==============================================================] 100.00% 3s
20210723:16:54:35 gpbackup:gpadmin:nna:002465-[INFO]:-Skipped data backup of 1 external/foreign table(s).
20210723:16:54:35 gpbackup:gpadmin:nna:002465-[INFO]:-See home/gpadmin/gpAdminLogs/gpbackup_20210723.log for a complete list of skipped tables.
20210723:16:54:35 gpbackup:gpadmin:nna:002465-[INFO]:-Data backup complete
20210723:16:54:36 gpbackup:gpadmin:nna:002465-[INFO]:-Found neither usr/local/greenplum-db/./bin/gp_email_contacts.yaml nor home/gpadmin/gp_email_contacts.yaml
20210723:16:54:36 gpbackup:gpadmin:nna:002465-[INFO]:-Email containing gpbackup report data/gpbackup/gpseg-1/backups/20210723/20210723165430/gpbackup_20210723165430_report will not be sent
20210723:16:54:36 gpbackup:gpadmin:nna:002465-[INFO]:-Backup completed successfully

模拟dd存储的情况，即是各节点服务器都有全部的备份目录

[gpadmin@nna gpbackup]$ scp -r gpseg-1 dn1:/data/gpbackup/
[gpadmin@nna gpbackup]$ scp -r gpseg-1 dn2:/data/gpbackup/

[gpadmin@dn1 gpbackup]$ scp -r gpseg0 nna:/data/gpbackup/  
[gpadmin@dn1 gpbackup]$ scp -r gpseg1 nna:/data/gpbackup/  
[gpadmin@dn1 gpbackup]$ scp -r gpseg0 dn2:/data/gpbackup/ 
[gpadmin@dn1 gpbackup]$ scp -r gpseg1 dn2:/data/gpbackup/

[gpadmin@dn2 gpbackup]$ scp -r gpseg2 nna:/data/gpbackup/  
[gpadmin@dn2 gpbackup]$ scp -r gpseg3 nna:/data/gpbackup/
[gpadmin@dn2 gpbackup]$ scp -r gpseg2 dn1:/data/gpbackup/
[gpadmin@dn2 gpbackup]$ scp -r gpseg3 dn1:/data/gpbackup/

恢复到temp库

[gpadmin@nna ~]$ createdb temp
[gpadmin@nna ~]$ gprestore --redirect-db temp  --backup-dir data/gpbackup/ --timestamp 20210723165430
20210723:17:01:17 gprestore:gpadmin:nna:002814-[INFO]:-Restore Key = 20210723165430
20210723:17:01:17 gprestore:gpadmin:nna:002814-[INFO]:-Restoring pre-data metadata
Pre-data objects restored:  27  27 [===================================================] 100.00% 0s
20210723:17:01:18 gprestore:gpadmin:nna:002814-[INFO]:-Pre-data metadata restore complete
Tables restored:  9  9 [===============================================================] 100.00% 1s
20210723:17:01:20 gprestore:gpadmin:nna:002814-[INFO]:-Data restore complete
20210723:17:01:20 gprestore:gpadmin:nna:002814-[INFO]:-Restoring post-data metadata
Post-data objects restored:  3  3 [====================================================] 100.00% 0s
20210723:17:01:20 gprestore:gpadmin:nna:002814-[INFO]:-Post-data metadata restore complete
20210723:17:01:20 gprestore:gpadmin:nna:002814-[INFO]:-Found neither usr/local/greenplum-db/./bin/gp_email_contacts.yaml nor home/gpadmin/gp_email_contacts.yaml
20210723:17:01:20 gprestore:gpadmin:nna:002814-[INFO]:-Email containing gprestore report data/gpbackup/gpseg-1/backups/20210723/20210723165430/gprestore_20210723165430_20210723170117_report will not be sent
20210723:17:01:21 gprestore:gpadmin:nna:002814-[INFO]:-Restore completed successfully

验证数据

跟备份前一致

test=# select count(*) from tmp_ao;
 count  
--------
 100000
(1 row)

test=# select count(*) from tmp_heap;
 count  
--------
 100000
(1 row)

test=# select count(*) from tmp_par;
 count 
-------
 21000
(1 row)

test=# \c temp
You are now connected to database "temp" as user "gpadmin".
temp=# select count(*) from tmp_ao;
 count  
--------
 100000
(1 row)

temp=# select count(*) from tmp_heap;
 count  
--------
 100000
(1 row)

temp=# select count(*) from tmp_par;
 count 
-------
 21000
(1 row)

增量备份

使用增量备份要注意的是，gpbackup全量基础备份和增量备份的参数要一样，而且平时备份需要有参数--leaf-partition-data ,，即全量备份也需要指定这个参数才行
每次的增量备份，heap表都会全量进行备份，ao表在有以下操作时，也会进行全量的备份
增量备份不支持参数 --leaf-partition-data
ALTER TABLEDELETEINSERTTRUNCATEUPDATEDROP and then re-create the table

首先做一个全量的基础备份

[gpadmin@nna ~]$ gpbackup --backup-dir data/gpbackup/ --dbname test  --no-compression --leaf-partition-data --jobs 2
20210715:09:35:48 gpbackup:gpadmin:nna:035015-[INFO]:-Starting backup of database test
20210715:09:35:49 gpbackup:gpadmin:nna:035015-[INFO]:-Backup Timestamp = 20210715093548
20210715:09:35:49 gpbackup:gpadmin:nna:035015-[INFO]:-Backup Database = test
20210715:09:35:49 gpbackup:gpadmin:nna:035015-[INFO]:-Gathering table state information
20210715:09:35:49 gpbackup:gpadmin:nna:035015-[INFO]:-Acquiring ACCESS SHARE locks on tables
Locks acquired:  14  14 [==============================================================] 100.00% 0s
20210715:09:35:49 gpbackup:gpadmin:nna:035015-[INFO]:-Gathering additional table metadata
20210715:09:35:49 gpbackup:gpadmin:nna:035015-[INFO]:-Metadata will be written to data/gpbackup/gpseg-1/backups/20210715/20210715093548/gpbackup_20210715093548_metadata.sql
20210715:09:35:49 gpbackup:gpadmin:nna:035015-[INFO]:-Writing global database metadata
20210715:09:35:49 gpbackup:gpadmin:nna:035015-[INFO]:-Global database metadata backup complete
20210715:09:35:49 gpbackup:gpadmin:nna:035015-[INFO]:-Writing pre-data metadata
20210715:09:35:50 gpbackup:gpadmin:nna:035015-[INFO]:-Pre-data metadata backup complete
20210715:09:35:50 gpbackup:gpadmin:nna:035015-[INFO]:-Writing post-data metadata
20210715:09:35:50 gpbackup:gpadmin:nna:035015-[INFO]:-Post-data metadata backup complete
20210715:09:35:50 gpbackup:gpadmin:nna:035015-[INFO]:-Writing data to file
Tables backed up:  12  12 [============================================================] 100.00% 0s
20210715:09:35:50 gpbackup:gpadmin:nna:035015-[INFO]:-Skipped data backup of 1 external/foreign table(s).
20210715:09:35:50 gpbackup:gpadmin:nna:035015-[INFO]:-See home/gpadmin/gpAdminLogs/gpbackup_20210715.log for a complete list of skipped tables.
20210715:09:35:50 gpbackup:gpadmin:nna:035015-[INFO]:-Data backup complete
20210715:09:35:51 gpbackup:gpadmin:nna:035015-[INFO]:-Found neither usr/local/greenplum-db/./bin/gp_email_contacts.yaml nor /home/gpadmin/gp_email_contacts.yaml
20210715:09:35:51 gpbackup:gpadmin:nna:035015-[INFO]:-Email containing gpbackup report /data/gpbackup/gpseg-1/backups/20210715/20210715093548/gpbackup_20210715093548_report will not be sent
20210715:09:35:51 gpbackup:gpadmin:nna:035015-[INFO]:-Backup completed successfully

检查备份表的信息

- schema: public
  name: tmp_heap
  oid: 16637
  attributestring: (aid,bid,abalance,filler)
  rowscopied: 100000
  partitionroot: ""
- schema: public
  name: tmp_ao
  oid: 16640
  attributestring: (aid,bid,abalance,filler)
  rowscopied: 100000
  partitionroot: ""
- schema: public
  name: tmp_par_1_prt_p01
  oid: 16653
  attributestring: (id,sfdm,tjyf)
  rowscopied: 5000
  partitionroot: tmp_par
  
[gpadmin@dn1 20210715093548]$ wc -l gpbackup_0_20210715093548_16637
26714 gpbackup_0_20210715093548_16637
[gpadmin@dn1 20210715093548]$ wc -l gpbackup_0_20210715093548_16640
26714 gpbackup_0_20210715093548_16640
[gpadmin@dn1 20210715093548]$ wc -l gpbackup_0_20210715093548_16653
1249 gpbackup_0_20210715093548_16653

分别往ao表、head表以及分区表插入数据

test=# insert into tmp_ao select * from pgbench_accounts limit 100000;
INSERT 0 100000
test=# insert into tmp_heap select * from pgbench_accounts limit 100000;
INSERT 0 100000
test=# insert into tmp_par select * from tmp_par;
INSERT 0 21000

增量备份,备份时指定上一个备份的时间戳

[gpadmin@nna 20210715]$ gpbackup --dbname test --incremental --from-timestamp 20210715093548 --backup-dir /data/gpbackup/ --no-compression --leaf-partition-data
20210715:09:38:54 gpbackup:gpadmin:nna:035080-[INFO]:-Starting backup of database test
20210715:09:38:55 gpbackup:gpadmin:nna:035080-[INFO]:-Backup Timestamp = 20210715093854
20210715:09:38:55 gpbackup:gpadmin:nna:035080-[INFO]:-Backup Database = test
20210715:09:38:55 gpbackup:gpadmin:nna:035080-[INFO]:-Gathering table state information
20210715:09:38:55 gpbackup:gpadmin:nna:035080-[INFO]:-Acquiring ACCESS SHARE locks on tables
Locks acquired:  14 / 14 [==============================================================] 100.00% 0s
20210715:09:38:55 gpbackup:gpadmin:nna:035080-[INFO]:-Gathering additional table metadata
20210715:09:38:55 gpbackup:gpadmin:nna:035080-[INFO]:-Metadata will be written to /data/gpbackup/gpseg-1/backups/20210715/20210715093854/gpbackup_20210715093854_metadata.sql
20210715:09:38:55 gpbackup:gpadmin:nna:035080-[INFO]:-Writing global database metadata
20210715:09:38:55 gpbackup:gpadmin:nna:035080-[INFO]:-Global database metadata backup complete
20210715:09:38:55 gpbackup:gpadmin:nna:035080-[INFO]:-Writing pre-data metadata
20210715:09:38:55 gpbackup:gpadmin:nna:035080-[INFO]:-Pre-data metadata backup complete
20210715:09:38:55 gpbackup:gpadmin:nna:035080-[INFO]:-Writing post-data metadata
20210715:09:38:55 gpbackup:gpadmin:nna:035080-[INFO]:-Post-data metadata backup complete
20210715:09:38:55 gpbackup:gpadmin:nna:035080-[INFO]:-Basing incremental backup off of backup with timestamp = 20210715093548
20210715:09:38:55 gpbackup:gpadmin:nna:035080-[INFO]:-Writing data to file
Tables backed up:  12 / 12 [============================================================] 100.00% 1s
20210715:09:38:56 gpbackup:gpadmin:nna:035080-[INFO]:-Skipped data backup of 1 external/foreign table(s).
20210715:09:38:56 gpbackup:gpadmin:nna:035080-[INFO]:-See /home/gpadmin/gpAdminLogs/gpbackup_20210715.log for a complete list of skipped tables.
20210715:09:38:56 gpbackup:gpadmin:nna:035080-[INFO]:-Data backup complete
20210715:09:38:57 gpbackup:gpadmin:nna:035080-[INFO]:-Found neither /usr/local/greenplum-db/./bin/gp_email_contacts.yaml nor /home/gpadmin/gp_email_contacts.yaml
20210715:09:38:57 gpbackup:gpadmin:nna:035080-[INFO]:-Email containing gpbackup report /data/gpbackup/gpseg-1/backups/20210715/20210715093854/gpbackup_20210715093854_report will not be sent
20210715:09:38:57 gpbackup:gpadmin:nna:035080-[INFO]:-Backup completed successfully

再检查备份表的信息

- schema: public
  name: tmp_heap
  oid: 16637
  attributestring: (aid,bid,abalance,filler)
  rowscopied: 200000
  partitionroot: ""
- schema: public
  name: tmp_ao
  oid: 16640
  attributestring: (aid,bid,abalance,filler)
  rowscopied: 200000
  partitionroot: ""
- schema: public
  name: tmp_par_1_prt_p01
  oid: 16653
  attributestring: (id,sfdm,tjyf)
  rowscopied: 10000
  partitionroot: tmp_par
  
[gpadmin@dn1 20210715093854]$ wc -l gpbackup_0_20210715093854_16637
49062 gpbackup_0_20210715093854_16637
[gpadmin@dn1 20210715093854]$ wc -l gpbackup_0_20210715093854_16640
50246 gpbackup_0_20210715093854_16640
[gpadmin@dn1 20210715093854]$ wc -l gpbackup_0_20210715093854_16653
2498 gpbackup_0_20210715093854_16653

可以看到heap表和ao表都进行了全量备份，因为ao表做了insert的操作

接着不对数据库做任何操作，再次进行增量备份

[gpadmin@nna gpseg-1]$  gpbackup --dbname test --incremental --backup-dir /data/gpbackup/ --no-compression --leaf-partition-data
20210715:09:56:48 gpbackup:gpadmin:nna:035363-[INFO]:-Starting backup of database test
20210715:09:56:48 gpbackup:gpadmin:nna:035363-[INFO]:-Backup Timestamp = 20210715095648
20210715:09:56:48 gpbackup:gpadmin:nna:035363-[INFO]:-Backup Database = test
20210715:09:56:48 gpbackup:gpadmin:nna:035363-[INFO]:-Gathering table state information
20210715:09:56:48 gpbackup:gpadmin:nna:035363-[INFO]:-Acquiring ACCESS SHARE locks on tables
Locks acquired:  14 / 14 [==============================================================] 100.00% 0s
20210715:09:56:48 gpbackup:gpadmin:nna:035363-[INFO]:-Gathering additional table metadata
20210715:09:56:48 gpbackup:gpadmin:nna:035363-[INFO]:-Metadata will be written to /data/gpbackup/gpseg-1/backups/20210715/20210715095648/gpbackup_20210715095648_metadata.sql
20210715:09:56:48 gpbackup:gpadmin:nna:035363-[INFO]:-Writing global database metadata
20210715:09:56:49 gpbackup:gpadmin:nna:035363-[INFO]:-Global database metadata backup complete
20210715:09:56:49 gpbackup:gpadmin:nna:035363-[INFO]:-Writing pre-data metadata
20210715:09:56:49 gpbackup:gpadmin:nna:035363-[INFO]:-Pre-data metadata backup complete
20210715:09:56:49 gpbackup:gpadmin:nna:035363-[INFO]:-Writing post-data metadata
20210715:09:56:49 gpbackup:gpadmin:nna:035363-[INFO]:-Post-data metadata backup complete
20210715:09:56:49 gpbackup:gpadmin:nna:035363-[INFO]:-Basing incremental backup off of backup with timestamp = 20210715095132
20210715:09:56:49 gpbackup:gpadmin:nna:035363-[INFO]:-Writing data to file
Tables backed up:  11 / 11 [============================================================] 100.00% 0s
20210715:09:56:50 gpbackup:gpadmin:nna:035363-[INFO]:-Skipped data backup of 1 external/foreign table(s).
20210715:09:56:50 gpbackup:gpadmin:nna:035363-[INFO]:-See /home/gpadmin/gpAdminLogs/gpbackup_20210715.log for a complete list of skipped tables.
20210715:09:56:50 gpbackup:gpadmin:nna:035363-[INFO]:-Data backup complete
20210715:09:56:51 gpbackup:gpadmin:nna:035363-[INFO]:-Found neither /usr/local/greenplum-db/./bin/gp_email_contacts.yaml nor /home/gpadmin/gp_email_contacts.yaml
20210715:09:56:51 gpbackup:gpadmin:nna:035363-[INFO]:-Email containing gpbackup report /data/gpbackup/gpseg-1/backups/20210715/20210715095648/gpbackup_20210715095648_report will not be sent
20210715:09:56:51 gpbackup:gpadmin:nna:035363-[INFO]:-Backup completed successfully

再看备份的数据情况

[gpadmin@dn1 20210715095648]$ ll -tr
total 32412
-rw-------. 1 gpadmin gpadmin        0 Jul 15 09:56 gpbackup_0_20210715095648_16625
-rw-------. 1 gpadmin gpadmin      201 Jul 15 09:56 gpbackup_0_20210715095648_16628
-rw-------. 1 gpadmin gpadmin 23997223 Jul 15 09:56 gpbackup_0_20210715095648_16631
-rw-------. 1 gpadmin gpadmin       10 Jul 15 09:56 gpbackup_0_20210715095648_16634
-rw-------. 1 gpadmin gpadmin  6613824 Jul 15 09:56 gpbackup_0_20210715095648_16637
-rw-------. 1 gpadmin gpadmin    36920 Jul 15 09:56 gpbackup_0_20210715095648_16653
-rw-------. 1 gpadmin gpadmin    36920 Jul 15 09:56 gpbackup_0_20210715095648_16661
-rw-------. 1 gpadmin gpadmin    43840 Jul 15 09:56 gpbackup_0_20210715095648_16668
-rw-------. 1 gpadmin gpadmin    36920 Jul 15 09:56 gpbackup_0_20210715095648_16675
-rw-------. 1 gpadmin gpadmin  2399904 Jul 15 09:56 gpbackup_0_20210715095648_16684
-rw-------. 1 gpadmin gpadmin        0 Jul 15 09:56 gpbackup_0_20210715095648_16687

可以看到，ao表(oid为16640)没有备份出来，因为没有对ao表做操作，感觉这增量备份作用不太大

恢复增量备份的数据

[gpadmin@nna ~]$ dropdb test
[gpadmin@nna ~]$ gprestore --backup-dir /data/gpbackup/ --timestamp 20210715095648 --create-db
20210715:10:03:44 gprestore:gpadmin:nna:035452-[INFO]:-Restore Key = 20210715095648
20210715:10:03:44 gprestore:gpadmin:nna:035452-[INFO]:-Creating database
20210715:10:03:45 gprestore:gpadmin:nna:035452-[INFO]:-Database creation complete for: test
20210715:10:03:45 gprestore:gpadmin:nna:035452-[INFO]:-Restoring pre-data metadata
Pre-data objects restored:  27 / 27 [===================================================] 100.00% 0s
20210715:10:03:46 gprestore:gpadmin:nna:035452-[INFO]:-Pre-data metadata restore complete
Tables restored:  12 / 12 [=============================================================] 100.00% 3s
20210715:10:03:50 gprestore:gpadmin:nna:035452-[INFO]:-Data restore complete
20210715:10:03:50 gprestore:gpadmin:nna:035452-[INFO]:-Restoring post-data metadata
Post-data objects restored:  3 / 3 [====================================================] 100.00% 0s
20210715:10:03:50 gprestore:gpadmin:nna:035452-[INFO]:-Post-data metadata restore complete
20210715:10:03:50 gprestore:gpadmin:nna:035452-[INFO]:-Found neither /usr/local/greenplum-db/./bin/gp_email_contacts.yaml nor /home/gpadmin/gp_email_contacts.yaml
20210715:10:03:50 gprestore:gpadmin:nna:035452-[INFO]:-Email containing gprestore report /data/gpbackup/gpseg-1/backups/20210715/20210715095648/gprestore_20210715095648_20210715100344_report will not be sent
20210715:10:03:50 gprestore:gpadmin:nna:035452-[INFO]:-Restore completed successfully

gpbackup原理

备份的数据其实就是调用了copy的命令，在备份时可以看到copy进程

COPY edw.sic_dm_dscdrug_md_cmh TO PROGRAM 'cat - > /mnt/gpboostfs/gpseg<SEGID>/backups/20210714/20210714010002/gpbackup_<SEGID>_20210714010002_4250654' WITH CSV DELIMITER ',' ON SEGMENT

copy ... on segment语法

导出

--语法1
test=# COPY public.tmp_heap TO PROGRAM 'cat - > /tmp/<SEGID>_tmp_heap.csv' WITH CSV DELIMITER ',' ON SEGMENT ;
COPY 100000
--语法2
test=# COPY public.tmp_heap TO '/tmp/<SEGID>_tmp_heap2.csv' WITH CSV DELIMITER ',' ON SEGMENT ;
COPY 100000

导入

--语法1
test=# COPY public.tmp_heap from PROGRAM 'cat /tmp/<SEGID>_tmp_heap.csv' WITH CSV DELIMITER ',' ON SEGMENT ;
COPY 100000

--语法2
test=# COPY public.tmp_heap from '/tmp/<SEGID>_tmp_heap2.csv' WITH CSV DELIMITER ',' ON SEGMENT ;
COPY 100000

并且可以用*匹配入库

test=# COPY public.tmp_heap from PROGRAM 'cat /tmp/<SEGID>_tmp_heap*.csv' WITH CSV DELIMITER ',' ON SEGMENT ;
COPY 200000

通过`_toc.yaml`
查询备份表的信息，例如记录数

[gpadmin@nna 20210714093247]$ cat gpbackup_20210714093247_toc.yaml|tr '\n' ' '|tr '-' '\n'|grep rowscopied|awk '{print $2"."$4"|"$10}'
public.pgbench_history|0
public.pgbench_tellers|100
public.pgbench_accounts|1000000
public.pgbench_branches|10
public.tmp_heap|100000
public.tmp_ao|100000
public.tmp_par|21000
public.tmp_heap_bak|100000
public.tmp|0

文章转载自叶同学专栏，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。