postgres9.5.1外部表FDW-postgres_fdw

postgresql 9提供可远程可读可写数据库的功能，fdw（Foreign Data Wrappers），即可以在本地数据库操作远程数据库。这和dblink有相似之处，但是fdw提供更多透明和标准的语法，而且性能也比dblink好。

9.5版本新增了操作简化的新特性。

1实验目的

在服务器192.168.204.143的数据库中远程增，删，改，查192.168.204.144服务器中test数据库中示例表。

2准备环境

虚拟机2台:

主机名	ip
postgres1	192.168.204.143（安装好postgres9.5.1+初始用户postgres+测试数据库testbak）
Postgres2	192.168.204.144（安装好postgres9.5.1+初始用户postgres+测试数据库test）

服务器中安装数据库：具体安装步骤见：http://blog.csdn.net/sunziyue/article/details/49122721

3postgres_fdw 编译安装

PostgreSQL 9.5.1包含postgres_fdw模块, 已经整合在源码包中，手工编译

$ cd  /pgsolf/postgresql-9.5.1/contrib/postgres_fdw

$USE_PGX=1  make install

结果会在$PGHOME/share/postgresql/extension下生成2个文件

postgres_fdw.control

postgres_fdw--1.0.sql

$PGHOME/lib/postgresql/目录下生成postgres_fdw.so

或者直接拷贝已经编译好的包 postgres_fdw.so到$PGHOME/lib/postgresql/目录下 postgres_fdw.control 与postgres_fdw--1.0.sql 到$PGHOME/share/postgresql/extension目录下

4pg数据库中操作

4.1源库中创建库，模式，表（192.168.204.144服务器数据库操作）

create database test;

create schema test;

create table test.testtb1(id int,name text);

insert into test.testtb1 values(1, 'test1');

insert into test.testtb1 values(2, 'test2');

create table test.testtb2(id int,name text);

insert into test.testtb2 values(1, 'test1');

以下为192.168.204.143服务器数据库操作

4.2创建库，模式

create database testbak;

create schema testbak;

4.3安装postgres_fdw扩展与授权

testbak=#create extension postgres_fdw;

testbak=#grant usage on foreign data wrapper postgres_fdw to postgres;

4.4创建远程数据库服务器

createserver pg_pg foreign data wrapper postgres_fdw options (host '192.168.204.144',dbname 'test',port '5432');

//其中 pg_pg自定义服务名称，host 远程数据库IP,dbname远程库名称，port 远程库端口。

CREATE SERVER server_name [ TYPE 'server_type' ] [ VERSION 'server_version' ] FOREIGN DATA WRAPPER fdw_name [ OPTIONS ( option 'value' [, ... ] ) ] server_name：远程数据库的名称。 server_type、server_version：可选，可能对一些外部数据封装有用。 fdw_name：外部数据分装名字，这里我们是postgres_fdw。 options：其他可选参数，包括数据库地址，数据库名称和端口等。

4.5创建映射用户

CREATE USER MAPPING指定连接远程数据库的用户，这些数据和创建的foreign server远程服务器一起被封装起来。拥有foreign server权限的用户可以创建映射用户。

create user mapping FOR PUBLIC server pg_pg options(user 'postgres',password 'postgres');--192.168.182.144中数据库用户名密码

create user mapping命令语法： CREATE USER MAPPING FOR { user_name | USER | CURRENT_USER | PUBLIC } SERVER server_name [OPTIONS ( option 'value' [ , ... ] ) ] user_name：现有的用户映射到远程server。 server_name：当前需要用户映射到的远程数据库服务器名。 options：其他可选，包括用户名、密码等。

4.6创建远程表

create foreign table testbak.testtb1bak(id int,name text) server pg_pg options (schema_name 'test',table_name 'testtb1');（其中表中字段与远程库中表字段需要对应）

4.7查看效果

在本地操作testbak.testtb1bak表可增删改查远程192.168.182.144中test. testtb1中对应数据。

testbak=#show search_path;

testbak=#set search_path to testbak,"$user",public;

testbak=#\dE

testbak=#select *  from  testbak.testtb1bak;

 id | name 

----+-------

  1 | test1

  2 | test2

(2rows)

testbak=#insert into testbak.testtb1bak values (3,'test3');

INSERT0 1

testbak=#select *  from  testbak.testtb1bak;

 id | name 

----+-------

  1 | test1

  2 | test2

  3 | test3

(3rows)

testbak=#delete from  testbak.testtb1bak whereid=3;

DELETE1

testbak=#select *  from  testbak.testtb1bak;

 id | name 

----+-------

  1 | test1

  2 | test2

(2rows)

5其他

在本地函数中fdw可以正常使用。

查询数据库安装插件：\dx   或者select extname,extversion from pg_extension;

查询本地外部表:\dE或者select * from pg_foreign_table；

修改定义的外部表： alter foreign table testtb1bak RENAME  to testtb1bak1;

删除数据库安装插件:postgres=# drop extension postgres_fdw cascade;

语法具体参考: 

http://www.postgresql.org/docs/current/static/sql-dropextension.html

6 9.5.1新特性IMPORT FOREIGN SCHEMA 

新特性：在本地数据库可以自动创建所有映射表

IMPORT FOREIGN SCHEMA remote_schema

[ {LIMIT TO | EXCEPT } ( table_name [, ...] ) ]

FROM SERVER server_name

INTO local_schema

[OPTIONS ( option 'value' [, ... ] ) ]

语法示例：

IMPORT FOREIGN SCHEMA  xxx  FROM SERVER pgsql_srv INTO yyy;

把远程数据库模式xxx中的表全部创建对应得映射表在本地数据库模式yyy中

 IMPORT FOREIGN SCHEMA xxx EXCEPT (X, Y) ---排除某些表

FROM SERVER servername INTO yyy;

 IMPORT FOREIGN SCHEMA xxx LIMIT TO (X, Y) --指定某些表

FROM SERVER servername INTO yyy;

以下为192.168.204.143服务器数据库操作 接上示例 testbak=# drop foreign table testbak.testtb1bak; testbak=# IMPORT FOREIGN SCHEMA  test FROM SERVER pg_pg INTO testbak; IMPORT FOREIGN SCHEMA testbak=# \dE              List of relations  Schema  |  Name   |     Type      | Owner ---------+---------+---------------+-------  testbak | testtb1 | foreign table | dba  testbak | testtb2 | foreign table | dba (2 rows) testbak=# select *   from  testbak.testtb1;  id | name  ----+-------   1 | test1   2 | test2   3 | test3 (3 rows)