Chinaunix首页 | 论坛 | 认证专区 | 博客 登录 | 注册

Eric Gao's Bloggaoqiang.blog.chinaunix.net

Oracle MySQL PostgreSQL AIX Linux Storage

  • 博客访问: 1867097
  • 博文数量: 196
  • 博客积分: 3409
  • 博客等级: 中校
  • 技术积分: 3858
  • 用 户 组: 普通用户
  • 注册时间: 2010-12-24 10:21
  • 认证徽章:
个人简介

● ITPUB名人堂嘉宾 ● ChinaUnix社区博客专家 ● ChinaUnix社区Oracle板块版主 ● 优酷网认证音乐牛人:EricGuitar ● SDOUG 核心成员 ●E-mail:gaoqiangdba@163.com

文章分类

全部博文(196)

文章存档

2016年(5)

2015年(39)

2014年(27)

2013年(18)

2012年(24)

2011年(63)

2010年(20)

微信关注

IT168企业级官微



微信号:IT168qiye



系统架构师大会



微信号:SACC2013

订阅
热词专题

分类: Mysql/postgreSQL


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

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

 

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

 

  领导:“比较快速、方便的方法有没有?”

 

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

 

  领导:“更快速、方便的方法有没有?”

 

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

 

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

 


 

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

 

  领导认真的问到:“一共多少张表?”

 

  小明有些不确定:“200张?”

 

  领导有些惊讶:“这么多?!”

 

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

 

  领导有点沉不住气了:“滚出去!”

 

  小明红着脸说:“领导!你听我说...”

 

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


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

 

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

 

  领导:“没问题,老板,稍等片刻!”

 

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

 

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

 

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

 

  领导:“平时看你活泼夸张,今天说话有所保留;说说看,你有什么思路?”

 

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

 

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

 

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

 

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

 

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

 

小明的方案:

 


 

 

  

 

小强的方案:


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

 

  

 

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

 

 

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

-bash-3.2$ psql

psql (9.5beta2)

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

 

postgres=# select version();

                                                   version                                                   

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

 PostgreSQL 9.5beta2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-55), 64-bit

(1 行记录)

 

 

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

postgres=# \c music

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

postgres=# create schema eric  authorization eric;

CREATE SCHEMA

 

3.安装postgres_fdw插件:

music=# create extension postgres_fdw;

CREATE EXTENSION

 

 

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

music=# create server music_fdw_server foreign data wrapper postgres_fdw options (host '192.168.1.143',dbname 'music',port '5432');

CREATE SERVER

 

5.创建用户映射对象:

music=# create user mapping for eric  server music_fdw_server options (user 'eric',password 'gao');

CREATE USER MAPPING

 

 

 

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

music=# \c music eric

You are now connected to database "music" as user "eric".

 

music=> \d

              List of relations

 Schema |  Name   |     Type      |  Owner   

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

 eric   | summary | table         | eric

...

(200rows)

 

6.远程导入整个schema

music=# import foreign schema eric from server music_fdw_server into eric;

IMPORT FOREIGN SCHEMA

 

 

7.查看新系统表状态:

 

music=> \d summary;

                  引用的外部表 "eric.summary"

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

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

 id   | integer                |        | (column_name 'id')

 info | character varying(128) |        | (column_name 'info')

Server: music_fdw_server

FDW Options: (schema_name 'eric', table_name 'summary')

 

 

8.调整属主和权限:

music=# grant select on eric.summary to eric;

GRANT

 

music=# alter table eric.summary owner to eric;

ALTER TABLE

 

 

9.验证数据量:

music=> select  count(*) from summary;

 count 

-------

     8

(1 行记录)

 

 

 

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

 

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

 

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

 

 

 

测试报告:

在源端创建2个新的表:

music=> create table ericgaoI as select * from summary;

SELECT 8

music=> create table ericgaoII as select * from summary;

SELECT 8

 

music=> \d

               List of relations

 Schema |   Name    |     Type      |  Owner   

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

 eric   | ericgaoi  | table         | eric

 eric   | ericgaoii | table         | eric

 eric   | summary   | table         | eric

(5 rows)

 

 

 

排除某个表:

music=> \c music postgres

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

music=#  IMPORT FOREIGN SCHEMA eric EXCEPT (summary) FROM SERVER music_fdw_server INTO eric;

IMPORT FOREIGN SCHEMA

 

 

查看一下导入效果:

music=> \d

                    关联列表

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

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

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

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

(2 行记录)

 

已经排除表summary。

 

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

music=> drop foreign table ericgaoi;

DROP FOREIGN TABLE

music=> drop foreign table ericgaoii;

DROP FOREIGN TABLE

 

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

music=#  import foreign schema eric limit to  (summary) from server music_fdw_server into eric;

IMPORT FOREIGN SCHEMA

music=> \d

                   关联列表

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

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

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

(1 行记录)

 

 

 

 

 

领导:“谢谢你,小强,回去工作吧”

 

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

 

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

 

小明:“领导,没关系,确实是我技术不精,以后多多学习!”

 

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

 

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

 

 

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

 

在新系统上:

music=> analyze eric.summary;

ANALYZE

 

 

music=> explain analyze select * from summary;

                                                QUERY PLAN                                                

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

 Foreign Scan on summary  (cost=100.00..101.24 rows=8 width=28) (actual time=4.308..4.319 rows=8 loops=1)

 Planning time: 0.076 ms

 Execution time: 8.308 ms

(3 行记录)

 

 

 

 

 

在生产系统的服务器看:

music=> analyze eric.summary;

ANALYZE

music=> explain analyze select * from summary;

                                            QUERY PLAN                                            

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

 Seq Scan on summary  (cost=0.00..1.08 rows=8 width=28) (actual time=0.003..0.004 rows=8 loops=1)

 Planning time: 0.108 ms

 Execution time: 0.023 ms

(3 rows)

 

 

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

 

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

 

小明:”领导,不好意思...物化视图是...什么?“

 

领导:”滚出去!!!“

 


 

 

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

 

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

 

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

 

阅读(9018) | 评论(1) | 转发(2) |
给主人留下些什么吧!~~

cuyunwu3212016-12-15 15:10:05

牛,技术解释的很好,情节很吸引人,请告诉我你的起点账号!

评论热议
请登录后评论。

登录 注册