全部博文（2759）
分类： Mysql/postgreSQL
2016-02-09 04:51:19
本次以故事的形式讲述一名不紧跟时代步伐、不主动积极学习的员工在不了解最新特性的情况下，如何失去了“老员工”的优势和领导的信任。
在一个风和日丽，没有雾霾的早上，技术部领导把小明叫到了办公室，问：“兄弟，我想在新的系统上查询一下在用的生产系统的数据，有没有比较快速、方便的方法？新的用的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/
