产品动态
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
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=>
sudo yum install libxml2-devel libcurl-devel -y
rpm -ivh cosfs-1.0.19-centos7.0.x86_64.rpm
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
[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
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 -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;'
CREATE EXTENSION pgcrypto;CREATE EXTENSION cos_fdw;
CREATE SERVER cos_server FOREIGN DATA WRAPPER cos_fdw OPTIONS(host 'xxxxxx.cos.ap-nanjing.myqcloud.com',bucket 'xxxxxxxx',id 'xxxxxxxx',key 'xxxxxxxxxx');
SELECT * FROM pg_foreign_server；看到。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');
, 分隔即可（不允许出现多余空格）。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');
-- 单⼀⽂件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;
参数 | 说明 |
host | 内网访问 COS 的地址，注意 host 不包含 http/https 前缀 |
bucket | 存储桶名称，存储桶的命名格式为 BucketName-APPID，此处填写的存储桶名称必须为此格式 |
id | 账号的 secret id |
key | 账号的 secret key |
参数 | 说明 |
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 |
• 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=
文档反馈