FDW (Foreign Data Wrapper)

[TOC]

FDW: 外部表背景

提供外部数据源的透明访问机制。PostgreSQL fdw是一种外部访问接口，可以在PG数据库中创建外部表，用户访问的时候与访问本地表的方法一样，支持增删改查。而数据则是存储在外部，外部可以是一个远程的pg数据库或者其他数据库（mysql, oracle等）,又或者是文件等。

refer:

https://wiki.postgresql.org/wiki/Foreign_data_wrappers

https://www.postgresql.org/docs/12/fdwhandler.html

http://postgres.cn/docs/14/file-fdw.html
https://www.modb.pro/db/93487
https://www.cnblogs.com/huanying47/p/15994313.html (功能增强)

作用：

同构或异构的数据迁移, ETL,
数据分析（偶尔需要访问时, 分析，DBA)
快速提升开发效率

FDW支持的外部数据源

RDBMS FDW
- mysql_fdw(支持写)
- oracle_fdw
- sqlite_fdw
- postgres_fdw
- odbc_fdw
- jdbc_fdw
- firebird_fdw, tds_fdw, monetdb_fdw ...
NoSQL FDW
- couchdb_fdw
- redis_fdw
- mongo_fdw
- rethinkdb_fdw
- wdb_fdw
Format FDW
- json_fdw
- file_fdw
- multicdr_fdw
Web FDW
- www_fdw
- s3_fdw
Other FDW
- twitter_fdw docker_fdw hdfs_fdw RSS_fdw git_fdw ldap_fdw cstore_fdw

完整的列表：https://wiki.postgresql.org/wiki/Foreign_data_wrappers 　

手动编写FDW介绍：

http://postgres.cn/docs/14/fdwhandler.html

所有这些FDW，全都是通过create extension来使用的。

file_fdw 介绍

创建file_fdw 扩展

create extension file_fdw;

创建外部表(file_fdw)

CREATE SERVER filefdw_server FOREIGN DATA WRAPPER file_fdw;

CREATE FOREIGN TABLE emp_new (
    empno int, --  primary key, 此处不能加外键
    depname varchar(64), 
    gender char(1),
    age int,
    city varchar(32),
    manager int,
    salary int
) SERVER filefdw_server
OPTIONS (filename ‘/tmp/data.csv’,format ‘csv’);

像普通表一样访问外部表

SELECT empno, depname, salary FROM emp_new;

使用元命令 \des 和 \det 查看库中有哪些外部表和外部服务器

示例一：empsalary单表操作

db1=# select * from empsalary;
 empno |  depname  | gender | age |   city    | manager | salary
-------+-----------+--------+-----+-----------+---------+--------
     0 | global    | m      |  45 | Beijing   |       0 |  55080
     1 | develop   | m      |  38 | Beijing   |       0 |  27000
     2 | develop   | m      |  25 | Beijing   |       1 |  10000
     3 | develop   | f      |  26 | Beijing   |       1 |  11000
     4 | develop   | f      |  29 | Beijing   |       1 |  13000
     5 | develop   | m      |  30 | Shanghai  |       1 |  22000
     6 | personnel | m      |  30 | Beijing   |       0 |  22000
     7 | personnel | f      |  25 | Nanjing   |       6 |  11500
     8 | sales     | m      |  40 | Beijing   |       0 |  35000
     9 | sales     | f      |  31 | Hangzhou  |       8 |  20050
    10 | sales     | m      |  32 | Shenzhen  |       8 |  20300
    11 | develop   | f      |  35 | Guangzhou |       1 |  21300
    12 | sales     | m      |  30 | Beijing   |       8 |  20050
(13 rows)

db1=# \copy empsalary to '/pgccc/tmp/emp.csv' CSV header;
COPY 13
db1=#
db1=#
db1=# \! cat /pgccc/tmp/emp.csv
empno,depname,gender,age,city,manager,salary
0,global,m,45,Beijing,0,55080
1,develop,m,38,Beijing,0,27000
2,develop,m,25,Beijing,1,10000
3,develop,f,26,Beijing,1,11000
4,develop,f,29,Beijing,1,13000
5,develop,m,30,Shanghai,1,22000
6,personnel,m,30,Beijing,0,22000
7,personnel,f,25,Nanjing,6,11500
8,sales,m,40,Beijing,0,35000
9,sales,f,31,Hangzhou,8,20050
10,sales,m,32,Shenzhen,8,20300
11,develop,f,35,Guangzhou,1,21300
12,sales,m,30,Beijing,8,20050

create extension file_fdw;
-- 建server
CREATE SERVER filefdw_server FOREIGN DATA WRAPPER file_fdw;

-- 建外部表
CREATE FOREIGN TABLE emp_new (
    empno int, 
    depname varchar(64), 
    gender char(1),
    age int,
    city varchar(32),
    manager int,
    salary int
) SERVER filefdw_server 
OPTIONS (filename '/pgccc/tmp/emp.csv', format 'csv', header 'true');

-- ERROR:  primary key constraints are not supported on foreign tables
-- LINE 2:     empno int primary key,
select * from emp_new;

DROP foreign TABLE emp_new;

示例二: pg_log

(http://postgres.cn/docs/14/file-fdw.html 与 http://postgres.cn/docs/14/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-CSVLOG)

准备工作，将log_destination设置成csvlog (postgresql.conf)

log_destination = 'csvlog' 然后重启PG

\! cp /var/lib/pgsql/14/data/log/postgresql-Thu.csv /pgccc/tmp/pglog.csv

CREATE SERVER pglog_server FOREIGN DATA WRAPPER file_fdw;

CREATE FOREIGN TABLE pglog (
  log_time timestamp(3) with time zone,
  user_name text,
  database_name text,
  process_id integer,
  connection_from text,
  session_id text,
  session_line_num bigint,
  command_tag text,
  session_start_time timestamp with time zone,
  virtual_transaction_id text,
  transaction_id bigint,
  error_severity text,
  sql_state_code text,
  message text,
  detail text,
  hint text,
  internal_query text,
  internal_query_pos integer,
  context text,
  query text,
  bbb text,
  query_pos integer,
  location text,
  application_name text,
  backend_type text,
  ccc integer
) SERVER pglog_server
OPTIONS ( filename '/pgccc/tmp/pglog.csv', format 'csv' );

这里添加了两列bbb text, ccc integer，才能工作。

postgres_fdw 介绍

refer: http://postgres.cn/docs/14/postgres-fdw.html

postgres_fdw模块提供了外部数据包装器postgres_fdw，它可以被用来访问存储在外部PostgreSQL服务器中的数据。这个模块提供的功能大体上覆盖了较老的dblink模块的功能。但是postgres_fdw提供了更透明且更兼容标准的语法来访问远程表，并且可以在很多情况下给出更好的性能。

要使用postgres_fdw来为远程访问做准备：

使用CREATE EXTENSION来安装postgres_fdw扩展。 (super user)
使用CREATE SERVER创建一个外部服务器对象，它用来表示你想连接的每一个远程数据库。指定除了user和password之外的连接信息作为该服务器对象的选项。
使用CREATE USER MAPPING创建一个用户映射，每一个用户映射都代表你想允许一个数据库用户访问一个外部服务器。指定远程用户名和口令作为用户映射的user和password选项。
为每一个你想访问的远程表使用CREATE FOREIGN TABLE或者IMPORT FOREIGN SCHEMA创建一个外部表。外部表的列必须匹配被引用的远程表。但是，如果你在外部表对象的选项中指定了正确的远程名称，你可以使用不同于远程表的表名和/或列名。

现在你只需要从一个外部表SELECT来访问存储在它的底层的远程表中的数据。你也可以使用INSERT、UPDATE或DELETE修改远程表（当然，你在你的用户映射中已经指定的远程用户必须具有做这些事情的权限）。

第2、3、4步，如果是普通用户，则需要foreign data wrapper权限。

实例：

host1: sean-rh1, 5432, mydb, mydb/mydb, table : empsalary host2: sean-rh4, 5432, mydb, create fdw

先在host1上设置hba, 以供sean-rh4访问：

host    all             all             0.0.0.0/0                scram-sha-256

在host1上建库建表

create database mydb;
create user mydb with password 'mydb';
grant all privileges on database mydb to mydb;
\c mydb mydb;

CREATE TABLE empsalary (empno int primary key, 
    depname varchar(64), 
    gender char(1),
    age int,
    city varchar(32),
    manager int,
    salary int
);

INSERT INTO empsalary VALUES 
(0, 'global', 'm', 45, 'Beijing', 0, 55080), 
(1, 'develop', 'm', 38, 'Beijing', 0, 27000), 
(2, 'develop', 'm', 25, 'Beijing', 1, 10000), 
(3, 'develop', 'f', 26, 'Beijing', 1, 11000), 
(4, 'develop', 'f', 29, 'Beijing', 1, 13000), 
(5, 'develop', 'm', 30, 'Shanghai', 1, 22000), 
(6, 'personnel', 'm', 30, 'Beijing', 0, 22000), 
(7, 'personnel', 'f', 25, 'Nanjing', 6, 11500), 
(8, 'sales', 'm', 40, 'Beijing', 0, 35000), 
(9, 'sales', 'f', 31, 'Hangzhou', 8, 20050), 
(10, 'sales', 'm', 32, 'Shenzhen', 8, 20300),
(11, 'develop', 'f', 35, 'Guangzhou', 1, 21300),
(12, 'sales', 'm', 30, 'Beijing', 8, 20050);

切到host2建库建表

create database mydb;
create user mydb with password 'mydb';
grant all privileges on database mydb to mydb;
\c mydb postres;

在host2上建extension, 并授权

create extension postgres_fdw;
GRANT usage on foreign data wrapper postgres_fdw to mydb;

创建外部user-mapping及外部表

这一步需要切到普通用户先。

\c mydb mydb
CREATE SERVER foreign_server
        FOREIGN DATA WRAPPER postgres_fdw
        OPTIONS (host '10.180.3.255', port '5432', dbname 'mydb');
        
CREATE USER MAPPING FOR mydb
 SERVER foreign_server
 OPTIONS (user 'mydb', password 'mydb');

CREATE FOREIGN TABLE f_empsalary (
    empno int, 
    depname varchar(64), 
    gender char(1),
    age int,
    city varchar(32),
    manager int,
    salary int
)
SERVER foreign_server
OPTIONS (schema_name 'public', table_name 'empsalary');

select * from f_empsalary;

新版本的PG，已经支持聚合函数下推功能。意思是聚合操作都在目标server上执行完以后，再把结果回传到本机。这样能节省很多网络I/O, 不需要把数据回传到本机server上再做聚合。

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

20220804_01.md

20220804_01.md

FDW (Foreign Data Wrapper)

FDW: 外部表背景

作用：

FDW支持的外部数据源

file_fdw 介绍

示例一：empsalary单表操作

示例二: pg_log

postgres_fdw 介绍

Files

20220804_01.md

Latest commit

History

20220804_01.md

File metadata and controls

FDW (Foreign Data Wrapper)

FDW: 外部表 背景

作用：

FDW支持的外部数据源

file_fdw 介绍

示例一：empsalary单表操作

示例二: pg_log

postgres_fdw 介绍

FDW: 外部表背景