PostgreSQL 9.5新特性：IMPORT FOREIGN SCHEMA_PHP教程
- WBOY原创
- 2016-07-12 09:00:231031浏览
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/