tencent cloud

#!/bin/bash

set -e

# Tencent Cloud TCHouse-P connection parameters that need to be entered
PWD='' # Required
HOST='' # Required
USER='' # Required
DEFAULT_DB='postgres'

# Backup parameters that need to be entered
SECRET_ID='' # Required
SECRET_KEY='' # Required
COS_URL='' # Required, such as `test-1301111111.cos.ap-guangzhou.myqcloud.com`
COMPRESS_TYPE='gzip' # Whether the files in COS are in compressed format. Valid values, gzip, none

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

# Step 1. Get the list of databases
db_list=`PGPASSWORD=${PWD} psql -t -h ${HOST} -p 5436 -d ${DEFAULT_DB} -U ${USER} -c "select datname from pg_database"`

# Step 2. Traverse the databases that need to be backed up
for db in $db_list
do
    # `template0`, `template1`, and `gpperfmon` are templates and system database and do not need to be backed up
        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`

    # Step 3. Get the current date
    # Use the date as part of the COS path to distinguish between data backed up on different dates
    cur_date=`date +%Y%m%d`	

    # Step 4. Get the list of tables that need to be backed up
    # External, virtual, temporary, and replicated tables (not supported currently) are excluded. For partitioned tables, only child tables are backed up 
    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'"`

    # Step 5. Create a COS extension
    PGPASSWORD=${PWD} psql -h ${HOST}  -p 5436 -d ${db} -U ${USER} -c "CREATE EXTENSION IF NOT EXISTS cos_ext SCHEMA public"

    # Step 6. Traverse the list and perform backups in sequence
    for table in $table_list
    do
        sleep 1
        table_start=`date +%s`
        echo -e "backup ${table} start"
            # Here, a name suffix must be added in the format of `{schema}.{table}`
            backup_table="${table}_cdw_backup_cos"

        # Step 7. Create COS backup tables
        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'"


        # Step 8. Import the data of original tables to backup tables
        PGPASSWORD=${PWD} psql -h ${HOST}  -p 5436 -d ${db} -U ${USER} -c "INSERT INTO ${backup_table} SELECT * FROM ${table}"

        # Step 9. Delete the backup external tables
        # Note: Deleting an external table will not delete the corresponding data in 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