PostgreSQL 9.5新特性：IMPORT FOREIGN SCHEMA

PostgreSQL 9.5新特性：IMPORT FOREIGN SCHEMA

本次以故事的形式讲述一名不紧跟时代步伐、不主动积极学习的员工在不了解最新特性的情况下，如何失去了“老员工”的优势和领导的信任。

在一个风和日丽，没有雾霾的早上，技术部领导把小明叫到了办公室，问：“兄弟，我想在新的系统上查询一下在用的生产系统的数据，有没有比较快速、方便的方法？新的用的PG的数据库有这种方案吗？”

小明：“领导，把数据从生产系统中导出，然后导入到新系统不就OK？”

领导：“比较快速、方便的方法有没有？”

小明：“领导，把数据从生产系统中导出，然后导入到新系统就很快。”

领导：“更快速、方便的方法有没有？”

小明：“领导，把数据从生产系统中导出，然后导入新系统就已经很快了！”

领导对于小明复读机似的回答有些恼火，按耐住心中的不耐烦，问了一句：“导入外部表需要做什么准备工作吗？”

小明胸有成竹的讲：“领导，就是实现创建好ddl就行。”

领导认真的问到：“一共多少张表？”

小明有些不确定：“200张？”

领导有些惊讶：“这么多？！”

小明突然又想起了些什么，小声的说：“领导......我刚想起来...还有一个用户，里面大概有300张表...”(-__-|||)（声音小的连他自己都快听不到了）

领导有点沉不住气了：“滚出去！”

小明红着脸说：“领导！你听我说...”

领导这次彻底的被点燃了：“滚出去！！！”

小明默默的在外面帮领导关上了门......

领导已经气的不行，此时老板的电话已打过来：“怎么样？搞定没？”

领导：“没问题，老板，稍等片刻！”

老板：“好的，搞好了说一声，好跟客户介绍咱们新开发的系统，起码有个查询体验，等你消息！抓紧。”

领导此时想起了公司新来的同事小强，虽然小强平时看起来比较逗逼和活泼，但是小强已经是公司为数不多做数据库的员工了。领导想好了如何跟老板解释系统开发进度慢，拖延体验时间的解释。抱着试试看的心态，传召了小强，表达了在此情景下的需求。

小强：“领导，这个问题，我不敢保证能做好，我尽力。”

领导：“平时看你活泼夸张，今天说话有所保留；说说看，你有什么思路？”

小强：“领导，不动数据库什么玩笑我都敢开，真刀真枪干事儿了，不来那些没用的话。我前段时间看到PostgreSQL9.5有个新特性，可以简单的导入外部表，按模式导入，我觉得可以尝试一下。”

领导：“好，去做吧，注意操作谨慎，别粗心。”

小强：“知道了，半个小时内给您回消息，不论成功还是失败，都即时汇报给您。”

小强出门后，领导点了一颗烟，静待平时看起来“不靠谱”的小强。

同时，领导也在想，小强说的方法理论上听起来确实不错，不知道放在实践中如何，他对比了一下两位员工的方案：

小明的方案：

小强的方案：

小强回到工位上，倒了杯水，心理有一些压力，尽管自己刚测试过新功能（导入外部模式），但是心理还是很紧张，因为他觉得要对领导的信任负责任，要对生产系统的稳定运行负责任，这绝对不是平时打打闹闹、说说笑笑之类的胡闹。

他打开笔记本，深吸了一口气，谨慎的进行了操作：

1.确定一下新环境的数据库版本：

-bash-3.2$psql

psql(9.5beta2)

输入"help"来获取帮助信息.

postgres=#selectversion();

version

-------------------------------------------------------------------------------------------------------------

PostgreSQL9.5beta2onx86_64-pc-linux-gnu,compiledbygcc(GCC)4.1.220080704(RedHat4.1.2-55),64-bit

(1行记录)

2.创建基于用户的模式：

postgres=#\cmusic

您现在已经连接到数据库"music",用户"postgres".

postgres=#createschemaericauthorizationeric;

CREATESCHEMA

3.安装postgres_fdw插件:

music=#createextensionpostgres_fdw;

CREATEEXTENSION

4.创建外部服务器对象：

music=#createservermusic_fdw_serverforeigndatawrapperpostgres_fdwoptions(host'192.168.1.143',dbname'music',port'5432');

CREATESERVER

5.创建用户映射对象：

music=#createusermappingforericservermusic_fdw_serveroptions(user'eric',password'gao');

CREATEUSERMAPPING

连接到生产库看一下生产库的表信息：

music=#\cmusiceric

Youarenowconnectedtodatabase"music"asuser"eric".

music=>\d

Listofrelations

Schema|Name|Type|Owner

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

eric|summary|table|eric

...

(200rows)

6.远程导入整个schema：

music=#importforeignschemaericfromservermusic_fdw_serverintoeric;

IMPORTFOREIGNSCHEMA

7.查看新系统表状态：

music=>\dsummary;

引用的外部表"eric.summary"

栏位|类型|修饰词|FDW选项

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

id|integer||(column_name'id')

info|charactervarying(128)||(column_name'info')

Server:music_fdw_server

FDWOptions:(schema_name'eric',table_name'summary')

8.调整属主和权限：

music=#grantselectoneric.summarytoeric;

GRANT

music=#altertableeric.summaryownertoeric;

ALTERTABLE

9.验证数据量：

music=>selectcount(*)fromsummary;

count

-------

8

(1行记录)

数据已经导入到了新的系统中，小强立刻给领导打电话通报。

领导：“不错，小强，如果上面要求屏蔽掉某些敏感的表或者是只提供基础数据好实现吗？”

小强：“没问题，我给你我的测试报告吧，最近刚做的。”

测试报告：

在源端创建2个新的表：

music=>createtableericgaoIasselect*fromsummary;

SELECT8

music=>createtableericgaoIIasselect*fromsummary;

SELECT8

music=>\d

Listofrelations

Schema|Name|Type|Owner

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

eric|ericgaoi|table|eric

eric|ericgaoii|table|eric

eric|summary|table|eric

(5rows)

排除某个表：

music=>\cmusicpostgres

您现在已经连接到数据库"music",用户"postgres".

music=#IMPORTFOREIGNSCHEMAericEXCEPT(summary)FROMSERVERmusic_fdw_serverINTOeric;

IMPORTFOREIGNSCHEMA

查看一下导入效果：

music=>\d

关联列表

架构模式|名称|类型|拥有者

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

eric|ericgaoi|所引用的外表|postgres

eric|ericgaoii|所引用的外表|postgres

(2行记录)

已经排除表summary。

删掉目标库的表，再测试一下包含某个表的用法：

music=>dropforeigntableericgaoi;

DROPFOREIGNTABLE

music=>dropforeigntableericgaoii;

DROPFOREIGNTABLE

这一次仅导入某些指定的表：

music=#importforeignschemaericlimitto(summary)fromservermusic_fdw_serverintoeric;

IMPORTFOREIGNSCHEMA

music=>\d

关联列表

架构模式|名称|类型|拥有者

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

eric|summary|所引用的外表|postgres

(1行记录)

领导：“谢谢你，小强，回去工作吧”

说着，领导拨通了小明的电话，心想还是要维系好同事关系，多一份人脉，多很多机会，再说小明也是老员工了，刚才对他发火也有些冲动。

小明进了办公司，领导微笑着，说：“小明，刚才事出紧急，有些急躁，抱歉。”

小明：“领导，没关系，确实是我技术不精，以后多多学习！”

领导：“刚才小强把表都整过来了，我看查询有些慢，你给看一下原因。”

小明：“好的领导，我收集一下信息，完事儿立刻给你汇报！”

小明回到工位执行了几条命令，收集了一下性能方面的信息：

在新系统上：

music=>analyzeeric.summary;

ANALYZE

music=>explainanalyzeselect*fromsummary;

QUERYPLAN

----------------------------------------------------------------------------------------------------------

ForeignScanonsummary(cost=100.00..101.24rows=8width=28)(actualtime=4.308..4.319rows=8loops=1)

Planningtime:0.076ms

Executiontime:8.308ms

(3行记录)

在生产系统的服务器看：

music=>analyzeeric.summary;

ANALYZE

music=>explainanalyzeselect*fromsummary;

QUERYPLAN

--------------------------------------------------------------------------------------------------

SeqScanonsummary(cost=0.00..1.08rows=8width=28)(actualtime=0.003..0.004rows=8loops=1)

Planningtime:0.108ms

Executiontime:0.023ms

(3rows)

小明把以上信息提交给了领导，解释说:”领导，你看收集到的信息显示，远程的表查询起来就是慢，看来这功能还是不靠谱啊，我建议还是用我的方案，导数据，或者可以买第三方软件进行同步数据，把数据实时同步到本地。”

领导：”小明...老师...公司花钱请你来是让你给个报告然后告诉老板要花钱买新产品的吗？那还用你做啥？同步数据可以用HotStandby，还用第三方软件？你一个导数据的方案想通吃所有项目？！目前这查询速度客户是可以接受的，并不是相对速度慢就是不可以好吗？！！想要查远程数据并且提升一下性能可以用物化视图好吗？！！！“

小明：”领导，不好意思...物化视图是...什么？“

领导：”滚出去！！！“

外部表在查询速度上还是有一定的延迟的，如果在项目中可以接受实际的查询速度就OK。如果接受不了，可以尝试使用物化视图，当然还有很多方案，方法总比困难多~~~

物化视图在此就不多浪费篇幅了，如果感兴趣的话，可参考如下文章，里面有对物化视图的性能测试：

http://gaoqiangdba.blog.163.com/blog/static/245970045201510171821363/