一、环境介绍
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 tablepostgres=# 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 EXTENSIONpostgres=# 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 executedplpgsql | 1.0 | pg_catalog | PL/pgsql procedural languagepostgres_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 SERVERpostgres=# 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 MAPPingpostgres=# 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 9postgres=# 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 tablepostgres=# drop user mapPing for postgres server server_remote_71 ;DROP USER MAPPingpostgres=# drop server server_remote_71 ;DROP SERVERpostgres=# 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.远程改掉用户密码对当前本地连接无效,但本地再次连接取数会报错 总结
以上是内存溢出为你收集整理的PostgreSQL的postgres_fdw跨库使用全部内容,希望文章能够帮你解决PostgreSQL的postgres_fdw跨库使用所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错,欢迎将内存溢出网站推荐给程序员好友。
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)