PostgreSQL 中的postgres_fdw扩展详解

通过postgres_fdw 扩展，访问远程数据库表

一、环境准备

虚拟机(node107)：centos7、PostgreSQL10

远程服务器(百度云服务BBC)： centos7、PostgreSQL10

在本地虚拟机上访问远程服务器的数据表。

二、配置连接

（1）创建扩展: 在本地107这个节点上创建扩展。

									[root@107 ~]# su postgre

									su: user postgre does not exist

									[root@107 ~]# su postgres

									bash-4.2$ psql mydb postgres

									could 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 MAPPING

									mydb=#

for 后面接的是 node107 的数据库用户，options 里接的是远程PostgreSQL数据库的用户和密码。password 注意修改成自己的

其实想访问远程数据库，无非就是知道连接信息。包括host、port、dbname、user、password

（4）BBC上准备数据。

									technology=# select * from public.customers where id < 5;

									 id | name

									----+-------

									 1 | name1

									 2 | name2

									 3 | name3

									 4 | 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=#

可以看见，外部表不支持主键约束。想想也是合理

									mydb=# create foreign table ft_customers (

									 id int4 , 

									 name varchar(200)

									) server fs_postgres_bbc options (schema_name 'public',table_name 'customers');

									CREATE FOREIGN TABLE

									mydb=#

options 选项中：需要指定外部表的schema和表名

（6）在node107上去访问远程BBC的数据

									mydb=# select * from ft_customers where id < 5;

									 id | name

									----+-------

									 1 | name1

									 2 | name2

									 3 | name3

									 4 | 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 o

									mydb-# INNER JOIN ft_customers c ON o.customerid = c.id

									mydb-# WHERE c.id < 10000;

									 id | customerid | id | name

									----+------------+----+-------

									 1 |   1 | 1 | name1

									 2 |   2 | 2 | name2

									(2 rows)

									mydb=#

PostgreSQL 中的postgres_fdw扩展详解

三、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 1

									mydb=# select count(*) from ft_customers;

									 count

									---------

									 9999999

									(1 row)

									mydb=# insert into ft_customers values(9999999,'name1');

									INSERT 0 1

									mydb=# 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 1

									mydb=# select * from ft_customers where id = 9999999;

									 id | name

									---------+---------

									 9999999 | name999

									(1 row)

									mydb=#

可以看见对ft_customers 进行增删改查。

四、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 1

									 Planning time: 0.247 ms

									 Execution time: 249.410 ms

									(6 rows)

									mydb=#