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
发布者：全栈程序员-站长，转载请注明出处：https://javaforall.net/109866.html原文链接：https://javaforall.net
