注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

PostgreSQL 中文网

 
 
 

日志

 
 

PostgreSQL: Using pgsql_fdw connect remote PostgteSQL DB  

2012-06-07 11:24:20|  分类: Postgres基础 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

  
  
      今天在从 德哥 blog  中发现 PostgreSQL9.1 外部表支持另一个模块,即 pgsql_fdw ,使用这个模块
可以方便地在一个 PG 库中访问远程 PG 库中的表,当然是以创建外部表的形式, 这种方法可以近似地
理解成 dblink ,关于PostgreSQL 中的 dblink 可以参考以下文章
http://francs3.blog.163.com/blog/static/4057672720108401139868/
http://francs3.blog.163.com/blog/static/40576727201152114831694/

   接下来演示下 pgsql_fdw 的实验过程。

 

一 安装
--1.1 Requirement
  PostgreSQL 9.1 or later

--1.2 下载
https://build.opensuse.org/package/files?package=pgsql_fdw&project=home%3Adeadpoint

--1.3 解压

 [postgres@pgb extension]$ tar zxvf pgsql_fdw-1.0.tar.gz
   

--1.4 编译

 [postgres@pgb pgsql_fdw-1.0]$ make USE_PGXS=1
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wformat-security -fno-strict-aliasing -fwrapv -fpic -I/opt/pgsql9.1/include -I. -I. -I/opt/pgsql9.1/include/server -I/opt/pgsql9.1/include/internal -D_GNU_SOURCE -I/usr/include/libxml2   -c -o pgsql_fdw.o pgsql_fdw.c
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wformat-security -fno-strict-aliasing -fwrapv -fpic -I/opt/pgsql9.1/include -I. -I. -I/opt/pgsql9.1/include/server -I/opt/pgsql9.1/include/internal -D_GNU_SOURCE -I/usr/include/libxml2   -c -o option.o option.c
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wformat-security -fno-strict-aliasing -fwrapv -fpic -I/opt/pgsql9.1/include -I. -I. -I/opt/pgsql9.1/include/server -I/opt/pgsql9.1/include/internal -D_GNU_SOURCE -I/usr/include/libxml2   -c -o deparse.o deparse.c
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wformat-security -fno-strict-aliasing -fwrapv -fpic -I/opt/pgsql9.1/include -I. -I. -I/opt/pgsql9.1/include/server -I/opt/pgsql9.1/include/internal -D_GNU_SOURCE -I/usr/include/libxml2   -c -o connection.o connection.c
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wformat-security -fno-strict-aliasing -fwrapv -fpic -I/opt/pgsql9.1/include -I. -I. -I/opt/pgsql9.1/include/server -I/opt/pgsql9.1/include/internal -D_GNU_SOURCE -I/usr/include/libxml2   -c -o ruleutils.o ruleutils.c
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wformat-security -fno-strict-aliasing -fwrapv -fpic -shared -o pgsql_fdw.so pgsql_fdw.o option.o deparse.o connection.o ruleutils.o -L/opt/pgsql9.1/lib -L/usr/lib  -Wl,-rpath,'/opt/pgsql9.1/lib',--enable-new-dtags  -L/opt/pgsql9.1/lib -lpq
   


--1.5 安装

 [postgres@pgb pgsql_fdw-1.0]$ make USE_PGXS=1 install
/bin/mkdir -p '/opt/pgsql9.1/lib'
/bin/mkdir -p '/opt/pgsql9.1/share/extension'
/bin/sh /opt/pgsql9.1/lib/pgxs/src/makefiles/../../config/install-sh -c -m 755  pgsql_fdw.so '/opt/pgsql9.1/lib/pgsql_fdw.so'
/bin/sh /opt/pgsql9.1/lib/pgxs/src/makefiles/../../config/install-sh -c -m 644 ./pgsql_fdw.control '/opt/pgsql9.1/share/extension/'
/bin/sh /opt/pgsql9.1/lib/pgxs/src/makefiles/../../config/install-sh -c -m 644 ./pgsql_fdw--1.0.sql  '/opt/pgsql9.1/share/extension/'
   


二 pgsql_fdw 演示

          这里准备访问同一台 PostgreSQL Server 中的另外一个库的表,

 源库:    127.0.0.1/1921   mydb

目标库  127.0.0.1/1921  skytf


--2.1 创建 extension

 [postgres@pgb pgsql_fdw-1.0]$ psql skytf postgres
psql (9.1.0)
Type "help" for help.

skytf=# create extension pgsql_fdw;
CREATE EXTENSION

skytf=# \dx
                             List of installed extensions
   Name    | Version |   Schema   |                    Description                    
-----------+---------+------------+----------------------------------------------------
 file_fdw  | 1.0     | public     | foreign-data wrapper for flat file access
 mysql_fdw | 1.0     | public     | Foreign data wrapper for querying a MySQL server
 pgsql_fdw | 1.0     | public     | foreign-data wrapper for remote PostgreSQL servers
 plpgsql   | 1.0     | pg_catalog | PL/pgSQL procedural language
(4 rows)

   

    备注: pgsql_fdw 已创建。

--2.2 创建 server

 skytf=# grant usage on foreign data wrapper pgsql_fdw to skytf;
GRANT

skytf=# \c skytf skytf
You are now connected to database "skytf" as user "skytf".

CREATE SERVER pgsql_srv FOREIGN DATA WRAPPER pgsql_fdw
OPTIONS (host '127.0.0.1', port '1923', dbname 'mydb');

skytf=> \des
           List of foreign servers
     Name     | Owner | Foreign-data wrapper
--------------+-------+----------------------
 file_srv     | skytf | file_fdw
 mysql_svr_25 | skytf | mysql_fdw
 pgsql_srv    | skytf | pgsql_fdw
(3 rows)

   

 备注: pgsql_srv 已创建。


--2.3 create mapping user

 CREATE USER MAPPING FOR public SERVER pgsql_srv
OPTIONS (user 'mydb', password 'mydb');
CREATE USER MAPPING
   


--2.4 create foreign table

 skytf=> CREATE FOREIGN TABLE ft_test (
skytf(>  id    integer  ,
skytf(>  name  character varying(32) )
skytf-> SERVER pgsql_srv
skytf-> OPTIONS (nspname 'mydb', relname 'test');
CREATE FOREIGN TABLE
   


--2.5 查询测试

 skytf=> select * from ft_test limit 1;
  id  | name
------+------
 5024 | AAAA
(1 row)
   


--2.6 执行计划

 skytf=> explain analyze select * from ft_test where id=1;
                                                 QUERY PLAN                                                
------------------------------------------------------------------------------------------------------------
 Foreign Scan on ft_test  (cost=100.00..111.46 rows=2 width=8) (actual time=2.190..2.190 rows=0 loops=1)
   Filter: (id = 1)
   Remote SQL: DECLARE pgsql_fdw_cursor_10 SCROLL CURSOR FOR SELECT id, name FROM mydb.test WHERE (id = 1)
 Total runtime: 3.092 ms
(4 rows)
   

   备注:根据where 条件查询, pgsql_fdw 会将查询语句发送到目标远程 PG 库,并且根据文档描述,pgsql_fdw
             优化了以下:  1 减少了远程数据传输;  2 将 where 条件子表发送到目标服务器。

skytf=>  explain analyze select count(*) from ft_test ;
                                                            QUERY PLAN                                                           
----------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=33446.70..33446.71 rows=1 width=0) (actual time=27718.201..27718.206 rows=1 loops=1)
   ->  Foreign Scan on ft_test  (cost=100.00..28553.56 rows=1957256 width=0) (actual time=40.259..15705.231 rows=1990000 loops=1)
         Remote SQL: DECLARE pgsql_fdw_cursor_8 SCROLL CURSOR FOR SELECT NULL, NULL FROM mydb.test
 Total runtime: 27718.583 ms
(4 rows)
   

 


--2.7 Connection management

 skytf=> select * From pgsql_fdw_connections;
 srvid |  srvname  | usesysid | usename
-------+-----------+----------+---------
 74984 | pgsql_srv |    41818 | skytf
(1 row)
   


--2.8 查看系统进程

 [postgres@pgb pg_root]$ ps -ef | grep mydb
postgres 18454 31854  0 10:37 ?        00:00:00 postgres: mydb mydb 127.0.0.1(25949) idle in transaction     
postgres 18514 31854  1 10:55 ?        00:00:00 postgres: mydb mydb 127.0.0.1(46007) idle in transaction
   
    备注:在一个 session 中查看外部表是以开启事务方式进行,直到退出当前 session 或者切换用户,
               事务才结束。
   
   

三 常见 ERROR
--3.1 查询外部表时报错

 [postgres@pgb ~]$ psql skytf skytf
psql (9.1.0)
Type "help" for help.

skytf=> select * from ft_test limit 1;
ERROR:  password is required
DETAIL:  Non-superuser cannot connect if the server does not request a password.
HINT:  Target server is authentication method must be changed.

   

   备注:上面报错是因为 pg_hba.conf  文件认证问题,本机配置的是 local 连接所有库都是 trust 方式,即不需要

             密码,而 pgsql_fdw 连接时需要提供用户名和密码。


--解决方法
修改 pg_hba.conf,增加红色字体行,并 reload

--pg_hba.conf 文件 

# IPv4 local connections:
host    mydb            mydb             127.0.0.1/32           md5
host    all             all             127.0.0.1/32            trust

--reload 配置文件

[postgres@pgb pg_root]$ pg_ctl reload -D $PGDATA
server signaled

   

 

四 pgsql_fdw 外部表限制

1  外部表只读,其它修改操作不允许,例如 update,delete,INSERT 等,并且外部表也不会被 vacuum。

2  当开启 session 查询外部表时, pgsql_fdw 以事务方式开启,即便当前 session 空闲,事务也没有提交,

   直到退出当前 session 或者切换到其它用户后,事务。


五 参考
http://interdbconnect.sourceforge.net/pgsql_fdw/pgsql_fdw-en.html
http://blog.163.com/digoal@126/blog/static/163877040201231514057303/

  评论这张
 
阅读(30149)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2016