PostgreSQL中的postgres_fdw扩展详解
项目招商找A5 快速获取精准代理名单
这篇文章主要介绍了PostgreSQL 中的postgres_fdw扩展详解，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧。
通过postgres_fdw 扩展，访问远程数据库表
一、环境准备
虚拟机(node107)：centos7、PostgreSQL10
远程服务器(百度云服务BBC)： centos7、PostgreSQL10
在本地虚拟机上访问远程服务器的数据表。
二、配置连接
（1）创建扩展: 在本地107这个节点上创建扩展。
[root@107 ~]# su postgresu: user postgre does not exist[root@107 ~]# su postgresbash-4.2$ psql mydb postgrescould not change directory to "/root": 权限不够psql (10.7)Type "help" for help.
mydb=# CREATE EXTENSION postgres_fdw;CREATE EXTENSION
如果是普通用户使用 ·postgres_fdw 需要单独授权
grant usage on foreign data wrapper postgres_fdw to 用户名
（2） 创建 foreign server 外部服务器，外部服务是指连接外部数据源的连接信息
mydb=# create server fs_postgres_bbc foreign data wrapper postgres_fdw options(host '182.61.136.109',port '5432',dbname 'technology');mydb=#
定义名称为 fs_postgres_bbc的外部服务，options 设置远程PostgreSQL数据源连接选项，通常设置主机名、端口、数据库名称。
（3）需要给外部服务创建映射用户
mydb=# create user mapping for postgres server fs_postgres_bbc options(user 'postgres',password 'password');CREATE USER MAPPINGmydb=#
for 后面接的是 node107 的数据库用户，options 里接的是远程PostgreSQL数据库的用户和密码。password 注意修改成自己的
其实想访问远程数据库，无非就是知道连接信息。包括host、port、dbname、user、password
（4）BBC上准备数据。
technology=# select * from public.customers where id < 5;id | name ----+-------1 | name12 | name23 | name34 | name4(4 rows)
technology=# -- schemaname = public
(5) 在node107上创建外部表：
mydb=# create foreign table ft_customers (id int4 primary key ,name varchar(200)) server fs_postgres_bbc options (schema_name 'public',table_name 'customers');
错误: 外部表上不支持主键约束
第1行create foreign table ft_customers (id int4 primary key , nam... ^mydb=#
错误: 外部表上不支持主键约束
第1行create foreign table ft_customers (id int4 primary key , nam... ^mydb=#
可以看见，外部表不支持主键约束。想想也是合理
mydb=# create foreign table ft_customers (id int4 , name varchar(200)) server fs_postgres_bbc options (schema_name 'public',table_name 'customers');CREATE FOREIGN TABLEmydb=#
options 选项中： 需要指定外部表的schema和表名
（6）在node107上去访问远程BBC的数据
mydb=# select * from ft_customers where id < 5;id | name ----+-------1 | name12 | name23 | name34 | name4(4 rows)
mydb=#
可以看见在mydb上能够访问远程数据库上 的数据了。
如果出现报错，如报pg_hba.conf 文件没有访问策略，在需要在对修改配置文件。
（7）本地数据库表与远程数据库表进行进行关联查询
create table orders (id int PRIMARY KEY,customerid int);
INSERT INTO orders(id,customerid) VALUES(1,1),(2,2);
SELECT * FROM orders;
-- 和外部表关联查询。mydb=# SELECT o.*,c.*mydb-# FROM orders omydb-# INNER JOIN ft_customers c ON o.customerid = c.idmydb-# WHERE c.id < 10000;id | customerid | id | name ----+------------+----+-------1 | 1 | 1 | name12 | 2 | 2 | name2(2 rows)
mydb=#
三、postgres_fdw 外部表支持写操作
postgres_fdw 外部表一开始只支持读，PostgreSQL9.3 版本开始支持可写。
写操作需要保证：1. 映射的用户对有写权限；2. 版本需要9.3 以上。
在node107结点上线删除数据，后再插入数据、最后更新。并查看远程BBC数据库表情况
mydb=# select count(*) from ft_customers;count ----------10000000(1 row)
mydb=# delete from ft_customers where id = 9999999;DELETE 1mydb=# select count(*) from ft_customers;count ---------9999999(1 row)
mydb=# insert into ft_customers values(9999999,'name1');INSERT 0 1mydb=# select count(*) from ft_customers;count ----------10000000(1 row)
mydb=# select * from ft_customers where id = 9999999;id | name ---------+-------9999999 | name1(1 row)
mydb=# update ft_customers set name = 'name999' where id = 9999999;UPDATE 1mydb=# select * from ft_customers where id = 9999999;id | name ---------+---------9999999 | name999(1 row)
mydb=#
四、postgres_fdw支持聚合函数下推
PostgreSQL10 增强了postgres_fdw 扩展模块的特性，可以将聚合、关联操作下推到远程PostgreSQL数据库进行，而之前的版本是将外部表相应的远程数据全部取到本地再做聚合，10版本这个心特性大幅度减少了从远程传输到本地库的数据量。提升了postgres_fdw外部表上聚合查询的性能。
mydb=# EXPLAIN(ANALYZE on,VERBOSE on) select id,count(*) from ft_customers where id < 100 group by id; QUERY PLAN ----------------------------------------------------------------------------------------------------Foreign Scan (cost=104.88..157.41 rows=199 width=12) (actual time=16.725..16.735 rows=99 loops=1)Output: id, (count(*))Relations: Aggregate on (public.ft_customers)Remote SQL: SELECT id, count(*) FROM public.customers WHERE ((id < 100)) GROUP BY 1Planning time: 0.247 msExecution time: 249.410 ms(6 rows)
mydb=#
remote sql: 远程库上执行的SQL，此SQL为聚合查询的SQL。聚合是在远程上执行的。
如果在PostgreSQL9.6 测试，则需要从远程传输到本地才可以。
小结
物理表和外部表不能同名，因为pg_class的对象名称唯一键的缘故
外部表不会存储数据。
文章来源：脚本之家
来源地址：https://www.jb51.net/article/204328.htm
尊敬的看官您对PostgreSQL中的postgres_fdw扩展详解有什么看法呢？互联网品牌制作专家愿与您共同探讨！版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容， 请加微信号oem365 举报，一经查实，本站将立刻删除。上一篇: PostgreSQL的外部数据封装器fdw用法 返 回 下一篇:Postgresql分布式插件plproxy的使用详解
