简介

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下面看到,直接安装即可。

1
2
3
4
5
6
7
8
--安装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’ [, … ] ) ]

1
2
3
4
5
6
7
8
9
10
11
12
13
--创建外部服务器,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’ [ , … ] ) ]

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
--创建用户映射
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;

创建远程表

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

1
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’ [, … ] ) ]

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
--创建远程数据库的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’ [, … ] ) ]

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