文档中心>腾讯云数据仓库 TCHouse-P

冷备数据

最近更新时间:2021-11-12 15:49:34

我的收藏
本文主要介绍如何在业务侧周期备份数据。

背景

尽管云数据仓库 PostgreSQL 对数据做了主备,但是在某些场景下,仍然需要对重要数据进行全量冷备,例如异常删除数据。由于目前云数据仓库 PostgreSQL 暂不支持自动冷备数据,因此需要业务侧手动完成相关工作。在云数据仓库 PostgreSQL 中,数据备份使用 COS 作为存储介质,对 COS 数据操作可参考 使用外表高速导入或导出 COS 数据

影响

使用本文提及方法进行数据备份,会对集群造成以下影响,需要提前注意:
1. 脚本运行会提高集群负载,特别网络侧开销较大,建议评估好备份时间,在业务低峰期进行。
2. 脚本运行会在每个库创建一个 COS 插件。
3. 脚本运行会对每张需要备份的表创建一张 COS 外表,备份结束后会进行删除。

问题

使用本文提交方法进行数据备份,可能会遇到以下问题:
报错信息
处理办法
ERROR: permission denied for external protocol cos
GRANT ALL ON PROTOCOL cos TO {backup_user}
ERROR: permission denied for schema {schame_name}
GRANT ALL ON SCHEMA {schame_name} to {backup_user}
ERROR: permission denied for relation {table_name}
GRANT SELECT ON {table_name} to {backup_user}

步骤

以下 shell 脚本提供了备份整个云数据仓库 PostgreSQL 集群数据的功能,用户可根据需要进行扩充,配合 crontab 完成周期冷备的任务,也可直接下载使用 backup_cdw_v101.sh
注意
删除可写外表,不会删除 COS 上对应数据。
1. 备份数据,会导致系统负载升高,建议在系统空闲时运行。
2. 备份时间取决于数据量以及集群规格,简单来说集群节点数越多,备份速度越快。
#!/bin/bash

set -e

# 云数据仓库 PostgreSQL 连接参数,需要填写
PWD='' # 必填
HOST='' # 必填
USER='' # 必填
DEFAULT_DB='postgres'

# 备份参数,需要填写
SECRET_ID='' # 必填
SECRET_KEY='' # 必填
COS_URL='' # 必填 类似 test-1301111111.cos.ap-guangzhou.myqcloud.com
COMPRESS_TYPE='gzip' # COS 上的文件是否采用压缩格式,支持 gzip|none

echo -e "\\n`date "+%Y%m%d %H:%M:%S"` backup task start\\n"

# step1 : 获取数据库列表
db_list=`PGPASSWORD=${PWD} psql -t -h ${HOST} -p 5436 -d ${DEFAULT_DB} -U ${USER} -c "select datname from pg_database"`

# step2 : 遍历需要备份的数据库
for db in $db_list
do
# template0 template1 gpperfmon 3个db属于模板以及系统库,不需要备份
if [ "$db" = "template0" -o $db = "template1" -o $db = "gpperfmon" ];then
continue
fi

echo -e "\\n************************************************"
echo -e "backup database:{$db} start"
db_start=`date +%s`

# step3 : 获取当前日期
# 使用日期作为 COS 存储路径的一部分,以此区分不同日期备份的数据
cur_date=`date +%Y%m%d`

# step4 : 获取需要备份的列表
# 这里去掉了外表,虚拟表,临时表,复制表(暂不支持),对于分区表,只备份子表
table_list=`PGPASSWORD=${PWD} psql -t -h ${HOST} -p 5436 -d ${db} -U ${USER} -c "SELECT t.schemaname||'.'||t.tablename FROM pg_class c join (SELECT a.schemaname,a.tablename,b.oid FROM pg_tables a join pg_namespace b on a.schemaname = b.nspname WHERE a.tableowner != 'gpadmincloud') as t on c.relnamespace = t.oid and c.relname = t.tablename join gp_distribution_policy d on c.oid = d.localoid WHERE c.relstorage not in('v','x') and c.relpersistence != 't' and c.relhassubclass != 't' and d.policytype != 'r'"`

# step5 : 创建cos插件
PGPASSWORD=${PWD} psql -h ${HOST} -p 5436 -d ${db} -U ${USER} -c "CREATE EXTENSION IF NOT EXISTS cos_ext SCHEMA public"

# step6 : 遍历列表,依次备份
for table in $table_list
do
sleep 1
table_start=`date +%s`
echo -e "backup ${table} start"
# 这里命名必须加在后面,格式是{schema}.{table}
backup_table="${table}_cdw_backup_cos"

# step7 : 创建 COS 备份表
PGPASSWORD=${PWD} psql -h ${HOST} -p 5436 -d ${db} -U ${USER} -c "CREATE WRITABLE EXTERNAL TABLE ${backup_table} (like ${table}) LOCATION('cos://${COS_URL}/backup/${cur_date}/${db}/${table}/ secretKey=${SECRET_KEY} secretId=${SECRET_ID} compressType=${COMPRESS_TYPE}') FORMAT 'csv'"


# step8 : 导入原表数据到备份表
PGPASSWORD=${PWD} psql -h ${HOST} -p 5436 -d ${db} -U ${USER} -c "INSERT INTO ${backup_table} SELECT * FROM ${table}"

# step9 : 删除备份外表
# 注:删除外表不会删除COS上对应的数据
PGPASSWORD=${PWD} psql -h ${HOST} -p 5436 -d ${db} -U ${USER} -c "DROP EXTERNAL TABLE ${backup_table}"

table_end=`date +%s`
echo -e "backup ${table} done, cost $[table_end - table_start]s\\n"
done

db_end=`date +%s`
echo -e "backup database:{$db} done, cost $[db_end - db_start]s"
echo -e "************************************************\\n"
done