Postgresql_fdw

Postgresql_fdw

测试环境

Ubuntu 16.04 LTS云主机2台，主机名为pg1（192.168.0.34）和pg2（192.168.0.39）。

安装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.5

2台机器安装步骤一样。

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.39（pg2）上并且监听5432端口的postgresql的服务器，在该远程服务器上要连接的数据库名为foreign_test：

postgres=# CREATE SERVER foreign_server
postgres-#         FOREIGN DATA WRAPPER postgres_fdw
postgres-#         OPTIONS (host '192.168.0.39', 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.39" and accepting

         TCP/IP connections on port 5432?

防火墙已关闭，但是报如上错误。

解决方法：

在192.168.0.39上修改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.34上查询：

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)

参考网址：http://www.postgres.cn/docs/9.6/postgres-fdw.html

欢迎加入大数据与AI交流QQ群一起学习：572703882