文档章节

PostgreSQL的postgres_fdw跨库使用

kenyon_君羊
 kenyon_君羊
发布于 2014/04/01 09:24
字数 1061
阅读 9415
收藏 9
PostgreSQL的数据库逻辑上是相互独立的,和Oracle类似,如果要访问其他数据库,需要做跨库操作,Postgres本身提供了一些扩展,比如dblink,pgsql_fdw等,高版本的建议使用postgres_fdw,也就是pgsql_fdw的升级版。

一、环境介绍
fdw是foreign-data wrapper的一个简称,可以叫外部封装数据,之前介绍过file_fdw,dblink http://my.oschina.net/Kenyon/blog/55294
http://my.oschina.net/Kenyon/blog/165432
而postgres_fdw实现的是各个postgresql数据库及远程数据库之间的跨库操作,功能和dblink一样。

本地 10.1.11.72 DB_port 5432
远程 10.1.11.71 DB_port 5432

71远端数据准备
postgres=# show search_path;
 search_path 
-------------
 schema_fdw
(1 row)
postgres=# create table tbl_kenyon (id int,remark text);
CREATE TABLE
postgres=# insert into tbl_kenyon select generate_series(1,100),'Kenyon Go!';
INSERT 0 100
二、安装使用
 安装分4步走
1.本地安装extension
安装的扩展名是来自于share/extension/*.control中的文件名*,比如postgres_fdw.control
postgres=# create extension postgres_fdw;
CREATE EXTENSION
postgres=# select * from pg_extension ;
 extname     | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition   -------------------+----------+--------------+----------------+------------+-----------+
 plpgsql            |       10 |           11 | f              | 1.0        |           | 
 pg_stat_statements |       10 |         2200 | t              | 1.1        |           | 
 postgres_fdw       |       10 |         2200 | t              | 1.0        |           | 
(3 rows)
postgres=# select * from pg_foreign_data_wrapper;
  fdwname    | fdwowner | fdwhandler | fdwvalidator | fdwacl | fdwoptions 
  ------------+----------+------------+--------------+--------+------------
 postgres_fdw |       10 |     154356 |       154357 |        | 
(1 row)

或
postgres=# \dx
                                     List of installed extensions
      Name        | Version |  Schema |                        Description                     -----------------+---------+------------+-----------------------------------------------------
 pg_stat_statements | 1.1     | public |track execution statistics of all SQL statements executed
 plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language
 postgres_fdw       | 1.0     | public     | foreign-data wrapper for remote PostgreSQL servers
(3 rows)
2.本地创建server并查看
该server作用是在本地配置一个连接远程的信息,下面的配置是要连接到远程DB名称是postgres数据库
postgres=# create server server_remote_71 foreign data wrapper postgres_fdw options(host '10.1.11.71',port '5432',dbname 'postgres');
CREATE SERVER
postgres=# select * from pg_foreign_server ;
     srvname      | srvowner | srvfdw | srvtype | srvversion | srvacl |   srvoptions  
--------------+----------+--------+---------+------------+--------+--------------------------
server_remote_71 |       10 | 154358 |         |     |{host=10.1.11.71,port=5432,dbname=postgres}
(1 row)
或者
postgres=# \des
              List of foreign servers
       Name       |  Owner   | Foreign-data wrapper 
------------------+----------+----------------------
 server_remote_71 | postgres | postgres_fdw
(1 row)
3.创建用户匹配信息并查看
--for后面的postgres是本地登录执行的用户名,option里存储的是远程的用户密码
postgres=# create user mapping for usr_pg_fdw server server_remote_71 options(user 'usr_pg_fdw',password '123456');
CREATE USER MAPPING
postgres=# select * from pg_user_mappings;
  umid  | srvid  |     srvname      | umuser | usename  |             umoptions             
--------+--------+------------------+--------+----------+-----------------------------------
 154360 | 154359 | server_remote_71 |     10 | postgres | {user=usr_pg_fdw,password=123456}
(1 row)
或
postgres=# \deu+
                          List of user mappings
      Server      | User name |               FDW Options                
------------------+-----------+------------------------------------------
 server_remote_71 | postgres  | ("user" 'usr_pg_fdw', password '123456')
(1 row)
4.本地创建外部表,指定server
postgres=# CREATE FOREIGN TABLE tbl_kenyon(id int,remark text) server server_remote_71 options (schema_name 'schema_fdw',table_name 'tbl_test');
CREATE FOREIGN TABLE

--如果不指定options,数据库会自动匹配相同的表名和表结构,如果有一项不匹配就会报错表或字段不对应的错误
--options可以指定对应的schema和表名等
5.配置pg_hba.conf
此处主要是在远端配置本地能访问的策略,略

6.本地访问远端,支持远程select和DML,和本地表操作一样
postgres=# select * from tbl_kenyon limit 10;
 id |   remark   
----+------------
  1 | Kenyon Go!
  2 | Kenyon Go!
  3 | Kenyon Go!
  4 | Kenyon Go!
  5 | Kenyon Go!
  6 | Kenyon Go!
  7 | Kenyon Go!
  8 | Kenyon Go!
  9 | Kenyon Go!
 10 | Kenyon Go!
(10 rows)

--本地更新远程数据
postgres=# delete from tbl_kenyon where id < 10;
DELETE 9
postgres=# select * from tbl_kenyon limit 10;
 id |   remark   
----+------------
 10 | Kenyon Go!
 11 | Kenyon Go!
 12 | Kenyon Go!
 13 | Kenyon Go!
 14 | Kenyon Go!
 15 | Kenyon Go!
 16 | Kenyon Go!
 17 | Kenyon Go!
 18 | Kenyon Go!
 19 | Kenyon Go!
(10 rows)
三、相关系统表
select * from pg_extension;
select * from pg_foreign_data_wrapper;
select * from pg_foreign_server;
select * from pg_foreign_table;
四、清理扩展
postgres=# drop foreign table tbl_kenyon;
DROP FOREIGN TABLE
postgres=# drop user mapping for postgres server server_remote_71 ;
DROP USER MAPPING
postgres=# drop server server_remote_71 ;
DROP SERVER
postgres=# drop extension postgres_fdw ;
DROP EXTENSION
五、相关问题
1.ERROR:  user mapping not found for "postgres"
检查一下user mapping用户信息,执行用户需要与user mapping的第一个用户相匹配

2.pg_fdw=> select * from tbl_kenyon limit 2;
ERROR:  could not connect to server "pg_remote_71"
DETAIL:  FATAL:  password authentication failed for user "usr_pg_fdw" 
检查一下options里面的用户密码与远程用户密码是否匹配
六、总结
1.postgres_fdw和dblink实现的功能是一样的,但是配置使用postgres_fdw更简单,而且也支持远程更新,稳定性和方便性考虑更推荐postgres_fdw,较像一个可以更新远程数据库的视图
2.在数据迁移或者ETL及定时刷新上面会比较有用处
3.外部表实际不占存储空间
4.物理表和外部表不能同名,因为pg_class的对象名称唯一键的缘故
5.远程改掉用户密码对当前本地连接无效,但本地再次连接取数会报错

© 著作权归作者所有

共有 人打赏支持
kenyon_君羊
粉丝 499
博文 170
码字总数 121714
作品 0
杭州
其他
私信 提问
加载中

评论(4)

kenyon_君羊
kenyon_君羊

引用来自“jackeven”的评论

博主你好,有个问题想请教一下:postgres_fdw连接远程服务器,在本地建立的foreign table 是一个视图吗?这个视图在本地数据是真实的吗?还是每次需要查询该表格时,访问远程数据获取?
不是真实的,本身不存数据,需要的时候远程获取
jackeven
jackeven
博主你好,有个问题想请教一下:postgres_fdw连接远程服务器,在本地建立的foreign table 是一个视图吗?这个视图在本地数据是真实的吗?还是每次需要查询该表格时,访问远程数据获取?
yang_real
yang_real
79
宏哥
宏哥
79
科普一种可以将PG变成通用SQL引擎的技术

作者介绍 伊翼,网名“小wing”,野生PG爱好者,从事数据库相关工作已近十年,目前供职于全球最大的通讯设备供应商。 原标题:《当FDW遇上GO》 FDW(Foreign Data Wrapper)是PostgreSQL(下...

伊翼
2018/06/14
0
0
PostgreSQL之Foreign Data Wrappers使用指南

PostgreSQL的fdw实现的功能是各个postgresql数据库及远程数据库之间的跨库操作,功能和oracle的dblink一样。 本文中的环境如下图所示: 1.1、目标端安装软件包 1.2、目标端创建用户 这里的源...

candon123
2018/11/28
0
0
本周推荐:MySQL、PostgreSQL没有更好,只看选择

点击上方蓝色字关注我们~ DB界谜题 MySQL好 or PostgreSQL好? 通过以下两场公开课 看老司机们分享的MySQL、PostgreSQL 或许没有谁更好的答案 只是关乎于选择 选择了,那就是千般好 MySQL公开...

老叶茶馆_
2018/10/26
0
0
PostgreSQL 自动创建分区实践 - 写入触发器

标签 PostgreSQL , 自动创建分区 , 触发器 , 写入 , 动态创建分区 背景 数据写入时,自动创建分区。 目前pg_pathman这个分区插件,有这个功能,如果你不是用的这个插件,可以考虑一下用触发器...

德哥
2018/06/21
0
0
PostgreSQL 9.6 更新版本发布说明

PostgreSQL是世界上最先进的开源数据库,9.6最新版本由PostgreSQL全球开发者今天发布。 此版本将允许用户纵向扩展(scale-up)和横向扩展(scale-out)来提高数据库的查询性能。 新功能包括并行查...

有理想的猪
2016/09/30
6.9K
31

没有更多内容

加载失败,请刷新页面

加载更多

智能合约编程/Dapp漏洞 -- 小心使用构造函数

构造函数是一个比较特殊的函数,在构造函数里会执行一些初始化合约是比较关键的功能。在Solidity 版本0.4.22之前,构造函数是一个和合约同名的函数。所以如果在开发过程中,合约名变了的话,...

怎当她临去时秋波那一转
31分钟前
2
0
JDK8发送邮件报错:Network is unreachable -- preferIPv4Stack

摘要: 使用javamail发送邮件时,老是提示Network is Network: ? 1 2 3 4 com.sun.mail.util.MailConnectException: Couldn't connect to host, port: smtp. com.sun.mail.util.MailConnec......

spinachgit
37分钟前
2
0
spring cloud feign 上传文件报关于not a type supported by this encoder解决方案

转载自:https://blog.csdn.net/qq_32786873/article/details/79756720

yan_liu
48分钟前
0
0
架构的“一小步”,业务的一大步

前言: 谈到“架构”这两个字,会有好多的名词闪现,比如:分层架构、事件驱动架构、DDD、CQRS等。亦或者一堆的软件设计原则,如:KISS原则(Keep it Simple and Stupid)、SOLID原则(单一责任...

阿里云官方博客
50分钟前
1
0
倒计时

DynamicConfig Utils CustomCountDownTimer CountdownView BaseCountdown BackgroundCountdown

lsy999
54分钟前
1
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部