云服务器特惠 99/年起
  1. 腾讯云优惠网首页
  2. 腾讯云产品

云数据库 PostgreSQL 通过 cos_fdw 插件支持分级存储能力

• 腾讯云产品

数据库作为数据处理、存放等的核⼼模块，随着业务的发展，其数据量会越来越⼤；由于时间或业务设计逻辑的原因，会存在部分历史数据、归档数据。而业务对此类数据的访问并不频繁，但又不能删除，因为在某些场景下会使⽤到这些数据。为了提升数据库的处理性能，需要将此类数据进⾏落冷处理。

相关产品
云数据库 PostgreSQL 产品页，含版本规格、价格与购买入口。

一、背景

对于数据库而言，如何最⼤化地存储数据以及更好的提供统⼀数据处理接⼝尤为重要，腾讯云数据库 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.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. PostgreSQL 客户端⼯具安装完成后，进⾏ 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' 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');

十、创建 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 | | 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 状态码。

·错误请求的 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=

相关内容：

  1. 云数据库 PostgreSQL 使用 starrocks_fdw 实现读写分离 云数据库 PostgreSQL 支持自研的 starrocks_fdw 插件，让你能够将 starrocks 中的表作为 PostgreSQL 的外部表，从而实现冷热存储分离。该插件的使用兼容 mysql_fdw 插件，以下为一个使用示例，更多详细的参数及使用请参考 mysql_fdw 页面。这篇的前提是你已经部署完毕 starrocks ，且可以访问。同时你已经购买了云数据库 PostgreSQL 实例。...
  2. 云数据库 PostgreSQL 跨库访问 跨库访问是指对本实例中其他库中的数据，或其他实例中的数据进行数据读取、写入、联合操作的过程。跨库访问的目标对象统称为外部数据源。云数据库 PostgreSQL 提供用于访问外部数据源的插件，用于实现、增强跨库访问能力。外部访问插件包括两种。...
  3. 云数据库 PostgreSQL pg_partman 这篇说说腾讯云PostgreSQL的pg_partman插件，用于自动化管理原生分区表，支持按时间或ID自动创建、路由和维护分区，并提供了创建分区表、配置策略、查看分区、数据写入及维护的完整操作步骤。 - 插件功能：自动预创建未来分区、清理过期分区，减少手动管理。 - 创建分区：使用create_parent函数配置分区策略，支持daily、hourly等间隔。 - 维护与查看：run_maintenance执行维护。...
  4. 云数据库 PostgreSQL pg_hint_plan 这篇说说pg_hint_plan插件，通过SQL注释提示控制优化器的执行计划，支持强制指定扫描方式、连接方式和行数估计，用于优化器选择不当的场景。 扫描方式提示：支持SeqScan、IndexScan等强制改变扫描类型。 连接方式提示：支持NestLoop、HashJoin等强制改变连接方式。 行数估计提示：通过Rows调整连接结果行数估计值。...
  5. 云数据库 PostgreSQL 使用 pglogical 进行逻辑复制 pglogical 是 PostgreSQL 的逻辑复制扩展插件，使用“发布/订阅”的数据模型进行数据复制。该模型包含“发布端”和“订阅端”，发布端用于定义和发布指定的数据，订阅端可以选择订阅，从而接收并使用数据变更。pglogical 允许用户只复制特定的数据内容，减少了不必要的数据传输和处理，使选择性复制更加高效。...
  6. 云数据库 PostgreSQL pgvector 这篇说说腾讯云pgvector插件，支持向量存储、相似度检索与HNSW/IVFFlat索引，适用于语义搜索、推荐等场景，并给出创建表、插入数据、距离查询及索引创建的完整示例。 向量类型与距离：支持L2、内积、余弦三种距离，通过操作符或函数计算。 索引选择：HNSW查询快但内存大，IVFFlat内存小适合大数据量。 使用注意：创建索引时操作符类需与距离类型一致，内积操作符返回负值。...
  7. 云数据库 PostgreSQL 子查询上拉优化 原生 PostgreSQL 的 pull_up_sublinks 只能上拉 ANY / EXISTS / NOT EXISTS 等有限形式的子查询；对于以下常。...
  8. 云数据库 PostgreSQL 数据库权限概述 PostgreSQL 的权限管理模型，是一种典型的 RBAC（基于角色的权限控制）的实现。其将用户、角色、权限通过此模型管理起来。PostgreSQL 中的“用户”概念和“角色”概念实际是等同的，唯一的区别在于“用户”拥有 login 权限，而“角色”拥有 nologin 权限。...
赞 (0)
上一篇 4天前
下一篇 4天前