TencentDB for PostgreSQL Supporting Tiered Storage Based on cos

Example
As the core component for data storage, processing, and refinement, databases grow in size with the expansion of business operations. Due to time or business design logic, some historical and archived data may exist. Although access to such data is infrequent, it cannot be deleted, as it may be needed in certain scenarios. To enhance the database's processing performance, it is necessary to implement cold storage solutions for this type of data.
For databases, maximizing data storage and providing a unified data processing interface are crucial. TencentDB for PostgreSQL offers a tiered storage solution to address these user needs. The core principle is to support various storage media with different cost-performance ratios for users to choose from. For example, cold data can be stored in lower-performance but cost-effective storage, while hot data can be stored in high-performance SSDs with higher costs. This storage solution effectively serves users, ensures the smooth operation of their businesses, and balances cost considerations, making it a highly cost-effective option.
Solution Summary
Tencent Cloud COS is an object storage service provided by Tencent Cloud. The current tiered storage capability is primarily implemented through the cos_fdw plugin, which connects to and parses file data on COS. 

By using the cos_fdw plugin, data from COS can be loaded into PostgreSQL database tables, allowing users to access COS data as if it were a regular table, thus achieving cold and hot storage separation. Users do not need to worry about different storage media access methods; they only need to configure the data files in COS storage to the PostgreSQL database.
Solution strengths
Unified Engine: With various storage media, there is no need to modify the code at the business layer. Directly using the PostgreSQL data protocol enables unified access.
Lower Cost: Compared to high-performance SSD storage, the overall cost is reduced by 86.25%.
Easy to use: Users only need to export the source data in CSV format and store it in COS. Then, in TencentDB for PostgreSQL, create an external table based on the plugin, and it can be used just like the original table.
Unlimited Storage: COS storage capacity has no upper limit, allowing users to dynamically store data based on their actual needs without worrying about capacity constraints.
Support for joining tables: Tables with various storage types support join operations, including cross-region joins, which cannot be directly implemented on other hybrid engines and require a unified data fusion node to support.
Supported Versions
Currently, tiered storage is supported for the following TencentDB for PostgreSQL versions:
PostgreSQL 10
PostgreSQL 11
PostgreSQL 12
PostgreSQL 13
PostgreSQL 14
Using cos_fdw
Use cos_fdw in the following steps:
1. Export the data.
2. Upload the data to COS.
3. Create the cos_fdw extension.
4. Create a foreign server.
5. Create a foreign table.
6. Query the foreign table.
Initializing Environment
First, you need to apply for a relay server, such as a CVM instance, with a low specification in the same region and AZ as the database and COS bucket.
Recommended OS: CentOS 7.
1. Install the PostgreSQL client by following the instructions in PostgreSQL Official Website Download and Installation Guide.
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. After the installation is completed, run the psql command to access the database and check whether the client is installed successfully:
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. After the PostgreSQL client tools are installed, proceed with mounting COS. In this case, we will use COSFS to mount it on the server, which avoids the need for larger capacity CVMs for transferring and uploading. Please refer to Mounting with COSFS.
4. Run the following command to install dependency packages for your current environment:
sudo yum install libxml2-devel libcurl-devel -y
5. Visit the GitHub download page for COSFS to download the COSFS installation package.
6. After downloading, upload the installation package to the server. Then, execute the following command to successfully install COSFS.
rpm -ivh cosfs-1.0.19-centos7.0.x86_64.rpm
Note
If dependency packages are installed, but COSFS still cannot be installed successfully, add the --force parameter to the command to forcibly install it.
7. After installing COSFS, run the following command to mount the COS bucket to the relay server.
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 is the format of the bucket name.
SecretId and SecretKey are the key information.
8. After the mounting is completed, navigate to the mounted directory and copy a file for testing to verify if the mounting was successful. You can also run df -h to check the mounting status:
[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
Export data
After mounting is completed, export the data.
If the sensor_log table exists, it needs to be in the following structure:
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);
If you are using the psql client to export data, follow the steps below, ensuring that the export does not include headers.
Export the entire table:
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;
Designated data export (supports data filtering, multi-table joins, views, and other scenarios):
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;'
After the above statement is executed, you can find the exported file in the corresponding directory in the COS bucket.
The CSV file exported to COS doesn't need to contain column names.
Creating a Plugin
The cos_fdw extension will encrypt the secret ID and secret key of COS. The encryption algorithm relies on the pgcrypto extension. Therefore, you need to install pgcrypto first.
CREATE EXTENSION pgcrypto;
CREATE EXTENSION cos_fdw;
Creating 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'
);
Note
The domain name configured in host is the access address of the COS bucket. The address doesn't need to contain the http or https prefix as the protocol.
The ID and key in the Foreign Server are sensitive information, and cos_fdw encrypts them for secure storage. Different instances will use different keys to maximize user information protection. This can be viewed using the command: SELECT * FROM pg_foreign_server;.
Creating COS Foreign Table
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 supports mapping multiple COS files to the same FOREIGN TABLE. To do this, enter multiple file names in the filepath parameter, separated by commas , (extra spaces are not allowed).
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'
);
Querying Foreign Table
Scheduling query plan
cos_fdw can estimate the size of external files, aiding in query planning. For external tables that map multiple COS files, it will print the size of each file and calculate the total size of all files.
-- Single file
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)
-- Multiple files
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)
Query data
postgres=# SELECT * FROM test_csv;
word1 | word2 | word3 | word4 
-------+-------+-------+-------
AAA | aaa | 123 |
XYZ | xyz | | 321
NULL | | |
NULL | | |
ABC | abc | | (5 rows)
Importing data from foreign table to local table
You can use a statement like INSERT INTO ... SELECT * FROM ...; to import data from external tables into local tables.
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)
Querying partitioned table
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
-- Partitioned tables can be queried
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)
-- Currently, data cannot be written to foreign tables
postgres=# INSERT INTO pt VALUES (1, 'xyzzy'); -- ERROR
ERROR: cannot route inserted tuples to a foreign table
-- As local tables are not affected, data can be written to local partitioned tables normally.
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)
Delete Plugin
DROP EXTENSION cos_fdw;
Description
Create Server Parameters
Category
Note
host
Private network access address for COS, note that the host does not include the http/https prefix.
bucket
The bucket name must follow the naming format: BucketName-APPID.
id
Account's secret id
key
Account's secret key
Create Foreign Table Parameters
Category
Note
filepath
Sample
format
Specify the data format, currently only supporting CSV.
delimiter
Specify the Data Separator
quote
Specifying the Reference Character for Data
escape
Specify the escape character for data
encoding
Specify Data Encoding
null
Specify columns with matching strings as null, for example, null 'NULL', which means the column value with the string 'NULL' is considered null.
force_not_null
Specify that the value of this column should not match an empty string. For example, force_not_null 'id' indicates: if the value of the id column is empty, the value will be an empty string instead of null.
force_null
Specify that the value of this column matches an empty string. For example, force_null 'id' indicates: if the value of the id column is empty, then the value is null.
Error handling
When a data request sent by cos_fdw to COS times out, the following will be displayed:
code: HTTP status code of the abnormal request.
Error request's HTTP header: Displays error information. The format can be found in Common Response Headers. The x-cos-request-id can be used to seek online support for troubleshooting. If this field is empty, it indicates that the request to COS was unsuccessful.
• 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=
﻿
Supporting Tiered Storage Based on cos_fdw Extension

On this page:

Example

Solution Summary

Solution strengths

Supported Versions

Using cos_fdw

Initializing Environment

Export data

Creating a Plugin

Creating Foreign Server

Creating COS Foreign Table

Querying Foreign Table

Scheduling query plan

Query data

Importing data from foreign table to local table

Querying partitioned table

Delete Plugin

Description

Error handling

Category	Note
host	Private network access address for COS, note that the host does not include the http/https prefix.
bucket	The bucket name must follow the naming format: BucketName-APPID.
id	Account's secret id
key	Account's secret key
Category	Note
filepath	Sample
format	Specify the data format, currently only supporting CSV.
delimiter	Specify the Data Separator
quote	Specifying the Reference Character for Data
escape	Specify the escape character for data
encoding	Specify Data Encoding
null	Specify columns with matching strings as null, for example, null 'NULL', which means the column value with the string 'NULL' is considered null.
force_not_null	Specify that the value of this column should not match an empty string. For example, force_not_null 'id' indicates: if the value of the id column is empty, the value will be an empty string instead of null.
force_null	Specify that the value of this column matches an empty string. For example, force_null 'id' indicates: if the value of the id column is empty, then the value is null.