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' [, ... ] ) ]