PostgreSQL postgres_fdw使用
1. 简介 2. 使用postgres_fdw
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与本地的用户连接起来。 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?
原因可能是:
参考: (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |