Shulou(Shulou.com)05/31 Report--

How to realize unlimited space storage of massive data in PostgreSQL? in view of this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

Use the s3_fdw plug-in to open the data flow between the cloud database PostgreSQL and OSS without complex program operation, and users only need a few lines of code to easily achieve it. Let's show how to read and write OSS external tables using s3_fdw in PostgreSQL:

# prepare data

Postgres=# CREATE TABLE local_table (id integer, name character varying, password character varying)

CREATE TABLE

Postgres=# insert into local_table select generate_series (1JI 40000000), md5 (random ():: text), md5 (random ():: text)

INSERT 0 40000000

# create plug-ins

Postgres=# create extension s3_fdw

CREATE EXTENSION

Postgres=# CREATE SERVER s3_fdw_server FOREIGN DATA WRAPPER s3_fdw options (host's 3murals. CNMUR NorthMur1.jdcloudMushoss.compositions, bucket 'postgresql')

CREATE SERVER

Postgres=# CREATE USER MAPPING FOR CURRENT_USER SERVER s3_fdw_server OPTIONS (access_key_id 'xxxxxx', secret_access_key' xxxxxx')

CREATE USER MAPPING

# create an external table

Postgres=# CREATE FOREIGN TABLE local_table_oss_1_10000000 (id integer, name character varying, password character varying) SERVER s3_fdw_server OPTIONS (dir's 3 million fdwls localizable tablespace 1, 000, format 'csv')

CREATE FOREIGN TABLE

Postgres=# CREATE FOREIGN TABLE local_table_oss_10000001_20000000 (id integer, name character varying, password character varying) SERVER s3_fdw_server OPTIONS (dir's 3-month fdwband localizable table csv' 10000001x20000000Universe, format 'csv')

CREATE FOREIGN TABLE

Postgres=# CREATE FOREIGN TABLE local_table_oss_20000001_30000000 (id integer, name character varying, password character varying) SERVER s3_fdw_server OPTIONS (dir's 3 million fdwls localizable tablepaces 20000001mm 3000000Universe, format 'csv')

CREATE FOREIGN TABLE

Postgres=# CREATE FOREIGN TABLE local_table_oss_30000001_40000000 (id integer, name character varying, password character varying) SERVER s3_fdw_server OPTIONS (dir's 3 million fdwls localizable tables, format 'csv')

CREATE FOREIGN TABLE

# Open 4 clients to import data from cloud PostgreSQL instances into OSS in parallel

Postgres=# insert into local_table_oss_1_10000000 select * from local_table where id = 10000001 and id = 20000001 and id = 30000001 and id

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.