简介

PostgreSQL的数据库逻辑上是相互独立的，和Oracle类似，如果要访问其他数据库，需要做跨库操作，fdw是foreign-data wrapper的一个简称，可以叫外部封装数据，postgres_fdw实现的是各个PostgreSQL数据库及远程数据库之间的跨库操作; 基本操作分为以下4步：

  1. 使用CREATE EXTENSION来安装postgres_fdw扩展。
  2. 使用CREATE SERVER创建外部服务器对象， 以表示你想连接的每一个远程数据库。指定除了user和 password之外的连接信息作为该服务器对象的选项。
  3. 使用CREATE USER MAPPING， 为每个要允许访问外部服务器的数据库用户创建用户映射。 指定远程用户名和口令作为用户映射的user和password选项。
  4. 使用CREATE FOREIGN TABLE或IMPORT FOREIGN SCHEMA， 为每一个你想访问的远程表创建一个外部表。外部表的列必须匹配被引用的远程表。 但是，如果指定正确的远程名称作为外部表对象的选项，则可以使用与远程表不同的表和/或列名。

更多信息请参考: http:—www.postgres.cn/docs/9.5/postgres-fdw.html

安装扩展

在安装PostgreSQL时已经附带了postgres_fdw扩展，可以在安装目录/share/extension下面看到,直接安装即可。

--安装postgres_fdw扩展，默认模式
create extension postgres_fdw;

--安装postgres_fdw扩展，指定模式
create extension postgres_fdw with schema 模式名;

--卸载postgres_fdw扩展
drop extension postgres_fdw;

创建外部服务器对象

语句: CREATE SERVER server_name [ TYPE ‘server_type’ ] [ VERSION ‘server_version’ ] FOREIGN DATA WRAPPER fdw_name [ OPTIONS ( option ‘value’ [, … ] ) ]

--创建外部服务器,options内的host=想要连接的数据库的地址、port=想要连接的数据库所在服务器的端口、dbname=想要连接的数据库的一个库
create server my_server foreign data wrapper postgres_fdw options(host '127.0.0.1',port '5555',dbname 'postgres');

--查看外部服务器
select * from pg_foreign_server;

--更改外部服务器的定义
alter server my_server options (host '172.0.16.1', post '5432');

--删除外部服务器
--cascade：自动删除依赖于该服务器的对象（例如用户映射）。
--restrict：如果有任何对象依赖于该服务器，则拒绝删除它。这是默认值。
drop server [if exists] my_server [cascade | restrict];

创建用户映射

语句: CREATE USER MAPPING FOR { user_name | USER | CURRENT_USER | PUBLIC } SERVER server_name [ OPTIONS ( option ‘value’ [ , … ] ) ]

--创建用户映射
create user mapping [IF NOT EXISTS] for postgres server my_server options (user 'postgres', password '123456');

--查看用户映射
select * from pg_user_mappings;

--更改用户映射
ALTER USER MAPPING FOR { user_name | USER | CURRENT_USER | SESSION_USER | PUBLIC }
    SERVER server_name
    OPTIONS ( [ ADD | SET | DROP ] option ['value'] [, ... ] );
--为服务器my_server的用户映射postgres更改口令
alter user mapping for postgres SERVER my_server options (set password 'postgres');

--删除用户映射
DROP USER MAPPING [IF EXISTS] FOR postgres SERVER my_server;

创建远程表

创建的远程表客户端是看不见的，可以通过语句查询.

select * from pg_foreign_table;

创建单个远程表

语句: CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name ( [ { column_name data_type [ OPTIONS ( option ‘value’ [, … ] ) ] [ COLLATE collation ] [ column_constraint [ … ] ] | table_constraint } [, … ] ] ) [ INHERITS ( parent_table [, … ] ) ] SERVER server_name [ OPTIONS ( option ‘value’ [, … ] ) ]

--创建远程数据库的public模式的user表映射到本地的test模式的user表
--1.test.user的字段和public.user的字段名必须一样
--2.test.user的字段和public.user的字段类型必须能相互默认转换,比如integer->text
--满足以上两个条件才能正确查询,当然即使字段或者类型不一样远程表还是可以创建,只是在查询时会查不到或者可能出现错误
create foreign table test.user(
	id integer,
	name text
)
server my_server
options (schema_name 'public', table_name 'user');

--更改远程表,更多见 http://postgres.cn/docs/13/sql-alterforeigntable.html
alter foreign table [IF EXISTS] test.user options (add age integer);

--删除远程表
drop foreign table test.user;

创建整个模式

语句: IMPORT FOREIGN SCHEMA remote_schema [ { LIMIT TO | EXCEPT } ( table_name [, …] ) ] FROM SERVER server_name INTO local_schema [ OPTIONS ( option ‘value’ [, … ] ) ]

--导入整个模式的表,以下是将远端数据库的private模式下的所有表映射到本地数据库的test模式
import foreign schema private from server my_server into test;