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
command to access the database and check whether the client is installed successfully: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
--force
parameter to the command to forcibly install it.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
is the format of the bucket name.SecretId
and SecretKey
are the key information.df -h
to view the mounting status.[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
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);
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');
host
is the access address of the COS bucket. The address doesn't need to contain the http
or https
prefix as the protocol.id
and key
of the foreign server are sensitive information, which will be encrypted and stored by cos_fdw. Different instances use different keys to maximize the user information protection. You can run SELECT * FROM pg_foreign_server;
to view the information.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
allows you to map multiple COS files to the same foreign table. To do so, enter multiple filenames in the filepath
parameter and separate them with commas (do not add spaces).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');
-- Single filepostgres=# 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)-- Multiple filespostgres=# 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 ...;
to import data from a foreign table to a local table.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-- Partitioned tables can be queriedpostgres=# 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)-- Currently, data cannot be written to foreign tablespostgres=# INSERT INTO pt VALUES (1, 'xyzzy'); -- ERRORERROR: 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 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
parametersParameter | Description |
host | Address for accessing COS over the private network. Note that host cannot contain the http or https prefix. |
bucket | Bucket name in the format of BucketName-APPID |
id | Account secret ID |
key | Account secret key |
CREATE FOREIGN TABLE
parametersParameter | Description |
filepath | Sample |
format | Data format, which currently can only be CSV. |
delimiter | Data delimiter |
quote | Data quote character |
escape | Data escape character |
encoding | Data encoding |
null | Specifies that the column matching the corresponding string is null . For example, null 'NULL' indicates to specify the string of the column value 'NULL' to null |
force_not_null | Specifies that the column's value should not match an empty string. For example, force_not_null 'id' indicates that if the value of the id column is empty, the value queried from the foreign table is an empty string but not null . |
force_null | Specifies that the column's value matches an empty string. For example, force_null 'id' indicates that if the value of the id column is empty, the value queried from the foreign table is null . |
x-cos-request-id
for assistance. If the field is empty, the request failed to be sent to 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=
Was this page helpful?