云数据库 PostgreSQL
有奖捉虫：行业应用 & 管理与支持文档专题 HOT
文档中心 > 云数据库 PostgreSQL > 最佳实践 > 通过 cos_fdw 插件支持分级存储能力

通过 cos_fdw 插件支持分级存储能力

最近更新时间：2024-02-22 17:38:21

我的收藏

本页目录：

背景

数据库作为数据处理、存放等的核?模块，随着业务的发展，其数据量会越来越?；由于时间或业务设计逻辑的原因，会存在部分历史数据、归档数据。而业务对此类数据的访问并不频繁，但又不能删除，因为在某些场景下会使?到这些数据。为了提升数据库的处理性能，需要将此类数据进?落冷处理。
对于数据库而言，如何最?化地存储数据以及更好的提供统?数据处理接?尤为重要，腾讯云数据库 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 -y 
https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-
x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo yum install -y postgresql13
2. 安装完成后，可使? psql 命令访问?下数据库，查看是否安装完成，命令如下：
psql -Uroot -p 5432 -h 10.x.x.8 -d postgres
Password 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-cosfs
chmod 640 /etc/passwd-cosfs
cosfs <BucketName-APPID> <MountPoint> -ourl=http://cos.<Region>.myqcloud.c
om -odbglevel=info -oallow_other
BucketName-APPID 为存储桶名称格式。
SecretId 和 SecretKey 为密钥信息。
8. 挂载完成后，可进入到挂载目录中，拷贝?个?件进?测试。查看是否挂载成功。亦可执行 df -h 查看挂载情况：
[root@VM-4-17-centos ~]# df -h
Filesystem Size Used Avail Use% Mounted on
devtmpfs 1.9G 0 1.9G 0% /dev
tmpfs 1.9G 0 1.9G 0% /dev/shm
tmpfs 1.9G 472K 1.9G 1% /run
tmpfs 1.9G 0 1.9G 0% /sys/fs/cgroup
/dev/vda1 50G 3.0G 44G 7% /
tmpfs 379M 0 379M 0% /run/user/0
cosfs 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' WITH 
csv;
指定数据导出（支持数据筛选，过滤，多表联合，视图等场景）：
psql -U root -p 5432 -h 10.0.4.8 -d hehe -c '\\COPY (select * from sensor_log 
where 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.com
 Foreign COS File Path: /test_csv.csv
 Foreign each COS File Size(Bytes): 86
 Foreign 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.com
 Foreign COS File Path: /a.csv,/b.csv,/c.csv.2
 Foreign each COS File Size(Bytes): 15,172,86
 Foreign total COS File Size(Bytes): 273
(5 rows)

查询数据

postgres=# SELECT * FROM test_csv;
word1 | word2 | word3 | word4 
-------+-------+-------+-------
AAA | aaa | 123 |
XYZ | xyz | | 321
NULL | | |
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 text
postgres(# );
CREATE TABLE
postgres=# INSERT INTO local_test_csv SELECT * FROM test_csv;
INSERT 0 5
postgres=# SELECT * FROM local_test_csv;
 a | b | c | d 
------+-----+-----+-----
AAA | aaa | 123 |
XYZ | xyz | | 321
NULL | | |
NULL | | |
ABC | abc | | (5 rows)

分区表查询

postgres=# CREATE TABLE pt (a int, b text) partition by list (a);
CREATE TABLE
postgres=# CREATE FOREIGN TABLE p1 partition of pt for values in (1) SERVER
cos_server
postgres-# OPTIONS (format 'csv', filepath '/list1.csv', delimiter ',');
CREATE FOREIGN TABLE
postgres=# CREATE TABLE p2 partition of pt for values in (2);
CREATE TABLE
-- 分区表?持查询
postgres=# SELECT tableoid::regclass, * FROM pt;
tableoid | a | b 
----------+---+-----
p1 | 1 | foo
p1 | 1 | bar
(2 rows)
postgres=# SELECT tableoid::regclass, * FROM p1;
tableoid | a | b 
----------+---+-----
p1 | 1 | foo
p1 | 1 | bar
(2 rows)
postgres=# SELECT tableoid::regclass, * FROM p2;
tableoid | a | b 
----------+---+---
(0 rows)
-- ?前不?持往外部表中写?数据
postgres=# INSERT INTO pt VALUES (1, 'xyzzy'); -- ERROR
ERROR: cannot route inserted tuples to a foreign table
-- 本地表不受影响，可以正常往分区表中写?
postgres=# INSERT INTO pt VALUES (2, 'xyzzy');
INSERT 0 1
postgres=# SELECT tableoid::regclass, * FROM pt;
tableoid | a | b 
----------+---+-------
p1 | 1 | foo
p1 | 1 | bar
p2 | 2 | xyzzy
(3 rows)
postgres=# SELECT tableoid::regclass, * FROM p1;
tableoid | a | b 
----------+---+-----
p1 | 1 | foo
p1 | 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 状态码。
错误请求的 HTTP header：显示错误的信息。其格式参见 公共响应头部。其中 x-cos-request-id 可以?于寻求 在线支持 排查问题。如果该项为空，表示未成功向 COS 发送请求。
? 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=
?
中国站


http://www.vxiaotou.com