PHP8.1.21版本已发布
vue8.1.21版本已发布
jquery8.1.21版本已发布

PostgreSQL 9.5新特性:IMPORT FOREIGN SCHEMA

原创
2016-06-13 08:47:27 879浏览

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/

声明:本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn核实处理。