postgres fdw

本文参考：http://postgres.cn/docs/11/postgres-fdw.html

介绍:

postgres_fdw 模块提供了外部数据包装器postgres_fdw，它可以被用来访问存储在外部PostgreSQL服务器中的数据。

解决问题：

不同数据库间的数据查询交互问题。

安装：

Postgres_fdw插件

Postgres 源码安装包中存在此插件。

可以找到

cd   ${postgres}/contrib/postgres_fdw

make && make install

安装完成后，在数据库查询

select * from pg_available_extensions;

发现多了一个name为 postgres_fdw的拓展，说明pg的postgres_fdw拓展已经安装成功。

mysql_fdw 插件

如果需要连接其他数据库的外部表，需要先安装对应的fdw插件。

下面以mysql为例：

下载地址可以去 https://github.com/EnterpriseDB/mysql_fdw 源码包

安装mysql_fdw 插件前，需要先安装mysql（https://downloads.mysql.com/archives/community/）的客户端。

wget https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.28-el7-x86_64.tar

解压后，将其中的lib文件夹软连接到postgres源码安装的lib中。

ln -s ${mysqlHome}/bin/*   /usr/local/bin

ln -s ${mysqlHome}/lib/*    /usr/local/lib/

ln -s ${mysqlHome}/lib/*    ${postgresHome}/lib/

mysql_fdw插件源码包：mysql_fdw-REL-2_7_0.tar.gz

解压 tar -xvf mysql_fdw-REL-2_7_0.tar.gz

编译：make USE_PGXS=1

安装：make USE_PGXS=1 install

使用：

新建插件：

在需要使用外部表的数据，首先创建插件。

create extension if not exists postgres_fdw;  -- postgres

create extension if not exists mysql_fdw;  -- mysql

外部表连接postgres

新建、编辑、删除一个外部服务器对象

（它用来表示你想连接的每一个远程数据库。指定除了user和password之外的连接信息作为该服务器对象的选项。）

CREATE SERVER forgin_server_pg_qgzhdc FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '127.0.0.1', port '5432',dbname 'qgzhdc');

修改

alter  server  forgin_server_pg_qgzhdc    options (set dbname 'qgzhdc');

删除

drop  server  forgin_server_pg_qgzhdc;

-- 查询已创建的外部服务器对象

select * from pg_foreign_server;

新建、编辑、删除用户映射

（每一个用户映射都代表你想允许一个数据库用户访问一个外部服务器。指定远程用户名和口令作为用户映射的user和password选项）

create user mapping for postgres server forgin_server_pg_qgzhdc options(user 'postgres',password 'testpwd');

修改用户映射:

ALTER USER MAPPING FOR postgres SERVER forgin_server_pg_qgzhdc OPTIONS ( set  user 'postgres',set  password 'testpwd');

删除用户映射:

DROP USER MAPPING FOR 'postgres' SERVER forgin_server_pg_qgzhdc;

查询已经创建的用户映射：

select * from pg_user_mapping;
select * from pg_user_mappings;

赋权限

GRANT USAGE ON FOREIGN SERVER cjfdw TO postgres;

新建、编辑、删除外部表

CREATE FOREIGN TABLE IF NOT EXISTS public.foreign_test (

    id character varying(255) DEFAULT uuid_generate_v1() NOT NULL,

    dwmc character varying(255)  NULL,

    address character varying(255)  NULL

    )SERVER forgin_server_pg_qgzhdc OPTIONS (schema_name 'public', table_name 'test');

-- 编辑外部表字段

    -- 编辑外部表跟原来编辑表的语句一样，只是增加了个  foreign 关键字。

         ALTER FOREIGN TABLE "public"."foreign_test" ADD COLUMN "code" varchar(255);

-- 修改外部表选项

         ALTER FOREIGN TABLE public.foreign_test OPTIONS ( SET table_name 'test2');

ALTER FOREIGN TABLE public.fdw_user OPTIONS (ADD opt1 'value1', SET opt2, 'value2', DROP opt3 'value3');

-- 删除外部表

       drop foreign table  foreign_test;

-- 测试查询已创建的外部表

select * from  pg_foreign_table;

外部表连接mysql

create user mapping for postgres server mysql_forgin_server_qgzhdc  options(username 'root',password '123456');

CREATE FOREIGN TABLE IF NOT EXISTS public.foreign_mysql_test (

    id int NOT NULL,

    dwmc character varying(255)  NULL,

    address character varying(255)  NULL

    )SERVER mysql_forgin_server_qgzhdc OPTIONS (dbname 'qgzhdc', table_name 'test');

                  

insert into foreign_mysql_test (dwmc,address) values('mysql-1','1');

                  

select * from  foreign_mysql_test;

效率影响：待测试