冷数据轻松转储到OSS

文中参考文档在github需点击阅读原文打开, 同时推荐2个学习环境: 

1、懒人Docker镜像, 已打包200+插件:《最好的PostgreSQL学习镜像》

2、有web浏览器就能用的云起实验室: 《免费体验PolarDB开源数据库》

3、PolarDB开源数据库内核、最佳实践等学习图谱:  https://www.aliyun.com/database/openpolardb/activity 

关注公众号, 持续发布PostgreSQL、PolarDB、DuckDB等相关文章. 

PostgreSQL冷数据轻松转储到OSS

冷数据放在数据库中, 弊端比较多:

• 占用昂贵的存储

• 备份空间需求变大

• 全量数据备份的时间拉长

• 恢复需要更大的空间

• vacuum freeze的时间变长, 严重的情况甚至导致xid回卷

• 不小心查到冷数据可能冲击shared buffer、long query导致膨胀等一系列隐藏风险. ps: 一部分场景PG使用ring buffer的设计缓解了访问大表导致的热数据从buffer挤出的问题.

冷数据适合丢到数据湖, 好处多多, 例如可以和其他业务打通, 打破数据孤岛. 老头子tom lane的公司crunchydata也在偷偷搞这个功能, crunchydata bridge就是干这个的 https://www.crunchydata.com/blog/crunchy-bridge-for-analytics-your-data-lake-in-postgresql , pg接入数据湖产品会越来越丝滑, 通过fdw、table access method等.

pg_tier是一个结合parquet_s3_fdw使用的插件, 方便将表转储到oss, 同时将本地表转换为外部表.

• https://github.com/tembo-io/pg_tier

安装很简单, 依赖rust

cd /tmp  
git clone --depth 1 https://github.com/tembo-io/pg_tier   
cd pg_tier  
  # pgrx 版本请参考不同版本的  Cargo.toml 文件   
cargo install --locked --version 0.11.3 cargo-pgrx      
cargo pgrx init    # create PGRX_HOME 后, 立即ctrl^c 退出          
cargo pgrx init --pg14=`which pg_config`    # 不用管报警     
PGRX_IGNORE_RUST_VERSIONS=y cargo pgrx install --release --pg-config `which pg_config`    
  postgres=# create extension pg_tier ;  
ERROR:  required extension "parquet_s3_fdw" is not installed  
HINT:  Use CREATE EXTENSION ... CASCADE to install required extensions too.  

使用方法

配置oss认证

select tier.set_tier_credentials('my-storage-bucket','AWS_ACCESS_KEY', 'AWS_SECRET_KEY','AWS_REGION');  

创建一张本地表

create table people (  
    name text not null,  
    age numeric not null  
);  

写入一些数据

insert into people values ('Alice', 34), ('Bob', 45), ('Charlie', 56);  

创建本地表在oss的关系

Initializes remote storage (S3) for the table.

select tier.create_tier_table('people');  

将本地表的数据迁移到原创表, 并且将本地表替换为外部表

Moves the local table into remote storage (S3).

select tier.execute_tiering('people');  

查询外部表

select * from people;  

  name   | age  
---------+-----  
 Alice   |  34  
 Bob     |  45  
 Charlie |  56  

转换后, 本地表就变成外部表了

postgres=# \d people  
                  Foreign table "public.people"  
 Column |  Type   | Collation | Nullable | Default | FDW options  
--------+---------+-----------+----------+---------+--------------  
 name   | text    |           | not null |         | (key 'true')  
 age    | numeric |           | not null |         | (key 'true')  
Server: pg_tier_s3_srv  
FDW options: (dirname 's3://my-storage-bucket/public_people/')  

postgres=# explain analyze select * from people;  
                                               QUERY PLAN  
---------------------------------------------------------------------------------------------------------  
 Foreign Scan on people  (cost=0.00..0.09 rows=9 width=64) (actual time=126.438..126.444 rows=9 loops=1)  
   Reader: Single File  
   Row groups: 1  
 Planning Time: 440.560 ms  
 Execution Time: 172.527 ms  
(5 rows)  

由于使用的是parquet_s3_fdw, 远程可能使用的是parquet格式.

欢迎关注我的github (https://github.com/digoal/blog) , 学习数据库不迷路.  
近期正在写公开课材料, 未来将通过视频号推出, 欢迎关注视频号:
文章中的参考文档请点击阅读原文获得.