背景
数据库作为数据处理、存放等的核⼼模块,随着业务的发展,其数据量会越来越⼤;由于时间或业务设计逻辑的原因,会存在部分历史数据、归档数据。而业务对此类数据的访问并不频繁,但又不能删除,因为在某些场景下会使⽤到这些数据。为了提升数据库的处理性能,需要将此类数据进⾏落冷处理。
对于数据库而言,如何最⼤化地存储数据以及更好的提供统⼀数据处理接⼝尤为重要,腾讯云数据库 PostgreSQL 针对此类用户需求,提供数据分级存储方案。其核心原理是⽀持多种成本的存储介质,供⽤户选择使⽤。如,可使冷数据存放于性能略低,但成本低的存储中,将热数据存放于成本较⾼,但性能更强的高性能 SSD 中。更好的服务⽤户,保证业务的正常运行,并且兼顾⽤户成本,是⼀种极具性价⽐的存储⽅案。
方案简介
腾讯云 COS 是腾讯云提供的对象存储服务。分级存储当前实现的能力主要是基于 cos_fdw 插件连接和解析 COS 上的⽂件数据。
通过 cos_fdw 插件可以将 COS 中的数据加载到 PostgreSQL 数据库表中,像访问普通表⼀样访问 COS 中的数据,实现冷热存储分离。⽤户无需关心不同存储介质的访问形式,仅需要将 COS 存储中的数据文件配置到 PostgreSQL 数据库中即可。
方案优势
统⼀引擎:多种存储介质,⽆需业务层改动代码,直接使用 PostgreSQL 数据协议均可实现统⼀访问。
成本更低:相对⾼性能 SSD 存储,整体成本降低 86.25%。
使用简单:用户仅需要将源端数据导出 CSV 格式存放于 COS 中,在云数据库 PostgreSQL 基于插件进⾏外表创建,即可像原表⼀样直接使用。
无限存储:COS 存储容量不设上限,⽤户可以根据实际情况进行动态存储,不再担心容量问题。
⽀持联合查询表:多种存储的表⽀持联合查询,跨区 join 等,这在其他混合引擎上是⽆法直接实现的,均需要⼀个统一的数据融合节点才能⽀持。
支持版本
目前分级存储⽀持以下版本的云数据库 PostgreSQL:
PostgreSQL 10
PostgreSQL 11
PostgreSQL 12
PostgreSQL 13
PostgreSQL 14
PostgreSQL 15
PostgreSQL 16
使用 cos_fdw 的方法
需要按照以下顺序使⽤ cos_fdw :
1. 导出数据。
2. 上传⾄ COS。
3. 创建 cos_fdw 插件。
4. 创建 Foreign Server。
5. 创建 Foreign Table。
6. 查询外部表。
初始化环境
⾸先申请⼀个在数据库与 COS 同地域,同可⽤区的规格较小的中转服务器如 CVM。
操作系统建议为:Centos 7。
1. 安装 PostgreSQL 客户端,可参考 PostgreSQL 官网下载安装方案。
sudo yum install -yhttps://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpmsudo yum install -y postgresql13
2. 安装完成后,可使⽤ psql 命令访问⼀下数据库,查看是否安装完成,命令如下:
psql -Uroot -p 5432 -h 10.x.x.8 -d postgresPassword for user root:psql (13.6, server 13.3)Type "help" for help.postgres=>
3. PostgerSQL 客户端⼯具安装完成后,进⾏ COS 挂载。这里我们通过 COSFS 挂载到服务器上的形式来进行,可避免需要更⼤容量的 CVM 进⾏转储上传。请参见 通过 COSFS 挂载。
4. 针对当前环境,可执⾏以下命令安装依赖包。
sudo yum install libxml2-devel libcurl-devel -y
5. 访问 COSFS 的 github 下载地址,下载 COSFS 的安装包。
6. 下载完成后将此安装包上传⾄此服务器中。再执⾏下列命令将 COSFS 安装成功。
rpm -ivh cosfs-1.0.19-centos7.0.x86_64.rpm
注意:
如确定依赖包安装完成,但是依然⽆法安装成功 COSFS 的,可以在上⾯命令中加⼊ --force 参数强制安装。
7. 安装完成 COSFS 后,执⾏以下命令,将 COS 桶挂载到中转服务器中。
echo <BucketName-APPID>:<SecretId>:<SecretKey> > /etc/passwd-cosfschmod 640 /etc/passwd-cosfscosfs <BucketName-APPID> <MountPoint> -ourl=http://cos.<Region>.myqcloud.com -odbglevel=info -oallow_other
BucketName-APPID 为存储桶名称格式。
SecretId 和 SecretKey 为密钥信息。
8. 挂载完成后,可进入到挂载目录中,拷贝⼀个⽂件进⾏测试。查看是否挂载成功。亦可执行 df -h 查看挂载情况:
[root@VM-4-17-centos ~]# df -hFilesystem Size Used Avail Use% Mounted ondevtmpfs 1.9G 0 1.9G 0% /devtmpfs 1.9G 0 1.9G 0% /dev/shmtmpfs 1.9G 472K 1.9G 1% /runtmpfs 1.9G 0 1.9G 0% /sys/fs/cgroup/dev/vda1 50G 3.0G 44G 7% /tmpfs 379M 0 379M 0% /run/user/0cosfs 256T 0 256T 0% /mnt/pgstorage
导出数据
挂载完成后,即可进⾏数据导出。
如果存在⼀张表 sensor_log,表结构如下:
CREATE TABLE sensor_log (sensor_log_id SERIAL PRIMARY KEY,location VARCHAR NOT NULL,reading BIGINT NOT NULL,reading_date TIMESTAMP NOT NULL);CREATE INDEX idx_sensor_log_location ON sensor_log (location);CREATE INDEX idx_sensor_log_date ON sensor_log (reading_date);insert into sensor_log(location,reading,reading_date) values('38c-1401',293857,current_timestamp);insert into sensor_log(location,reading,reading_date) values('38c-1402',293858,current_timestamp);insert into sensor_log(location,reading,reading_date) values('34c-1401',293859,current_timestamp);insert into sensor_log(location,reading,reading_date) values('18c-1401',2938510,current_timestamp);
如果使用 psql 客户端进行数据导出,可按照以下流程进行操作,注意导出不要带 header。
导出整张表:
psql -U root -p 5432 -h 10.0.4.8 -d hehe -c \\COPY sensor_log(sensor_log_id,location, reading,reading_date) TO '/mnt/xxx/sensor_log.csv' WITHcsv;
指定数据导出(支持数据筛选,过滤,多表联合,视图等场景):
psql -U root -p 5432 -h 10.0.4.8 -d hehe -c '\\COPY (select * from sensor_logwhere location='18c-1401') TO '/mnt/pgstorage/sensor_log.csv' WITH csv;'
上面的语句执⾏完成后,就可以在 COS 桶对应⽬录中找到导出的⽂件。
导入到 COS 的 csv 文件不需要带列名。
创建插件
cos_fdw 插件会对 COS 的 secret id 和 secret key 进⾏加密处理,加密算法依赖于 pgcrypto 插件,因此我们在使⽤时需要先安装 pgcrypto 插件。
CREATE EXTENSION pgcrypto;CREATE EXTENSION cos_fdw;
创建 Foreign Server
CREATE SERVER cos_server FOREIGN DATA WRAPPER cos_fdw OPTIONS(host 'xxxxxx.cos.ap-nanjing.myqcloud.com',bucket 'xxxxxxxx',id 'xxxxxxxx',key 'xxxxxxxxxx');
注意:
host 中配置的域名为 COS 桶的访问地址,地址前缀协议不需要带 http 或 https。
Foreign Server 中的 id 和 key 属于敏感信息,cos_fdw 会对其进⾏加密存储。不同的实例将会使⽤不同的密钥,最⼤限度保护用户信息。我们可以⽤
SELECT * FROM pg_foreign_server;
看到。创建 COS 外部表
CREATE FOREIGN TABLE test_csv (word1 text OPTIONS (force_not_null 'true'),word2 text OPTIONS (force_not_null 'off') ) SERVER cos_server OPTIONS (filepath '/test.csv',format 'csv',null 'NULL');
cos_fdw ⽀持将多个 COS ⽂件可以映射到同⼀个 FOREIGN TABLE 中,在 filepath 参数中填写多个⽂件名,每个⽂件用
,
分隔即可(不允许出现多余空格)。CREATE FOREIGN TABLE multi_csv (word1 text OPTIONS (force_not_null 'true'),word2 text OPTIONS (force_not_null 'off') ) SERVER cos_server OPTIONS (filepath '/a.csv,/b.csv,/c.csv.2',format 'csv',null 'NULL');
查询外部表
规划查询计划
cos_fdw 能够预估外部文件的大小,为查询计划做规划。对于映射了多个 COS 文件的外部表,将会把它们每⼀个的文件大小打印出来,并计算出来所有文件的总大小。
-- 单⼀⽂件postgres=# EXPLAIN SELECT * FROM test_csv;QUERY PLAN-------------------------------------------------------------------------------Foreign Scan on test_csv (cost=0.00..1.10 rows=1 width=128)Foreign COS Url: https://xxxxxxx.cos.ap-nanjing.myqcloud.comForeign COS File Path: /test_csv.csvForeign each COS File Size(Bytes): 86Foreign total COS File Size(Bytes): 86(5 rows)-- 多个⽂件postgres=# EXPLAIN SELECT * FROM multi_csv;QUERY PLAN--------------------------------------------------------------------------------Foreign Scan on multi_csv (cost=0.00..1.20 rows=2 width=128)Foreign COS Url: https://xxxxxxxxxx.cos.ap-nanjing.myqcloud.comForeign COS File Path: /a.csv,/b.csv,/c.csv.2Foreign each COS File Size(Bytes): 15,172,86Foreign total COS File Size(Bytes): 273(5 rows)
查询数据
postgres=# SELECT * FROM test_csv;word1 | word2 | word3 | word4-------+-------+-------+-------AAA | aaa | 123 |XYZ | xyz | | 321NULL | | |NULL | | |ABC | abc | | (5 rows)
将外部表数据导入本地表
可以使⽤
insert into ... select * from ...;
类似的语句将外部表的数据导入本地表中。postgres=# CREATE TABLE local_test_csv (postgres(# a text,postgres(# b text,postgres(# c text,postgres(# d textpostgres(# );CREATE TABLEpostgres=# INSERT INTO local_test_csv SELECT * FROM test_csv;INSERT 0 5postgres=# SELECT * FROM local_test_csv;a | b | c | d------+-----+-----+-----AAA | aaa | 123 |XYZ | xyz | | 321NULL | | |NULL | | |ABC | abc | | (5 rows)
分区表查询
postgres=# CREATE TABLE pt (a int, b text) partition by list (a);CREATE TABLEpostgres=# CREATE FOREIGN TABLE p1 partition of pt for values in (1) SERVERcos_serverpostgres-# OPTIONS (format 'csv', filepath '/list1.csv', delimiter ',');CREATE FOREIGN TABLEpostgres=# CREATE TABLE p2 partition of pt for values in (2);CREATE TABLE-- 分区表⽀持查询postgres=# SELECT tableoid::regclass, * FROM pt;tableoid | a | b----------+---+-----p1 | 1 | foop1 | 1 | bar(2 rows)postgres=# SELECT tableoid::regclass, * FROM p1;tableoid | a | b----------+---+-----p1 | 1 | foop1 | 1 | bar(2 rows)postgres=# SELECT tableoid::regclass, * FROM p2;tableoid | a | b----------+---+---(0 rows)-- ⽬前不⽀持往外部表中写⼊数据postgres=# INSERT INTO pt VALUES (1, 'xyzzy'); -- ERRORERROR: cannot route inserted tuples to a foreign table-- 本地表不受影响,可以正常往分区表中写⼊postgres=# INSERT INTO pt VALUES (2, 'xyzzy');INSERT 0 1postgres=# SELECT tableoid::regclass, * FROM pt;tableoid | a | b----------+---+-------p1 | 1 | foop1 | 1 | barp2 | 2 | xyzzy(3 rows)postgres=# SELECT tableoid::regclass, * FROM p1;tableoid | a | b----------+---+-----p1 | 1 | foop1 | 1 | bar(2 rows)postgres=# SELECT tableoid::regclass, * FROM p2;tableoid | a | b----------+---+-------p2 | 2 | xyzzy(1 row)
删除插件
DROP EXTENSION cos_fdw;
参数说明
CERATE SERVER 参数
参数 | 说明 |
host | 内网访问 COS 的地址,注意 host 不包含 http/https 前缀 |
bucket | 存储桶名称,存储桶的命名格式为 BucketName-APPID,此处填写的存储桶名称必须为此格式 |
id | 账号的 secret id |
key | 账号的 secret key |
CREATE FOREIGN TABLE 参数
参数 | 说明 |
filepath | Sample |
format | 指定数据的格式,⽬前仅⽀持 csv |
delimiter | 指定数据的分隔符 |
quote | 指定数据的引⽤字符 |
escape | 指定数据的转义字符 |
encoding | 指定数据的编码 |
null | 指定匹配对应字符串的列为 null,例如 null ‘NULL’,即列值为 ’NULL’ 的字符串为 null |
force_not_null | 指定该列的值不应该与空字符串匹配。例如,force_not_null ‘id’ 表示:如果 id 列的值为空,则该值为空字符串,而不是 null |
force_null | 指定该列的值与空字符串匹配。例如,force_null ‘id’ 表示:如果 id 列的值为空,则该值为 null |
错误处理
当使用 cos_fdw 向 COS 请求数据超时,会显示以下内容:
code:出错请求的 HTTP 状态码。
• postgres=# SELECT * FROM test_csv; • ERROR: COS api return error. • DETAIL: COS api http status:403• HTTP/1.1 403 Forbidden• Content-Type: application/xml• Content-Length: 0 • Connection: keep-alive• Date: Thu, 07 Apr 2022 09:00:22 GMT• Server: tencent-cos• x-cos-request-id: NjI0ZWE4MjZfNDc1NGU0MDlfMjI3ZTJfMTI3YTJjMWM=• x-cos-trace-id:OGVmYzZiMmQzYjA2OWNhODk0NTRkMTBiOWVmMDAxODc0OWRkZjk0ZDM1NmI1M2E2MTRlY2MzZDhmNmI5MWI1OTBjYzE2MjAxN2M1MzJiOTdkZjMxMDVlYTZjN2FiMmI0MWMyZGYxMDAyZmVmMjNkZDQ5NGViMDhiZWJkOTE2YzI=