Postgresql FDW技术的应用
postgres fdw是一种外部访问接口,它可以被用来访问存储在外部的数据,这些数据可以是外部的pg数据库,也可以oracle、mysql等数据库,甚至可以是文件。
测试环境
Ubuntu 16.04 LTS云主机2台，主机名为pg1（192.168.0.10）和pg2（192.168.0.11）。
安装postgresql
下面这个源是官网提供的postgressql-9.4的源，ubuntu16.04自带postgresql-9.5的源。
PostgreSQL Apt Repository
创建文件
sudo touch /etc/apt/sources.list.d/pgdg.list添加一行到文件中
sudo vi !$deb http://apt.postgresql.org/pub/repos/apt/ YOUR_UBUNTU_VERSION_HERE-pgdg main导入存储库签名秘钥，更新包列表
$ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | \
sudo apt-key add -
$ sudo apt-get update这是官网使用的源，但是在apt-get更新时由于网络问题报错，最后选择ubuntu16.04 apt仓库中默认的postgresql9.5。使用下面命令进行安装：
sudo apt-get install postgresql-9.52台机器安装步骤一样。
postgresql_fdw测试
主机pg2做远程服务器
在pg2上的postgresql上建库和表：
postgres=# alter user postgres with password '123456';
postgres=# create database foreign_test;
postgres=# \c foreign_test
foreign_test=# create schema schema1;
foreign_test=# \dn
List of schemas
Name | Owner
---------+----------
public | postgres
schema1 | postgres
(2 rows)
foreign_test=# create table schema1.t1(id int);
foreign_test=# insert into schema1.t1 values(1);
foreign_test=# select * from schema1.t1;
id
----
1
(1 row)在主机pg1上安装postgresql_fdw扩展
postgres=# CREATE EXTENSION postgres_fdw;
CREATE EXTENSION创建外部服务器，连接一个主机192.168.0.11（pg2）上并且监听5432端口的postgresql的服务器，在该远程服务器上要连接的数据库名为foreign_test：
postgres=# CREATE SERVER foreign_server
postgres-# FOREIGN DATA WRAPPER postgres_fdw
postgres-# OPTIONS (host '192.168.0.11', port '5432', dbname 'foreign_test');定义一个用户映射来标识远程服务器上使用哪个角色：
postgres=# CREATE USER MAPPING FOR postgres
SERVER foreign_server
OPTIONS (user 'postgres', password '123456');创建外部表：
postgres=# CREATE FOREIGN TABLE foreign_table (
id integer NOT NULL
)
SERVER foreign_server
OPTIONS (schema_name 'schema1', table_name 't1');FAQ：
postgres=# select * from foreign_table;
ERROR: could not connect to server "foreign_server"
DETAIL: could not connect to server: Connection refused
Is the server running on host "192.168.0.11" and accepting
TCP/IP connections on port 5432?
防火墙已关闭，但是报如上错误。
解决方法：
在192.168.0.11上修改postgresql的允许访问权限
$ vi /etc/postgresql/9.5/main/postgresql.conf添加：listen_addresses = '*'
password_encryption = on$ vi /etc/postgresql/9.5/main/pg_hba.conf添加：
host all all 0.0.0.0 0.0.0.0 md5重启服务：
$ /etc/init.d/postgresql restart然后到192.168.0.10上查询：
postgres=# select * from foreign_table;
id
----
1
(1 row)测试不同postgresql上数据源join操作
在pg2上进入foreign_test数据库：
postgres=# \c foreign_test展示当前schema：
foreign_test=# show search_path;切换schema：
foreign_test=# set search_path to schema1;列出当前schema下的表：
foreign_test=# \d在当前schema下新建表t2：
foreign_test=# create table t2(id int,name text);往表t2中插入数据：
foreign_test=# insert into t2 values(1, 'li'),(2, 'zhao'),(3, 'qian'),(4, 'wang'),(5, 'jia');在pg1上创建外部表：
postgres=# CREATE FOREIGN TABLE foreign_table1 (
id integer NOT NULL,
name text
)
SERVER foreign_server
OPTIONS (schema_name 'schema1', table_name 't2');查看外部表：
postgres=# select * from foreign_table1;
id | name
----+------
1 | li
2 | zhao
3 | qian
4 | wang
5 | jia
(5 rows)创建本地表：
postgres=# create table local_t1(id int,city text);
postgres=# insert into local_t1 values(1, 'beijing'),(2, 'shanghai'),(5, 'nanjing'),(6, 'lanzhou');
postgres=# select * from local_t1;
id | city
----+----------
1 | beijing
2 | shanghai
5 | nanjing
6 | lanzhou
(4 rows)测试远程postgresql和本地postgresql的join操作
postgres=# select * from local_t1 join foreign_table1 on local_t1.id = foreign_table1.id;
id | city | id | name
----+----------+----+------
1 | beijing | 1 | li
2 | shanghai | 2 | zhao
5 | nanjing | 5 | jia
(3 rows)
postgres=# select * from local_t1 left join foreign_table1 on local_t1.id = foreign_table1.id;
id | city | id | name
----+----------+----+------
1 | beijing | 1 | li
2 | shanghai | 2 | zhao
5 | nanjing | 5 | jia
6 | lanzhou | |
(4 rows)
postgres=# select * from local_t1 right join foreign_table1 on local_t1.id = foreign_table1.id;
id | city | id | name
----+----------+----+------
1 | beijing | 1 | li
2 | shanghai | 2 | zhao
| | 3 | qian
| | 4 | wang
5 | nanjing | 5 | jia
(5 rows)腾讯云开发者
