Postgresql - Foreign data wrappers - postgres_fdw

将外部数据库当作外部表访问，可以访问PG，Oracle，MySQL，MS SQL Server，SQLite等。

我们将在以下环境中进行实验：

CentOS 7 + PG 10.4

访问其他PG server实例

1. Extension

mytest=# create extension postgres_fdw;

CREATE EXTENSION

2. Server

mytest=# create server pg_03ac FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '192.168.4.13', dbname 'mytest', port '5432');

CREATE SERVER

3. User Mapping

mytest=# create user MAPPING FOR mytest SERVER pg_03ac options (user 'chen', password '');

CREATE USER MAPPING

4. Create foreign table

mytest=# create foreign table test_03ac(id int, col1 varchar(10), col2 varchar(10), col3 varchar(10), col4 date) server pg_03ac options (schema_name 'public' , table_name 'test');

CREATE FOREIGN TABLE

# Verify

mytest=# select * from test_03ac;

id | col1 | col2 | col3 | col4

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

1 | aa | aa | aa | 2018-05-22

2 | bb | bb | bb | 2018-05-22

3 | cc | cc | ee | 2018-05-22

(3 rows)

6. Import Foreign Schema

mytest=# import foreign schema public limit to (test,test01) from server pg_03ac into public;

IMPORT FOREIGN SCHEMA

# Verify

mytest=# select * from test;

id | col1 | col2 | col3 | col4

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

1 | aa | aa | aa | 2018-05-22

2 | bb | bb | bb | 2018-05-22

3 | cc | cc | ee | 2018-05-22

4 | d | dd | ddd | 2018-07-10

(4 rows)

mytest=# select * from test01;

col1 | col2 | col3

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

1 | a | 2018-04-13 00:56:51.157712

2 | b | 2018-07-10 23:27:11.993468

(2 rows)

======================================================================

官方给出的create foreign table 和 import foreign schema的语句:

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' [, ... ] ) ]CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name PARTITION OF parent_table [ ( { column_name [ WITH OPTIONS ] [ column_constraint [ ... ] ] | table_constraint } [, ... ]) ] partition_bound_spec SERVER server_name[ OPTIONS ( option 'value' [, ... ] ) ]where column_constraint is:[ CONSTRAINT constraint_name ]{ NOT NULL | NULL | CHECK ( expression ) [ NO INHERIT ] | DEFAULT default_expr }and table_constraint is:[ CONSTRAINT constraint_name ]CHECK ( expression ) [ NO INHERIT ]

IMPORT FOREIGN SCHEMA remote_schema [ { LIMIT TO | EXCEPT } ( table_name [, ...] ) ] FROM SERVER server_name INTO local_schema [ OPTIONS ( option 'value' [, ... ] ) ]