【greenplum】6.21.3 备份恢复

阿提艾斯

已于 2024-08-29 11:41:31 修改

阅读量1k

点赞数 30

分类专栏：数据库文章标签： greenplum 数据库

于 2024-08-06 11:30:47 首次发布

本文链接：https://blog.csdn.net/qq_37871657/article/details/136985361

版权

数据库专栏收录该内容

12 篇文章 0 订阅

订阅专栏

1. 备份环境

工具	版本	备注
greenplum	6.21.3
备份工具 gpbackup	1.30.2
恢复工具 gprestore	1.30.2

2. 安装备份工具

从Pivotal Network下载适用于的Greenplum数据库版本和OS平台的最新Pivotal Greenplum Backup and Restore软件发行版.
greenplum_backup_restore-1.30.2-gp6-rhel7-x86_64.gppkg

安装gpbackup/restore工具

su - gpadmin
gppkg -i greenplum_backup_restore-1.30.2-gp6-rhel7-x86_64.gppkg

查看是否安装成功

gpbackup --version
gprestore --version

3. 备份

3.1 备份选项

在这里插入图片描述

选项	含义	备注
–backup-dir string	保存备份文件的目录的绝对路径
–compression-level int	数据备份期间使用的压缩级别。	有效值的范围取决于压缩类型(默认值为1)，zstd压缩级别范围1-19, gzip压缩级别范围1-9。
–compression-type string	数据备份期间使用的压缩类型。	有效值gzip, zstd(默认值为gzip)
–copy-queue-size int	使用–single data file选项进行备份时，gpbackup应排队的COPY命令数(默认值为1)
–data-only	仅备份数据，不备份元数据
–dbname string	要备份的数据库	必填。
–debug	打印详细和调试日志消息
–exclude-schema stringArray	备份除指定架构中的对象之外的所有元数据，–可以多次指定排除结构。
–exclude-schema-file string	包含要从备份中排除的架构列表的文件
–exclude-table stringArray	备份指定表以外的所有元数据 --可以多次指定排除表。
–exclude-table-file string	包含要从备份中排除的完全限定表列表的文件
–from-timestamp string	用于建立当前增量备份的时间戳
–include-schema stringArray	仅备份指定的架构–可以多次指定include架构。
–include-schema-file string	包含要包含在备份中的架构列表的文件
–include-table stringArray	仅备份指定的表–可以多次指定include表。
–include-table-file string	包含要包含在备份中的完全限定表列表的文件。
–incremental	仅备份自上次备份以来已修改的AO表的数据。
–jobs int	备份数据时要使用的并行连接数。	默认值为1。不能和–metadata-only, --single-data-file参数一起使用。
–leaf-partition-data	对于分区表，为每个业分区创建一个数据文件，而不是为整个表创建一个文件。	备份必须参数，增量备份要求全备和增备都指定这个参数。
–metadata-only	仅备份元数据，不备份数据。
–no-compression	跳过数据文件的压缩。
–plugin-config string	用于插件的配置文件
–quiet	禁止显示非警告、非错误日志消息。
–single-data-file	将所有数据备份到单个文件，而不是每个表一个文件。	不能和–jobs共用，加上这个参数后，所有的表文件会被合入到一个文件中，相当于合并了一下。
–verbose	打印详细日志消息
–version	打印版本号并退出。
–with-stats	备份查询计划统计。
–without-globals	跳过全局元数据备份。

3.2 备份准备

创建备份目录

gpssh -f /home/gpadmin/conf/all_host 'sudo mkdir /opt/backup'
gpssh -f /home/gpadmin/conf/all_host 'sudo chmod -R 775 /opt/backup'
gpssh -f /home/gpadmin/conf/all_host 'sudo chown -R gpadmin. /opt/backup'

3.3 全量备份

全量备份

gpbackup --dbname test001 --backup-dir /opt/backup/ --leaf-partition-data --single-data-file --compression-type zstd --compression-level 19

集群中自带的数据库备份测试结果如下：
template0不能进行备份，报错如下： FATAL: database “template0” is not currently accepting connections (SQLSTATE 55000)
template1可以备份
postgres可以备份
gp_sydb可以备份
gpperfmon可以备份

备份之后的文件信息

master备份目录下的文件信息

[root@mdw1 ~]# ll /opt/backup/gpseg-1/backups/20240109/20240109102909/
总用量 24
-r--r--r-- 1 gpadmin gpadmin  881 1月   9 10:29 gpbackup_20240109102909_config.yaml
-r--r--r-- 1 gpadmin gpadmin 4417 1月   9 10:29 gpbackup_20240109102909_metadata.sql
-r--r--r-- 1 gpadmin gpadmin 1865 1月   9 10:29 gpbackup_20240109102909_report
-r--r--r-- 1 gpadmin gpadmin 7319 1月   9 10:29 gpbackup_20240109102909_toc.yaml

gpbackup_20240109102909_config.yaml

[root@mdw1 ~]# cat /opt/backup/gpseg-1/backups/20240109/20240109102909/gpbackup_20240109102909_config.yaml
backupdir: /opt/backup/
backupversion: 1.30.2
compressed: true
compressiontype: gzip
databasename: test001
databaseversion: 6.21.3 build commit:622fa9a831d189db93b0e2c338f1379d7d5f6e9d Open
  Source
segmentcount: 4
dataonly: false
datedeleted: ""
excluderelations: []
excludeschemafiltered: false
excludeschemas: []
excludetablefiltered: false
includerelations: []
includeschemafiltered: false
includeschemas: []
includetablefiltered: false
incremental: false
leafpartitiondata: true
metadataonly: false
plugin: ""
pluginversion: ""
restoreplan:
- timestamp: "20240109102909"
  tablefqns:
  - public.backup_tb
  - public.backup_tb_copy1
  - public.backup_tb_copy2
  - public.backup_tb_copy3
  - public.backup_tb_copy4
  - public.backup_tb_copy5
singledatafile: false
timestamp: "20240109102909"
endtime: "20240109102909"
withoutglobals: false
withstatistics: false
status: Success

gpbackup_20240109102909_metadata.sql

[root@mdw1 ~]# cat /opt/backup/gpseg-1/backups/20240109/20240109102909/gpbackup_20240109102909_metadata.sql
SET client_encoding = 'UTF8';
ALTER RESOURCE QUEUE pg_default WITH (ACTIVE_STATEMENTS=20);
ALTER RESOURCE GROUP admin_group SET CPU_RATE_LIMIT 1;
ALTER RESOURCE GROUP admin_group SET MEMORY_LIMIT 1;
ALTER RESOURCE GROUP default_group SET CPU_RATE_LIMIT 1;
ALTER RESOURCE GROUP default_group SET MEMORY_LIMIT 1;
ALTER RESOURCE GROUP default_group SET MEMORY_LIMIT 0;
ALTER RESOURCE GROUP default_group SET MEMORY_SHARED_QUOTA 80;
ALTER RESOURCE GROUP default_group SET MEMORY_SPILL_RATIO 0;
ALTER RESOURCE GROUP default_group SET CONCURRENCY 20;
ALTER RESOURCE GROUP default_group SET CPU_RATE_LIMIT 30;
ALTER RESOURCE GROUP admin_group SET MEMORY_LIMIT 10;
ALTER RESOURCE GROUP admin_group SET MEMORY_SHARED_QUOTA 80;
ALTER RESOURCE GROUP admin_group SET MEMORY_SPILL_RATIO 0;
ALTER RESOURCE GROUP admin_group SET CONCURRENCY 10;
ALTER RESOURCE GROUP admin_group SET CPU_RATE_LIMIT 10;
CREATE ROLE gpadmin;
ALTER ROLE gpadmin WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION PASSWORD 'md5b44a9b06d576a0b083cd60e5f875cf48' RESOURCE QUEUE pg_default RESOURCE GROUP admin_group CREATEEXTTABLE (protocol='http') CREATEEXTTABLE (protocol='gpfdist', type='readable') CREATEEXTTABLE (protocol='gpfdist', type='writable');
CREATE ROLE gpmon;
ALTER ROLE gpmon WITH SUPERUSER INHERIT NOCREATEROLE CREATEDB LOGIN PASSWORD 'md5172c791e492002d91ef60418bb9d5d72' RESOURCE QUEUE pg_default RESOURCE GROUP admin_group;
CREATE ROLE gpcc_basic;
ALTER ROLE gpcc_basic WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN RESOURCE QUEUE pg_default RESOURCE GROUP default_group;
CREATE ROLE gpcc_operator;
ALTER ROLE gpcc_operator WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN RESOURCE QUEUE pg_default RESOURCE GROUP default_group;
CREATE ROLE gpcc_operator_basic;
ALTER ROLE gpcc_operator_basic WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN RESOURCE QUEUE pg_default RESOURCE GROUP default_group;
CREATE DATABASE test001 TEMPLATE template0;
ALTER DATABASE test001 OWNER TO gpadmin;
COMMENT ON SCHEMA public IS 'standard public schema';
ALTER SCHEMA public OWNER TO gpadmin;
REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM gpadmin;
GRANT ALL ON SCHEMA public TO PUBLIC;
GRANT ALL ON SCHEMA public TO gpadmin;
CREATE TABLE public.backup_za (
        id bigint NOT NULL,
        name character varying(255),
        age smallint,
        create_time timestamp without time zone,
        update_time timestamp without time zone
) DISTRIBUTED BY (id);
ALTER TABLE public.backup_za OWNER TO gpadmin;
CREATE TABLE public.backup_tb_copy1 (
        id bigint NOT NULL,
        name character varying,
        age smallint,
        create_time timestamp without time zone,
        update_time timestamp without time zone
) DISTRIBUTED BY (id);
ALTER TABLE public.backup_tb_copy1 OWNER TO gpadmin;
CREATE TABLE public.backup_tb_copy2 (
        id bigint NOT NULL,
        name character varying,
        age smallint,
        create_time timestamp without time zone,
        update_time timestamp without time zone
) DISTRIBUTED BY (id);
ALTER TABLE public.backup_tb_copy2 OWNER TO gpadmin;
CREATE TABLE public.backup_tb_copy3 (
        id bigint NOT NULL,
        name character varying,
        age smallint,
        create_time timestamp without time zone,
        update_time timestamp without time zone
) DISTRIBUTED BY (id);
ALTER TABLE public.backup_tb_copy3 OWNER TO gpadmin;
CREATE TABLE public.backup_tb_copy4 (
        id bigint NOT NULL,
        name character varying,
        age smallint,
        create_time timestamp without time zone,
        update_time timestamp without time zone
) DISTRIBUTED BY (id);
ALTER TABLE public.backup_tb_copy4 OWNER TO gpadmin;
CREATE TABLE public.backup_tb_copy5 (
        id bigint NOT NULL,
        name character varying,
        age smallint,
        create_time timestamp without time zone,
        update_time timestamp without time zone
) DISTRIBUTED BY (id);
ALTER TABLE public.backup_tb_copy5 OWNER TO gpadmin;
ALTER TABLE ONLY public.backup_za ADD CONSTRAINT backup_tb_pkey PRIMARY KEY (id);
ALTER TABLE ONLY public.backup_tb_copy1 ADD CONSTRAINT backup_tb_copy1_pkey PRIMARY KEY (id);
ALTER TABLE ONLY public.backup_tb_copy2 ADD CONSTRAINT backup_tb_copy2_pkey PRIMARY KEY (id);
ALTER TABLE ONLY public.backup_tb_copy3 ADD CONSTRAINT backup_tb_copy3_pkey PRIMARY KEY (id);
ALTER TABLE ONLY public.backup_tb_copy4 ADD CONSTRAINT backup_tb_copy4_pkey PRIMARY KEY (id);
ALTER TABLE ONLY public.backup_tb_copy5 ADD CONSTRAINT backup_tb_copy5_pkey PRIMARY KEY (id);

gpbackup_20240109102909_report

[root@mdw1 ~]# cat /opt/backup/gpseg-1/backups/20240109/20240109102909/gpbackup_20240109102909_report 
Greenplum Database Backup Report

timestamp key:         20240109102909
gpdb version:          6.21.3 build commit:622fa9a831d189db93b0e2c338f1379d7d5f6e9d Open Source
gpbackup version:      1.30.2

database name:         test001
command line:          gpbackup --dbname test001 --backup-dir /opt/backup/ --leaf-partition-data
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:            Tue Jan 09 2024 10:29:09
end time:              Tue Jan 09 2024 10:29:12
duration:              0:00:03

backup status:         Success

database size:         112 MB
segment count:         4

count of database objects in backup:
aggregates                   0
casts                        0
collations                   0
constraints                  6
conversions                  0
default privileges           0
database gucs                0
event triggers               0
extensions                   0
foreign data wrappers        0
foreign servers              0
functions                    0
indexes                      0
operator classes             0
operator families            0
operators                    0
procedural languages         0
protocols                    0
resource groups              2
resource queues              1
roles                        5
rules                        0
schemas                      1
sequences                    0
tables                       6
tablespaces                  0
text search configurations   0
text search dictionaries     0
text search parsers          0
text search templates        0
triggers                     0
types                        0
user mappings                0
views                        0

gpbackup_20240109102909_toc.yaml

[root@mdw1 ~]# cat /opt/backup/gpseg-1/backups/20240109/20240109102909/gpbackup_20240109102909_toc.yaml 
globalentries:
- schema: ""
  name: ""
  objecttype: SESSION GUCS
  referenceobject: ""
  startbyte: 0
  endbyte: 31
  tier:
  - 0
  - 0
- schema: ""
  name: pg_default
  objecttype: RESOURCE QUEUE
  referenceobject: ""
  startbyte: 31
  endbyte: 93
  tier:
  - 0
  - 0
- schema: ""
  name: admin_group
  objecttype: RESOURCE GROUP
  referenceobject: ""
  startbyte: 93
  endbyte: 149
  tier:
  - 0
  - 0
- schema: ""
  name: admin_group
  objecttype: RESOURCE GROUP
  referenceobject: ""
  startbyte: 149
  endbyte: 203
  tier:
  - 0
  - 0
- schema: ""
  name: default_group
  objecttype: RESOURCE GROUP
  referenceobject: ""
  startbyte: 203
  endbyte: 261
  tier:
  - 0
  - 0
- schema: ""
  name: default_group
  objecttype: RESOURCE GROUP
  referenceobject: ""
  startbyte: 261
  endbyte: 317
  tier:
  - 0
  - 0
- schema: ""
  name: default_group
  objecttype: RESOURCE GROUP
  referenceobject: ""
  startbyte: 317
  endbyte: 373
  tier:
  - 0
  - 0
- schema: ""
  name: default_group
  objecttype: RESOURCE GROUP
  referenceobject: ""
  startbyte: 373
  endbyte: 437
  tier:
  - 0
  - 0
- schema: ""
  name: default_group
  objecttype: RESOURCE GROUP
  referenceobject: ""
  startbyte: 437
  endbyte: 499
  tier:
  - 0
  - 0
- schema: ""
  name: default_group
  objecttype: RESOURCE GROUP
  referenceobject: ""
  startbyte: 499
  endbyte: 555
  tier:
  - 0
  - 0
- schema: ""
  name: default_group
  objecttype: RESOURCE GROUP
  referenceobject: ""
  startbyte: 555
  endbyte: 614
  tier:
  - 0
  - 0
- schema: ""
  name: admin_group
  objecttype: RESOURCE GROUP
  referenceobject: ""
  startbyte: 614
  endbyte: 669
  tier:
  - 0
  - 0
- schema: ""
  name: admin_group
  objecttype: RESOURCE GROUP
  referenceobject: ""
  startbyte: 669
  endbyte: 731
  tier:
  - 0
  - 0
- schema: ""
  name: admin_group
  objecttype: RESOURCE GROUP
  referenceobject: ""
  startbyte: 731
  endbyte: 791
  tier:
  - 0
  - 0
- schema: ""
  name: admin_group
  objecttype: RESOURCE GROUP
  referenceobject: ""
  startbyte: 791
  endbyte: 845
  tier:
  - 0
  - 0
- schema: ""
  name: admin_group
  objecttype: RESOURCE GROUP
  referenceobject: ""
  startbyte: 845
  endbyte: 902
  tier:
  - 0
  - 0
- schema: ""
  name: gpadmin
  objecttype: ROLE
  referenceobject: ""
  startbyte: 902
  endbyte: 1244
  tier:
  - 0
  - 0
- schema: ""
  name: gpmon
  objecttype: ROLE
  referenceobject: ""
  startbyte: 1244
  endbyte: 1433
  tier:
  - 0
  - 0
- schema: ""
  name: gpcc_basic
  objecttype: ROLE
  referenceobject: ""
  startbyte: 1433
  endbyte: 1593
  tier:
  - 0
  - 0
- schema: ""
  name: gpcc_operator
  objecttype: ROLE
  referenceobject: ""
  startbyte: 1593
  endbyte: 1759
  tier:
  - 0
  - 0
- schema: ""
  name: gpcc_operator_basic
  objecttype: ROLE
  referenceobject: ""
  startbyte: 1759
  endbyte: 1937
  tier:
  - 0
  - 0
- schema: ""
  name: test001
  objecttype: DATABASE
  referenceobject: ""
  startbyte: 1939
  endbyte: 1984
  tier:
  - 0
  - 0
- schema: ""
  name: test001
  objecttype: DATABASE METADATA
  referenceobject: ""
  startbyte: 1984
  endbyte: 2027
  tier:
  - 0
  - 0
predataentries:
- schema: public
  name: public
  objecttype: SCHEMA
  referenceobject: ""
  startbyte: 2027
  endbyte: 2028
  tier: []
- schema: public
  name: public
  objecttype: SCHEMA
  referenceobject: ""
  startbyte: 2028
  endbyte: 2084
  tier: []
- schema: public
  name: public
  objecttype: SCHEMA
  referenceobject: ""
  startbyte: 2084
  endbyte: 2124
  tier: []
- schema: public
  name: public
  objecttype: SCHEMA
  referenceobject: ""
  startbyte: 2124
  endbyte: 2286
  tier: []
- schema: public
  name: backup_za
  objecttype: TABLE
  referenceobject: ""
  startbyte: 2288
  endbyte: 2494
  tier:
  - 1
  - 1
incrementalmetadata:
  ao: {}

segment host上备份目录下的文件信息

[root@sdw1 ~]# ll /opt/backup/gpseg0/backups/20240109/20240109102909/
总用量 24
-rw------- 1 gpadmin gpadmin 1674 1月   9 10:29 gpbackup_0_20240109102909_16392.gz
-rw------- 1 gpadmin gpadmin 1674 1月   9 10:29 gpbackup_0_20240109102909_16397.gz
-rw------- 1 gpadmin gpadmin 1674 1月   9 10:29 gpbackup_0_20240109102909_16405.gz
-rw------- 1 gpadmin gpadmin 1674 1月   9 10:29 gpbackup_0_20240109102909_16413.gz
-rw------- 1 gpadmin gpadmin 1674 1月   9 10:29 gpbackup_0_20240109102909_16421.gz
-rw------- 1 gpadmin gpadmin 1674 1月   9 10:29 gpbackup_0_20240109102909_16429.gz

[root@sdw1 ~]# ll /opt/backup/gpseg1/backups/20240109/20240109102909/
总用量 24
-rw------- 1 gpadmin gpadmin 1657 1月   9 10:29 gpbackup_1_20240109102909_16392.gz
-rw------- 1 gpadmin gpadmin 1657 1月   9 10:29 gpbackup_1_20240109102909_16397.gz
-rw------- 1 gpadmin gpadmin 1657 1月   9 10:29 gpbackup_1_20240109102909_16405.gz
-rw------- 1 gpadmin gpadmin 1657 1月   9 10:29 gpbackup_1_20240109102909_16413.gz
-rw------- 1 gpadmin gpadmin 1657 1月   9 10:29 gpbackup_1_20240109102909_16421.gz
-rw------- 1 gpadmin gpadmin 1657 1月   9 10:29 gpbackup_1_20240109102909_16429.gz

这些.gz文件解压之后，都是csv文件，里面保存着表的数据。

3,187635467,2,2023-12-01 12:00:00,2023-12-01 12:00:00
4,187635567,2,2023-12-01 12:00:00,2023-12-01 12:00:00
7,187635867,2,2023-12-01 12:00:00,2023-12-01 12:00:00
8,187635967,2,2023-12-01 12:00:00,2023-12-01 12:00:00
18,187636967,2,2023-12-01 12:00:00,2023-12-01 12:00:00
19,187637067,2,2023-12-01 12:00:00,2023-12-01 12:00:00
22,187637367,2,2023-12-01 12:00:00,2023-12-01 12:00:00
27,187637867,2,2023-12-01 12:00:00,2023-12-01 12:00:00
29,187638067,2,2023-12-01 12:00:00,2023-12-01 12:00:00

3.4 增量备份

增量备份

gpbackup --dbname test001 --backup-dir /opt/backup/ --leaf-partition-data --incremental --single-data-file --compression-type zstd --compression-level 19

–leaf-partition-data: 必须，增量备份时，基于的全量必须要该参数，增量备份时也必须要该参数。
–incremental: 必须，不能将--data-only或--metadata-only和--incremental一起使用。
–from-timestamp: 可选。指定的时间戳是一个存在的备份对应的时间戳，可以是基于全量或增量备份。
–dbname: 数据库必须相同。
–backup-dir: 文件夹必须相同。备份集，全量和增量备份路径必须相同。
–single-data-file: 这个选项在集合中所有的备份，要么全部指定，要么全不指定。将所有数据备份到单个文件。
–no-compression: 备份集要么都压缩，要么都不压缩。备份集中的备份允许不同的压缩级别。

如果不做全量备份，直接做增量备份，根据report信息可以看到备份失败：

[gpadmin@mdw1 ~]$ gpbackup --dbname test001 --backup-dir /opt/backup/ --leaf-partition-data --incremental
20240109:10:26:43 gpbackup:gpadmin:mdw1:022034-[INFO]:-gpbackup version = 1.30.2
20240109:10:26:44 gpbackup:gpadmin:mdw1:022034-[INFO]:-Greenplum Database Version = 6.21.3 build commit:622fa9a831d189db93b0e2c338f1379d7d5f6e9d Open Source
20240109:10:26:44 gpbackup:gpadmin:mdw1:022034-[INFO]:-Starting backup of database test001
20240109:10:26:44 gpbackup:gpadmin:mdw1:022034-[INFO]:-Backup Timestamp = 20240109102644
20240109:10:26:44 gpbackup:gpadmin:mdw1:022034-[INFO]:-Backup Database = test001
20240109:10:26:44 gpbackup:gpadmin:mdw1:022034-[CRITICAL]:-There was no matching previous backup found with the flags provided. Please take a full backup.
20240109:10:26:45 gpbackup:gpadmin:mdw1:022034-[INFO]:-Found neither /usr/local/greenplum-db/bin/gp_email_contacts.yaml nor /home/gpadmin/gp_email_contacts.yaml
20240109:10:26:45 gpbackup:gpadmin:mdw1:022034-[INFO]:-Email containing gpbackup report /opt/backup/gpseg-1/backups/20240109/20240109102644/gpbackup_20240109102644_report will not be sent

做增量备份时，如果把segment host上全量备份的gz文件删除，再发起增量备份，是可以正常备份的。
做增量备份时，如果把master上全量备份的文件删除，再发起增量备份，不能备份成功，报错如下：

[gpadmin@mdw1 ~]$ gpbackup --dbname test001 --backup-dir /opt/backup/ --leaf-partition-data --incremental
20240109:11:00:57 gpbackup:gpadmin:mdw1:026222-[INFO]:-gpbackup version = 1.30.2
20240109:11:00:57 gpbackup:gpadmin:mdw1:026222-[INFO]:-Greenplum Database Version = 6.21.3 build commit:622fa9a831d189db93b0e2c338f1379d7d5f6e9d Open Source
20240109:11:00:57 gpbackup:gpadmin:mdw1:026222-[INFO]:-Starting backup of database test001
20240109:11:00:57 gpbackup:gpadmin:mdw1:026222-[INFO]:-Backup Timestamp = 20240109110057
20240109:11:00:57 gpbackup:gpadmin:mdw1:026222-[INFO]:-Backup Database = test001
20240109:11:00:57 gpbackup:gpadmin:mdw1:026222-[INFO]:-Gathering table state information
20240109:11:00:57 gpbackup:gpadmin:mdw1:026222-[INFO]:-Acquiring ACCESS SHARE locks on tables
Locks acquired:  6 / 6 [================================================================] 100.00% 0s
20240109:11:00:57 gpbackup:gpadmin:mdw1:026222-[INFO]:-Gathering additional table metadata
20240109:11:00:58 gpbackup:gpadmin:mdw1:026222-[INFO]:-Getting partition definitions
20240109:11:00:58 gpbackup:gpadmin:mdw1:026222-[INFO]:-Getting storage information
20240109:11:00:58 gpbackup:gpadmin:mdw1:026222-[INFO]:-Getting child partitions with altered schema
20240109:11:00:58 gpbackup:gpadmin:mdw1:026222-[INFO]:-Metadata will be written to /opt/backup/gpseg-1/backups/20240109/20240109110057/gpbackup_20240109110057_metadata.sql
20240109:11:00:58 gpbackup:gpadmin:mdw1:026222-[INFO]:-Basing incremental backup off of backup with timestamp = 20240109105639
20240109:11:00:58 gpbackup:gpadmin:mdw1:026222-[CRITICAL]:-open /opt/backup/gpseg-1/backups/20240109/20240109105639/gpbackup_20240109105639_toc.yaml: no such file or directory
20240109:11:00:59 gpbackup:gpadmin:mdw1:026222-[INFO]:-Found neither /usr/local/greenplum-db/bin/gp_email_contacts.yaml nor /home/gpadmin/gp_email_contacts.yaml
20240109:11:00:59 gpbackup:gpadmin:mdw1:026222-[INFO]:-Email containing gpbackup report /opt/backup/gpseg-1/backups/20240109/20240109110057/gpbackup_20240109110057_report will not be sent

3.5 查看历史备份记录

gpbackup_manager list-backups

3.6 删除备份

使用gpbackup_manager工具删除

删除指定备份

gpbackup_manager delete-backup 20240108184800

删除指定备份之前的备份集，下方例子会删除20240108184730之前的备份集，但是不会删除20240108184730这次备份。

gpbackup_manager delete-backups-before 20240108184730

4. 恢复

4.1 恢复选项

在这里插入图片描述

选项	含义	备注
–backup-dir string
–copy-queue-size int
–create-db	元数据恢复之前创建数据库	如果数据库存在，则会报错：[CRITICAL]:-Database “test001” already exists. Run gprestore again without --create-db flag.
–data-only	只恢复数据，不恢复元数据
–debug	打印调试信息
–exclude-schema stringArray
–exclude-schema-file string
–exclude-table stringArray
–exclude-table-file string
–include-schema stringArray
–include-schema-file string
–include-table stringArray
–include-table-file string
–incremental	只恢复heap表和AO表自上次备份后修改的数据。
–jobs int	恢复并发连接数	当备份的时候使用了–single-data-file，恢复的时候不能指定该参数。
–metadata-only
–on-error-continue	发生错误继续恢复
–plugin-config string
–quiet
–redirect-db string	恢复到指定的数据库，而不是备份的数据库。	需要和–create-db一起使用。
–redirect-schema string	恢复到指定模式，而不是备份时的模式。
–report-dir string	恢复报错和错误表将写入到这个目录下，绝对路径。
–resize-cluster
–run-analyze
–timestamp string	恢复到具体的时间点，格式：YYYYMMDDHHMMSS	必填。
–truncate-table	清空表
–verbose
–version
–with-globals
–with-stats

4.2 恢复命令

gprestore --backup-dir /opt/backup/ --timestamp 20240514001813 --create-db --with-globals -jobs 8

gprestore --backup-dir /opt/backup/ --timestamp 20240109112419 --verbose

5. 补充

gpbackup或gprestore完成后返回的状态码含义：

0: 备份或还原已完成，没有任何问题。
1：备份或还原已完成，但非致命错误。
2：备份或还原失败，并出现致命错误。

6. 专业名词

交换分区
分区表
Heap表
即使用MEMORY存储引擎的表，这种表的数据存储在内存中，由于硬件问题或者断电，数据容易丢失，所以只能从其他数据表中读取数据作为临时表或者只读缓存来使用。
AO表
AO最初设计是只支持append的，APPEND-ONLY。

7. 参考资料

下载gpbackup/gprestore工具
https://network.pivotal.io/products/greenplum-backup-restore/#/releases/1424076/file_groups/17406
gpbackup/gprestore github地址
https://github.com/greenplum-db/gpbackup/releases
GreenPlum备份和恢复工具之gpbackup和gprestore
https://www.modb.pro/db/1720258731890532352
Greenplum备份恢复工具gpbackup主题（上）- 简介与源码编译
https://cn.greenplum.org/backup_tool_gpbackup/
Greenplum备份恢复工具gpbackup——参数
https://cn.greenplum.org/gpbackup_-parameter/
PostgreSQL 技术内幕(二) Greenplum-AO表
https://zhuanlan.zhihu.com/p/587753029
greenplum archive_mode默认
https://blog.51cto.com/u_16175455/7391999?articleABtest=0
使用gpbackup和gprestore创建增量备份
https://docs-cn.greenplum.org/v6/admin_guide/managing/backup-gpbackup-incremental.html#topic_kvf_mkr_t2b