PostgreSQL 9.5 使用 import foreign schema 语法一键创建外部表

最新推荐文章于 2023-03-29 11:00:34 发布

weixin_33836223

最新推荐文章于 2023-03-29 11:00:34 发布

阅读量1.6k

点赞数 3

文章标签：数据库 git

原文链接：https://yq.aliyun.com/articles/6625

版权

PostgreSQL 9.5提供了一个快捷的将远程数据库中的表，视图或物化视图转换成外部表的方式, 使用import foreign schema可以直接将远端的整个schema中的所有表或部分表直接创建在本地的某个指定的schema下.

Command:     IMPORT FOREIGN SCHEMA  
Description: import table definitions from a foreign server  
Syntax:  
IMPORT FOREIGN SCHEMA remote_schema  
[ { LIMIT TO | EXCEPT } ( table_name [, ...] ) ]  
FROM SERVER server_name  
INTO local_schema  
[ OPTIONS ( option 'value' [, ... ] ) ]

测试 :
remote db postgresql 9.4.1

postgres=# create schema rmt;  
CREATE SCHEMA  
postgres=# create table rmt(id int, info text, crt_time timestamp);  
CREATE TABLE  
postgres=# create table rmt1(id int, info text, crt_time timestamp);  
CREATE TABLE  
postgres=# create table rmt2(id int, info text, crt_time timestamp);  
CREATE TABLE  

postgres=# insert into rmt select generate_series(1,100000), md5(random()::text), clock_timestamp();  
INSERT 0 100000  
postgres=# insert into rmt1 select generate_series(1,100000), md5(random()::text), clock_timestamp();  
INSERT 0 100000  
postgres=# insert into rmt2 select generate_series(1,100000), md5(random()::text), clock_timestamp();  
INSERT 0 100000  
postgres=# alter table rmt add constraint pk primary key (id);  
ALTER TABLE  
postgres=# alter table rmt add constraint ck check (length(info)>1);  
ALTER TABLE  

postgres=# alter table rmt set schema rmt;  
ALTER TABLE  
postgres=# alter table rmt1 set schema rmt;  
ALTER TABLE  
postgres=# alter table rmt2 set schema rmt;  
ALTER TABLE  
postgres=# \dt rmt.*  
        List of relations  
 Schema | Name | Type  |  Owner     
--------+------+-------+----------  
 rmt    | rmt  | table | postgres  
 rmt    | rmt1 | table | postgres  
 rmt    | rmt2 | table | postgres  
(3 rows)

local db postgresql 9.5

postgres=# create extension postgres_fdw;  
CREATE EXTENSION  
postgres=# create server rmt foreign data wrapper postgres_fdw options (hostaddr '127.0.0.1', port '1921', dbname 'postgres');  
CREATE SERVER  
postgres=# create user mapping for postgres server rmt options (user 'postgres', password 'postgres');  
CREATE USER MAPPING  

postgres=# create schema r1;  
CREATE SCHEMA  
postgres=# import FOREIGN SCHEMA rmt from server rmt into r1 ;  
IMPORT FOREIGN SCHEMA  

postgres=# \det+  
                             List of foreign tables  
 Schema | Table | Server |              FDW Options               | Description   
--------+-------+--------+----------------------------------------+-------------  
 r1     | rmt   | rmt    | (schema_name 'rmt', table_name 'rmt')  |   
 r1     | rmt1  | rmt    | (schema_name 'rmt', table_name 'rmt1') |   
 r1     | rmt2  | rmt    | (schema_name 'rmt', table_name 'rmt2') |   
(3 rows)  

postgres=# \d r1.rmt  
                            Foreign table "r1.rmt"  
  Column  |            Type             | Modifiers |       FDW Options          
----------+-----------------------------+-----------+--------------------------  
 id       | integer                     | not null  | (column_name 'id')  
 info     | text                        |           | (column_name 'info')  
 crt_time | timestamp without time zone |           | (column_name 'crt_time')  
Server: rmt  
FDW Options: (schema_name 'rmt', table_name 'rmt')  

postgres=# select count(*) from r1.rmt1;  
 count    
--------  
 100000  
(1 row)  

postgres=# select count(*) from r1.rmt;  
 count    
--------  
 100000  
(1 row)  

postgres=# select count(*) from r1.rmt2;  
 count    
--------  
 100000  
(1 row)

还可以使用limit to或者except来控制只导某些表, 或排除某些表.

postgres=# drop foreign table r1.rmt;  
DROP FOREIGN TABLE  
postgres=# drop foreign table r1.rmt1;  
DROP FOREIGN TABLE  
postgres=# drop foreign table r1.rmt2;  
DROP FOREIGN TABLE  

postgres=# import FOREIGN SCHEMA rmt limit to (rmt) from server  rmt into r1 ;  
IMPORT FOREIGN SCHEMA  
postgres=# \det+  
                            List of foreign tables  
 Schema | Table | Server |              FDW Options              | Description   
--------+-------+--------+---------------------------------------+-------------  
 r1     | rmt   | rmt    | (schema_name 'rmt', table_name 'rmt') |   
(1 row)  

postgres=# drop foreign table r1.rmt;  
DROP FOREIGN TABLE  
postgres=# import FOREIGN SCHEMA rmt except (rmt) from server  rmt into r1 ;  
IMPORT FOREIGN SCHEMA  
postgres=# \det+  
                             List of foreign tables  
 Schema | Table | Server |              FDW Options               | Description   
--------+-------+--------+----------------------------------------+-------------  
 r1     | rmt1  | rmt    | (schema_name 'rmt', table_name 'rmt1') |   
 r1     | rmt2  | rmt    | (schema_name 'rmt', table_name 'rmt2') |   
(2 rows)

注意, 导入时会同时将视图, 物化视图, 外部表都一并导入, 除非使用except来排除.
remote db postgresql 9.4.1

postgres=# \dt  
        List of relations  
 Schema | Name | Type  |  Owner     
--------+------+-------+----------  
 public | rt1  | table | postgres  
 public | rt2  | table | postgres  
 public | tbl  | table | postgres  
 public | test | table | postgres  
(4 rows)  
postgres=# \dn  
  List of schemas  
  Name  |  Owner     
--------+----------  
 public | postgres  
 rmt    | postgres  
(2 rows)  
postgres=# create view rmt.v1 as select * from test;  
CREATE VIEW  
postgres=# \dv rmt.*  
        List of relations  
 Schema | Name | Type |  Owner     
--------+------+------+----------  
 rmt    | v1   | view | postgres  
(1 row)  
postgres=# create server rmt foreign data wrapper postgres_fdw options (hostaddr '127.0.0.1', port '1921', dbname 'postgres');  
CREATE SERVER  
postgres=# create user mapping for postgres server rmt options (user 'postgres', password 'postgres');  
CREATE USER MAPPING  
postgres=# create foreign table rmt.ft1 (id int, info text) server rmt options (schema_name 'public', table_name 'test');   
CREATE FOREIGN TABLE  
postgres=# SELECT id, info FROM rmt.ft1;  
  id  | info    
------+-------  
    1 | test1  
    2 | test2  
    3 | test2  
    4 | test2  
    5 | test2  
    6 | test2  
    7 | test2  
    8 | test3  
  100 | test3  
 1000 | test4  
    2 | test2  
    2 | test2  
    2 | test2  
(13 rows)

local db postgresql 9.5
导入时会同时将视图, 物化视图, 外部表都一并导入, 除非使用except来排除.

postgres=# drop foreign table r1.rmt1;  
DROP FOREIGN TABLE  
postgres=# drop foreign table r1.rmt2;  
DROP FOREIGN TABLE  
postgres=# import FOREIGN SCHEMA rmt except (rmt) from server  rmt into r1 ;  
IMPORT FOREIGN SCHEMA  
postgres=# \det+  
                             List of foreign tables  
 Schema | Table | Server |              FDW Options               | Description   
--------+-------+--------+----------------------------------------+-------------  
 r1     | ft1   | rmt    | (schema_name 'rmt', table_name 'ft1')  |   
 r1     | rmt1  | rmt    | (schema_name 'rmt', table_name 'rmt1') |   
 r1     | rmt2  | rmt    | (schema_name 'rmt', table_name 'rmt2') |   
 r1     | v1    | rmt    | (schema_name 'rmt', table_name 'v1')   |   
(4 rows)  
postgres=# select * from r1.v1;  
  id  | info    
------+-------  
    1 | test1  
    2 | test2  
    3 | test2  
    4 | test2  
    5 | test2  
    6 | test2  
    7 | test2  
    8 | test3  
  100 | test3  
 1000 | test4  
    2 | test2  
    2 | test2  
    2 | test2  
(13 rows)  
postgres=# select * from r1.ft1;  
  id  | info    
------+-------  
    1 | test1  
    2 | test2  
    3 | test2  
    4 | test2  
    5 | test2  
    6 | test2  
    7 | test2  
    8 | test3  
  100 | test3  
 1000 | test4  
    2 | test2  
    2 | test2  
    2 | test2  
(13 rows)

最后需要注意的是, 目前只有postgres_fdw支持import FOREIGN SCHEMA语法, 其他fdw需要自己去实现.

[参考]

1. http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=59efda3e50ca4de6a9d5aa4491464e22b6329b1e  
Implement IMPORT FOREIGN SCHEMA.  

This command provides an automated way to create foreign table definitions  
that match remote tables, thereby reducing tedium and chances for error.  
In this patch, we provide the necessary core-server infrastructure and  
implement the feature fully in the postgres_fdw foreign-data wrapper.  
Other wrappers will throw a "feature not supported" error until/unless  
they are updated.  

Ronan Dunklau and Michael Paquier, additional work by me  
2. http://www.postgresql.org/docs/devel/static/sql-importforeignschema.html  
3. http://blog.163.com/digoal@126/blog/static/163877040201521162114359/