暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / PostgreSQL FDW(外部表) 简介

PostgreSQL FDW(外部表) 简介

数据库杂记 2023-02-25
276

PostgreSQL FDW(外部表)

  • 1、FDW: 外部表  背景

  • 2、基本作用

  • 3、FDW支持的外部数据源

    • 3.1、RDBMS FDW

    • 3.2 、NoSQL FDW

    • 3.3、Format FDW

    • 3.4、Web FDW

    • 3.5、Other FDW

  • 4、file_fdw 介绍

    • 4.1、示例一：empsalary单表操作

    • 4.2、示例二: pg_log

  • 5、postgres_fdw 介绍

  • 6、odbc_fdw实例

    • 6.1、Windows下配置实例

    • 6.2、Linux下的配置实例

1、FDW: 外部表  背景

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

2、基本作用

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

3、FDW支持的外部数据源

3.1、RDBMS FDW

mysql_fdw(支持写) (https://github.com/EnterpriseDB/mysql_fdw)

oracle_fdw (https://github.com/laurenz/oracle_fdw)

sqlite_fdw

postgres_fdw (见5.)

odbc_fdw(见6节)

jdbc_fdw -- https://github.com/pgspider/jdbc_fdw, https://www.postgresql.org/about/news/jdbc-fdw-010-release-2382/

firebird_fdw,

tds_fdw,

tds_fdw on centos:  
https://github.com/tds-fdw/tds_fdw/blob/master/InstallRHELandClones.md

1yum install freetds-devel.x86_64
2再make

monetdb_fdw

3.2 、NoSQL FDW

  • couchdb_fdw
  • redis_fdw
  • mongo_fdw
  • rethinkdb_fdw
  • wdb_fdw

3.3、Format FDW

  • json_fdw
  • file_fdw
  • multicdr_fdw

3.4、Web FDW

  • www_fdw
  • s3_fdw

3.5、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来使用的。

4、file_fdw 介绍

  1. 创建file_fdw 扩展
create extension file_fdw;

  1. 创建外部表(file_fdw)
 1CREATE SERVER filefdw_server FOREIGN DATA WRAPPER file_fdw;
 2
 3CREATE FOREIGN TABLE emp_new (
 4    empno int, --  primary key, 此处不能加外键
 5    depname varchar(64), 
 6    gender char(1),
 7    age int,
 8    city varchar(32),
 9    manager int,
10    salary int
11) SERVER filefdw_server
12OPTIONS (filename ‘/tmp/data.csv’,format ‘csv’);


  1. 像普通表一样访问外部表
SELECT empno, depname, salary FROM emp_new;

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

4.1、示例一：empsalary单表操作

 1CREATE TABLE empsalary (empno int primary key, 
 2    depname varchar(64), 
 3    gender char(1),
 4    age int,
 5    city varchar(32),
 6    manager int,
 7    salary int
 8);
 9
10INSERT INTO empsalary VALUES 
11(0, 'global', 'm', 45, 'Beijing', 0, 55080), 
12(1, 'develop', 'm', 38, 'Beijing', 0, 27000), 
13(2, 'develop', 'm', 25, 'Beijing', 1, 10000), 
14(3, 'develop', 'f', 26, 'Beijing', 1, 11000), 
15(4, 'develop', 'f', 29, 'Beijing', 1, 13000), 
16(5, 'develop', 'm', 30, 'Shanghai', 1, 22000), 
17(6, 'personnel', 'm', 30, 'Beijing', 0, 22000), 
18(7, 'personnel', 'f', 25, 'Nanjing', 6, 11500), 
19(8, 'sales', 'm', 40, 'Beijing', 0, 35000), 
20(9, 'sales', 'f', 31, 'Hangzhou', 8, 20050), 
21(10, 'sales', 'm', 32, 'Shenzhen', 8, 20300),
22(11, 'develop', 'f', 35, 'Guangzhou', 1, 21300),
23(12, 'sales', 'm', 30, 'Beijing', 8, 20050);
24
25db1=# select * from empsalary;
26 empno |  depname  | gender | age |   city    | manager | salary
27-------+-----------+--------+-----+-----------+---------+--------
28     0 | global    | m      |  45 | Beijing   |       0 |  55080
29     1 | develop   | m      |  38 | Beijing   |       0 |  27000
30     2 | develop   | m      |  25 | Beijing   |       1 |  10000
31     3 | develop   | f      |  26 | Beijing   |       1 |  11000
32     4 | develop   | f      |  29 | Beijing   |       1 |  13000
33     5 | develop   | m      |  30 | Shanghai  |       1 |  22000
34     6 | personnel | m      |  30 | Beijing   |       0 |  22000
35     7 | personnel | f      |  25 | Nanjing   |       6 |  11500
36     8 | sales     | m      |  40 | Beijing   |       0 |  35000
37     9 | sales     | f      |  31 | Hangzhou  |       8 |  20050
38    10 | sales     | m      |  32 | Shenzhen  |       8 |  20300
39    11 | develop   | f      |  35 | Guangzhou |       1 |  21300
40    12 | sales     | m      |  30 | Beijing   |       8 |  20050
41(13 rows)
42
43db1=# \copy empsalary to '/pgccc/tmp/emp.csv' WITH (FORMAT csv, HEADER false);
44或者
45db1=# \copy empsalary to '/pgccc/tmp/emp.csv' CSV header;
46COPY 13
47db1=#
48db1=#
49db1=# \! cat /pgccc/tmp/emp.csv
50empno,depname,gender,age,city,manager,salary
510,global,m,45,Beijing,0,55080
521,develop,m,38,Beijing,0,27000
532,develop,m,25,Beijing,1,10000
543,develop,f,26,Beijing,1,11000
554,develop,f,29,Beijing,1,13000
565,develop,m,30,Shanghai,1,22000
576,personnel,m,30,Beijing,0,22000
587,personnel,f,25,Nanjing,6,11500
598,sales,m,40,Beijing,0,35000
609,sales,f,31,Hangzhou,8,20050
6110,sales,m,32,Shenzhen,8,20300
6211,develop,f,35,Guangzhou,1,21300
6312,sales,m,30,Beijing,8,20050
64
65create extension file_fdw;
66-- 建server
67CREATE SERVER filefdw_server FOREIGN DATA WRAPPER file_fdw;
68
69-- 建外部表
70CREATE FOREIGN TABLE emp_new (
71    empno int, 
72    depname varchar(64), 
73    gender char(1),
74    age int,
75    city varchar(32),
76    manager int,
77    salary int
78) SERVER filefdw_server 
79OPTIONS (filename '/pgccc/tmp/emp.csv', format 'csv', header 'true');
80
81-- ERROR:  primary key constraints are not supported on foreign tables
82-- LINE 2:     empno int primary key,
83select * from emp_new;
84
85DROP foreign TABLE emp_new;


4.2、示例二: pg_log

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

https://www.postgresql.org/docs/14/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-CSVLOG

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

log_destination = 'csvlog' 然后重启PG

 1\! cp /var/lib/pgsql/14/data/log/postgresql-Thu.csv /pgccc/tmp/pglog.csv
 2
 3CREATE SERVER pglog_server FOREIGN DATA WRAPPER file_fdw;
 4
 5CREATE FOREIGN TABLE pglog (
 6  log_time timestamp(3) with time zone,
 7  user_name text,
 8  database_name text,
 9  process_id integer,
10  connection_from text,
11  session_id text,
12  session_line_num bigint,
13  command_tag text,
14  session_start_time timestamp with time zone,
15  virtual_transaction_id text,
16  transaction_id bigint,
17  error_severity text,
18  sql_state_code text,
19  message text,
20  detail text,
21  hint text,
22  internal_query text,
23  internal_query_pos integer,
24  context text,
25  query text,
26  query_pos integer,
27  location text,
28  application_name text,
29  backend_type text,
30  leader_pid integer,
31  query_id bigint
32) SERVER pglog_server
33OPTIONS ( filename '/pgccc/tmp/pglog.csv', format 'csv' );


5、postgres_fdw 介绍

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

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

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

  1. 使用CREATE EXTENSION来安装postgres_fdw
    扩展。   (super user)
  2. 使用CREATE SERVER创建一个外部服务器对象，它用来表示你想连接的每一个远程数据库。指定除了user
    和password
    之外的连接信息作为该服务器对象的选项。
  3. 使用CREATE USER MAPPING创建一个用户映射，每一个用户映射都代表你想允许一个数据库用户访问一个外部服务器。指定远程用户名和口令作为用户映射的user
    和password
    选项。
  4. 为每一个你想访问的远程表使用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

  1. 先在host1上设置hba, 以供sean-rh4访问：
host    all             all             0.0.0.0/0                scram-sha-256

  1. 在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);

  1. 切到host2建库建表
create database mydb;
create user mydb with password 'mydb';
grant all privileges on database mydb to mydb;
\c mydb postres;


  1. 在host2上建extension, 并授权
create extension postgres_fdw;
GRANT usage on foreign data wrapper postgres_fdw to mydb;


  1. 创建外部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上再做聚合。

6、odbc_fdw实例

windows版本for PG odbc_fdw下载：https://www.postgresonline.com/journal/index.php?/archives/402-PostgreSQL-14-64-bit-for-Windows-FDWs.html

相关配置步骤介绍

6.1、Windows下配置实例

Windows下示例：
1. downlaod and install 64 bit https://www.postgresonline.com/journal/index.php?/archives/402-PostgreSQL-14-64-bit-for-Windows-FDWs.html

2. configure DSN test for SQLSever  (downloa server odbc driver 64 bit if required)

create extension odbc_fdw;
CREATE SERVER odbc_server
  FOREIGN DATA WRAPPER odbc_fdw
  OPTIONS (dsn 'test');

CREATE FOREIGN TABLE
  t123_fdw (
    id integer
  )
  SERVER odbc_server
  OPTIONS (
    odbc_DATABASE 'smp3',
    schema 'dbo', table 't123'
  );

CREATE USER MAPPING FOR postgres
  SERVER odbc_server
  OPTIONS (odbc_UID 'smp', odbc_PWD 'abcdef');


6.2、Linux下的配置实例

以连接SQLServer为例 。需要下载SQLServer的linux odbc驱动。

/usr/pgsql-14 已经安装了postgres-14

https://github.com/CartoDB/odbc_fdw
required: 
odbc-postgresql: >= 9.x
libmyodbc: >= 5.1
FreeTDS: >= 1.0
yum install unixODBC-devel.x86_64 freetds-devel.x86_64

1. git clone https://github.com/CartoDB/odbc_fdw/
至postgres/contrib 目录下

2. postgres source:
   ./configure --with-openssl --prefix=/usr/pgsql-14
3. yum install unixODBC.x86_64 unixODBC-devel.x86_64 freetds-devel.x86_64 freetds.x86_64

   使用isql --version 来验证一下
    odbcinst -j
    unixODBC 2.3.7
    DRIVERS............: /etc/odbcinst.ini
    SYSTEM DATA SOURCES: /etc/odbc.ini
    FILE DATA SOURCES..: /etc/ODBCDataSources
    USER DATA SOURCES..: /root/.odbc.ini
    SQLULEN Size.......: 8
    SQLLEN Size........: 8
    SQLSETPOSIROW Size.: 8

4. postgres/contrib/odbc_fdw/
   make; make install

然后:
vim  /etc/odbc.ini

[ODBC Data Sources]
test= My second Test DSN
test2 = My Test DSN

[test]
Driver = /opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.1.so.1.1
Description = My Second Test DSN
Trace = YES
Server = 10.47.41.234
Database = smp3
Port = 1433
#TDS_Version = 8.0
TDS_Version = 7.3

[test2]
Driver = /usr/lib64/libtdsodbc.so
Description = My Test DSN
Trace = 1
TraceFile = /tmp/odbc.trace
#Servername = DEMO
Server = 10.47.41.234
Database = <dbname>
Port = 1433
TDS_Version = 7.3

isql -v test2 <user> <password>
// test2为数据源的名称

回到：postgresql里
create extension odbc_fdw;
CREATE SERVER odbc_server
  FOREIGN DATA WRAPPER odbc_fdw
  OPTIONS (dsn 'test2');

CREATE FOREIGN TABLE
  t123_fdw (
    id integer
  )
  SERVER odbc_server
  OPTIONS (
    odbc_DATABASE 'smp3',
    schema 'dbo', table 't123'
  );

CREATE USER MAPPING FOR postgres
  SERVER odbc_server
  OPTIONS (odbc_UID 'smp', odbc_PWD 'Secret12');


常见错误：

测试SQLServer ODBC驱动的时候：
[21:52:59-root@sean-rh1.openstack.eu-nl-1.cloud.sap:/etc]$ isql -v test smp Secret12
[08001][unixODBC][Microsoft][ODBC Driver 18 for SQL Server]SSL Provider: [error:14090086:SSL routines:ssl3_get_server_certificate:certificate verify failed:self signed certificate]
[08001][unixODBC][Microsoft][ODBC Driver 18 for SQL Server]Client unable to establish connection
[ISQL]ERROR: Could not SQLConnect

A: 在odbc.ini上加一行配置：即可
TrustServerCertificate=Yes

一种更常见的命令行方式如下：(直接指定全部参数)
isql -v -k "DRIVER={ODBC Driver 18 for SQL Server};SERVER=10.47.41.234,1433;UID=smp;PWD=Secret12;Authentication=SqlPassword;TrustServerCertificate=Yes"


参考：

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  (功能 增强)


postgresql外部表
文章转载自数据库杂记，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论