PostgreSQL postgres_fdw使用

一白.

已于 2023-04-21 11:42:19 修改

阅读量3.3k

点赞数

分类专栏： PostgreSQL 扩展文章标签： PostgreSQL pgsql-fdw

于 2015-05-18 11:36:47 首次发布

本文链接：https://blog.csdn.net/luojinbai/article/details/45818197

版权

PostgreSQL 扩展专栏收录该内容

6 篇文章 0 订阅

订阅专栏

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. 问题

3.1 查看数据

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?

原因可能是：