tencent cloud

#!/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