当前位置： 首页 > news >正文

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

news 来源：原创 2024/4/28 19:34:42

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/

相关文章：

  • 应用如何在后台执行
  • c语言随机数
  • 混合使用Azure LB和ILB访问相同web服务（3）
  • git服务器修改ssh端口后配置方法
  • POJ 1005 I Think I Need a Houseboat
  • compileDebugJavaWithJavac
  • OSI七层与TCP/IP四层模型
  • 3-9-模拟银行排队过程-栈和队列-第3章-《数据结构》课本源码-严蔚敏吴伟民版...
  • 《从零开始学Swift》学习笔记（Day 24）——枚举（Day 24）——枚举
  • 日志管理
  • 初探验证码识别
  • HttpClient4.5教程-基础 1.1
  • Spring的IoC容器（转）BeanFactory
  • 最短路问题------分别用深搜和广搜去解题
  • linux ps 查看耗cpu高的线程
  • 【347天】每日项目总结系列085（2018.01.18）
  • CentOS从零开始部署Nodejs项目
  • Java超时控制的实现
  • linux学习笔记
  • OpenStack安装流程（juno版）- 添加网络服务（neutron）- controller节点
  • php面试题 汇集2
  • quasar-framework cnodejs社区
  • Vue2 SSR 的优化之旅
  • 欢迎参加第二届中国游戏开发者大会
  • 检测对象或数组
  • 排序算法之--选择排序
  • 我的zsh配置, 2019最新方案
  • !!【OpenCV学习】计算两幅图像的重叠区域
  • #{}和${}的区别是什么 -- java面试
  • #周末课堂# 【Linux + JVM + Mysql高级性能优化班】（火热报名中~~~）
  • $$$$GB2312-80区位编码表$$$$
  • $.each()与$(selector).each()
  • $分析了六十多年间100万字的政府工作报告，我看到了这样的变迁
  • （1）Nginx简介和安装教程
  • (17)Hive ——MR任务的map与reduce个数由什么决定？
  • （分享）一个图片添加水印的小demo的页面，可自定义样式
  • （篇九）MySQL常用内置函数
  • （一）Mocha源码阅读: 项目结构及命令行启动
  • (转)Android学习系列(31)--App自动化之使用Ant编译项目多渠道打包
  • (转)socket Aio demo
  • （转）winform之ListView
  • (转载)虚函数剖析
  • ***linux下安装xampp,XAMPP目录结构（阿里云安装xampp）
  • .bat批处理（八）：各种形式的变量%0、%i、%%i、var、%var%、!var!的含义和区别
  • .halo勒索病毒解密方法|勒索病毒解决|勒索病毒恢复|数据库修复
  • .net core 实现redis分片_基于 Redis 的分布式任务调度框架 earth-frost
  • .Net Core/.Net6/.Net8 ，启动配置/Program.cs 配置
  • .NET Core日志内容详解，详解不同日志级别的区别和有关日志记录的实用工具和第三方库详解与示例
  • .NET Framework 服务实现监控可观测性最佳实践
  • .Net Remoting(分离服务程序实现) - Part.3
  • .Net 高效开发之不可错过的实用工具
  • .NET开发者必备的11款免费工具
  • .net之微信企业号开发(一) 所使用的环境与工具以及准备工作
  • .考试倒计时43天！来提分啦！
  • [ IO.File ] FileSystemWatcher