PostgreSQL数据备份教程

1. 介绍

• 本文为您介绍通过ClickHouse对PostgreSQL数据库进行数据备份（冷备数据存放至COS）

• 冷备地址：COS对象存储-qianyi-backup：/history_db/{db_name}/{table_name}/{data_time}/data_{timestamp}.csv

2. 备份流程

2.1 ClickHouse创建临时数据表

• 每次备份都需要删除历史临时表，创建新临时表;

--临时表删除
drop table IF EXISTS dmp_test.temp_platformdb_creative_tracker_sync_history ON CLUSTER default_cluster ;

--临时表创建
CREATE TABLE IF NOT EXISTS dmp_test.temp_platformdb_creative_tracker_sync_history ON CLUSTER default_cluster
(
    record_id           Nullable(String)  ,
    creative_id         Nullable(String)  ,
    state               Nullable(String)  ,
    logged_on           Nullable(String)  ,
    created_by          Nullable(String)  ,
    created_on          Nullable(String)  ,
    retry_times         Nullable(String)  ,
    identify            Nullable(String)  ,
    trigger_type        Nullable(String)  
) 
ENGINE=S3(concat('http://qianyi-backup-1305502582.cos.ap-shanghai.myqcloud.com/history_db/platformdb/creative_tracker_sync_history/',toString(today()),'/data_',toString(toUnixTimestamp(now())),'.csv'), 'CSV');

2.2数据导入

• 通过ClickHouse表函数postgresql() 连接PostgreSQL 表，进行查询导入；

-- 表函数 postgresql('host:5432', 'db_name', 'table_name', 'user_name', 'password') 
insert into dmp_test.temp_platformdb_creative_tracker_sync_history
select *
from postgresql('10.80.16.9:5432', 'platformdb', 'creative_tracker_sync', 'wayio', 'HsQ9tE3C') limit 100 ;

2.3数据查询

• 新建备份数据查询（一次创建，后续有新备份数据可直接检索出来）;

-- 新建备份数据表（正式表，一次创建）
CREATE TABLE IF NOT EXISTS dmp_test.platformdb_creative_tracker_sync_history ON CLUSTER default_cluster
(
    record_id           Nullable(String)  ,
    creative_id         Nullable(String)  ,
    state               Nullable(String)  ,
    logged_on           Nullable(String)  ,
    created_by          Nullable(String)  ,
    created_on          Nullable(String)  ,
    retry_times         Nullable(String)  ,
    identify            Nullable(String)  ,
    trigger_type        Nullable(String)  
) 
ENGINE=S3('http://qianyi-backup-1305502582.cos.ap-shanghai.myqcloud.com/history_db/platformdb/creative_tracker_sync_history/*/data_*.csv', 'CSV');

-- 备份数据查询
select * from dmp_test.platformdb_creative_tracker_sync_history limit 100;