PostgreSQL的postgres_fdw跨库使用

PostgreSQL的postgres_fdw跨库使用

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.远程改掉用户密码对当前本地连接无效,但本地再次连接取数会报错

转载于:https://my.oschina.net/Kenyon/blog/214953

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。

发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/109866.html原文链接:https://javaforall.cn

【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛

【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...

(0)


相关推荐

  • ostringstream使用

    ostringstream使用

    2021年12月17日
  • C语言函数pow(c语言pow函数头文件)

    函数介绍pow(x,y)——求x的y次方函数模拟实现普通版:递减n的大小,逐次乘num#include<stdio.h>intmy_pow(intnum,intn){ if(n!=0) returnnum*my_pow(num,n-1); else return1;}intmain(){ printf(“%d\n”,my_pow(2,3)); return0;}优化版:依据n的奇偶性来进行递归处理#incl

  • Tomcat部署war包项目请求404「建议收藏」

    Tomcat部署war包项目请求404「建议收藏」Tomcat部署war包请求404

  • 从零和使用mxnet实现softmax分类

    1.softmax从零实现(1797,64)(1797,)(1797,10)epoch:50,loss:[1.9941667],accuracy:0.3550361713967724

    2021年12月30日
  • 牛客国庆集训派对Day6 I.清明梦超能力者黄YY(树剖)「建议收藏」

    牛客国庆集训派对Day6 I.清明梦超能力者黄YY(树剖)「建议收藏」题目:https://www.nowcoder.com/acm/contest/206/I正难则反。问你倒数第k次的颜色,正着来搞不定,那就转换成“倒着来的第k次”。使用树剖将这棵树丢进线段树里,不维护染色,而是维护更新的次数(因为除了倒数第k次的颜色,其他的根本没用啊!!!),然后把区间最小值pushUp到树顶。更新完染色次数之后,用树顶来判整个区间里是否存在已经被更新了k次的节点,如果…

  • go语言微服务之RPC协议[通俗易懂]

    go语言微服务之RPC协议[通俗易懂]1什么是RPCRemoteProcedureCallProtocol–远程过程调用协议!IPC:进程间通信RPC:远程进程通信–应用层协议(http协议同层),底层用TCP实现。理解RPC:像调用本地函数一样,去调用远程函数。通过rpc协议,传递:函数名,函数参数。达到在本地,调用远端函数,得返回值到本地的目标。 为什么微服务要使用RPC:每个服务都被封转成进程,彼此“独立”。 进程与进程之间,可以使用不同的语言实现。2RPC使用的步骤服务端:注册rpc

发表回复

您的电子邮箱地址不会被公开。

关注全栈程序员社区公众号