1 简介

• postgres_fdw扩展可以
  • 以外部表的方式在本地访问远程PG库里面的表。

有什么意义吗？？，会不会把能下推的操作放到远程服务器上去做呢？

2 使用postgres_fdw

• 测试环境是两虚拟机。
• 源库： 192.168.17.11 postgres
• 目标库： 192.168.17.35 postgres

2.1 添加扩展

postgres=# create extension postgres_fdw ;
CREATE EXTENSION

• \dx看当前库添加的所有扩展

postgres=# \dx
                               List of installed extensions
     Name     | Version |   Schema   |                    Description                     
--------------+---------+------------+----------------------------------------------------
 file_fdw     | 1.0     | public     | foreign-data wrapper for flat file access
 hstore       | 1.2     | public     | data type for storing sets of (key, value) pairs
 plpgsql      | 1.0     | pg_catalog | PL/pgSQL procedural language
 postgres_fdw | 1.0     | public     | foreign-data wrapper for remote PostgreSQL servers
(4 rows)

2.2 创建server

postgres=# create server server_pgsql_fdw foreign data wrapper postgres_fdw options(host '192.168.17.35',port '5432',dbname 'postgres');
CREATE SERVER

• 创建server 需要超级用户。

2.3 create user mapping

postgres=# create user mapping for public server server_pgsql_fdw options(user 'postgres',password '123456');                          
CREATE USER MAPPING

• create user mapping将server与本地的用户连接起来。
  public表示当前库的没有明确指定user mapping 的所有用户。

2.4 创建外部表

postgres=# create foreign table foreign_goods(id integer,name character varying)server server_pgsql_fdw options(schema_name 'public',table_name 'goods');
CREATE FOREIGN TABLE

2.5 查看数据

postgres=# select * from foreign_goods;
 id | name 
----+------
  1 | aa
  2 | bb
  3 | cc
(3 rows)

postgres=# select * from foreign_goods where id=1;
 id | name 
----+------
  1 | aa
(1 row)

2.6 执行计划

postgres=# explain (analyze,verbose) select * from foreign_goods  where id=1;
                                                      QUERY PLAN                                                       
-----------------------------------------------------------------------------------------------------------------------
 Foreign Scan on public.foreign_goods  (cost=100.00..127.20 rows=7 width=36) (actual time=1.256..1.257 rows=1 loops=1)
   Output: id, name
   Remote SQL: SELECT id, name FROM public.goods WHERE ((id = 1))
 Total runtime: 2.614 ms
(4 rows)

3. 问题

postgres=# select * from foreign_goods ;
ERROR:  could not connect to server "server_pgsql_fdw"
DETAIL:  could not connect to server: No route to host
    Is the server running on host "192.168.17.35" and accepting
    TCP/IP connections on port 5432?

• 可能是：
• 目标库的数据库是否启动
• ip port是否配置错误。
• 目标库的listen_addresses是否是 listen_addresses=’*’

参考链接

• https://blog.csdn.net/luojinbai/article/details/45818197?utm_source=distribute.pc_relevant.none-task

第二篇

• Pg数据库逻辑上是相互独立，和Oracle类似，
  • 要访问其他数据库，需做跨库操作，
• Pg本身提供一些扩展，如dblink,pgsql_fdw
• 高版本建议用postgres_fdw，即pgsql_fdw的升级版

 

• fdw叫外部封装数据
• postgres_fdw实现的是各个postgresql数据库及远程数据库之间的跨库操作
• 这个工具是自带的扩展工具，可以直接到postgres的解压目录下。
• 我的目录是 /tmp/postgresql-9.4.4/contrib。
• cd /tmp/postgresql-9.4.4/contrib
  make
  make install
• 运行完上面后，可直接进入数据库，
• 然后select * from pg_available_extensions;
• 看是否安装成功。如果看到postgres_fdw，说明安装成功。
• 然后创建该扩展工具：
• create extension postgres_fdw;
• 就可以正常使用了。

 

• 查看已经安装并创建的扩展工具：
  test=# \dx
• select * from pg_foreign_data_wrapper; （也可）

postgres_fdw使用简单，分四部

• 创建远程服务器，需定义主机地址，数据库名和端口。
• 指定连接远程数据库的用户，和创建的远程服务器一起封装。
• 第三步创建远程表，这里也需要把远程表的字段和类型都列一下。
• 第四步和操作本地表一样操作远程表。

测试：

• 环境： red hat 6.5
• 数据库：postgres 9.4.4
• IP： 192.168.10.8
• 本次测试用一台服务器，两库test和postgres
• test中创外部表，查看postgres中的表tb1
• postgres中：
• 我站在test里面，想访问Postgres里面的那个表哦！！！！

postgres=# select * from tb1 ;
 a 
---
 1
 2
 3
 7
(4 rows)

• test中：
• 创建远程服务器：

test=# create server server_remote_70 foreign data wrapper postgres_fdw options(host '192.168.10.8',port '5432',dbname 'postgres');
CREATE SERVER
test=# select * from pg_foreign_server ;  
     srvname      | srvowner | srvfdw | srvtype | srvversion | srvacl |                  srvoptions                   
------------------+----------+--------+---------+------------+--------+-----------------------------------------------
 server_remote_70 |       10 |  33291 |         |            |        | {host=192.168.10.8,port=5432,dbname=postgres}
(1 row)

• 指定连接远程数据库的用户和密码：

test=# create user mapping for postgres server server_remote_70 options(user 'postgres',password 'postgres');
CREATE USER MAPPING
test=# select * from pg_user_mappings;  
 umid  | srvid |     srvname      | umuser | usename  |             umoptions             
-------+-------+------------------+--------+----------+-----------------------------------
 33311 | 33310 | server_remote_70 |     10 | postgres | {user=postgres,password=postgres}
(1 row)

• 创建远程表并查看数据：

test=# CREATE FOREIGN TABLE tbl_fdw(a int) server server_remote_70 options (schema_name 'public',table_name 'tb1');  
CREATE FOREIGN TABLE
test=# select * from tbl_fdw ;
 a 
---
 1
 2
 3
 7
(4 rows)

• 操作远程表：

test=# insert into tbl_fdw values (9);
INSERT 0 1

• postgres查tb1

postgres=# select * from tb1 ;
 a 
---
 1
 2
 3
 7
 9
(5 rows)

注意

• 本次用的是同一台服务器上的两套库之间查询。
• 假如用两台服务器的话，那么需要两台服务器之间的数据库能够互相远程才可以，也就是说需要修改postgres.conf中的listen_addresses = ‘*’ 以及pg_hba.conf中在IPV4下面增加一列：
  host all all 0.0.0.0/0 trust

我的这次测试其实可能不需要修改默认的listen_addresses = ‘locahost’,
因为是使用本机连接。
但是我的这次测试还是失败了，原因是我的这台服务器在建库以后修改过IP地址。
所以locahost可能代表的是原来的IP地址（192.168.1.8），而不是修改以后的。
为什么会做出这样的推测，是因为我用修改后的IP 直接进入数据库会报错：
（修改以前的IP地址是192.168.1.8）
[postgres@pg2 data]$ psql -h 192.168.10.8 -U postgres -d postgres
psql: could not connect to server: Connection refused
Is the server running on host “192.168.10.8” and accepting
TCP/IP connections on port 5432?
那么我之前是怎么进入数据库的呢？
直接使用psql 或者psql dbname。