PostgreSQL 9.5 使用 import foreign schema 语法一键创建外部表-阿里云开发者社区

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

2016-02-23 7149

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

本文涉及的产品

云原生数据库 PolarDB MySQL 版，Serverless 5000PCU 100GB

云原生数据库 PolarDB 分布式版，标准版 2核8GB

云数据库 RDS MySQL Serverless，0.5-2RCU 50GB

简介：

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/

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

关系型数据库

热门文章

最新文章

相关产品

相关课程

相关电子书

相关实验场景

推荐镜像