腾讯云数据仓库 TCHouse-P
有奖捉虫：办公协同&微信生态&物联网文档专题 HOT
文档中心 > 腾讯云数据仓库 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

中国站