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

Eric Gao's Bloggaoqiang.blog.chinaunix.net

Oracle MySQL PostgreSQL AIX Linux Storage

  • 博客访问: 1866269
  • 博文数量: 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

PostgreSQL不同模式(SCHEMA)之间迁移数据 - Eric Gao - Eric Gaos Blog

  PostgreSQL提供了外部数据包装器postgres_fdw,作用跟dblink相同,即查询远程数据库中的数据信息,但是postgres_fdw比dblink在某些场景更稳定、更方便。同时PostgreSQL也提供对其他数据库如Oracle和MySQL等数据库的外部数据包装器:oracle_fdw和mysql_fdw,可查询Oracle和MySQL数据库中的相关表信息。

  注意,不论使用PG的哪种外部数据包装器,尽可能的保证两端的表中字段的数量、类型和顺序一致,否则可能导致很多问题。

下面我们来体验一下该功能:

测试环境准备:

在远程数据库上创建新的数据库musician,并在库里创建表man,插入测试数据:
postgres=# create database musician;
CREATE DATABASE
music=# \c musician eric
您现在已经连线到数据库 "musician",用户 "eric".
musician=> create table man(id bigint);
CREATE TABLE
musician=> insert into man select * from generate_series(1,8000);
INSERT 0 8000
musician=> select count(*) from man;
 count 
-------
  8000
(1 行记录)

musician=> \d
             关联列表
 架构模式 | 名称 |  型别  | 拥有者 
----------+------+--------+--------
 public   | man  | 资料表 | eric
(1 行记录)


在本地测试库安装插件postgres_fdw:
postgres=# create extension postgres_fdw;
CREATE EXTENSION
music=> \c music postgres
You are now connected to database "music" as user "postgres".

创建外部服务器对象,需要指定相关信息:
对象名称:musician_fdw_server
包装器类型:postgres_fdw,如果要连接Oracle或者MySQL数据库的话,可用oracle_fdw或mysql_fdw
主机IP:192.168.1.129
数据库名称:musician(刚刚创建的数据库名)
端口号:5432
music=# create server musician_fdw_server foreign data wrapper postgres_fdw options (host '192.168.1.129',dbname 'musician',port '5432');
CREATE SERVER

创建用户映射,相关信息:
本地用户:eric
外部服务器对象:musician_fdw_server
远程数据库用户名密码:eric,gao
music=#  create user mapping for eric  server musician_fdw_server options (user 'eric',password 'gao');
CREATE USER MAPPING

配置外部表,相关信息:
外部表在本库的名称:manid
外部服务器:musician_fdw_server
外部表名:man
music=> \c music postgres
You are now connected to database "music" as user "postgres".
music=# create foreign table manid(id bigint) server musician_fdw_server options(table_name 'man');
CREATE FOREIGN TABLE

注意:
  在远程数据库的pg_hba.conf中修改一下相关的配置:

最终这样修改的:
# IPv4 local connections:
host    all           all            192.168.1.0/24              md5

因为远程连接的话,PG要求是需要有密码验证的,设置成trust的话会报错。

设置完成之后验证一下查询效果:
music=> \c music eric
You are now connected to database "music" as user "postgres".
music=# select count(*) from manid;
 count 
-------
  8000
(1 row)

数据是可以看到了,性能如何呢?我们来测试一下:

在远程数据库本地执行语句:
musician=> explain analyze select count(*) from man;
                                                 QUERY PLAN                                                 
------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=136.00..136.01 rows=1 width=0) (actual time=26.128..26.129 rows=1 loops=1)
   ->  Seq Scan on man  (cost=0.00..116.00 rows=8000 width=0) (actual time=0.014..13.068 rows=8000 loops=1)
 Planning time: 0.045 ms
 Execution time: 26.189 ms
(4 行记录)

在本地数据库本地执行语句:
music=> explain analyze select count(*) from manid;
                                                     QUERY PLAN                                                     
--------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=220.92..220.93 rows=1 width=0) (actual time=42.804..42.804 rows=1 loops=1)
   ->  Foreign Scan on manid  (cost=100.00..212.39 rows=3413 width=0) (actual time=2.264..41.813 rows=8000 loops=1)
 Planning time: 0.067 ms
 Execution time: 44.411 ms
(4 rows)

看起来差别不是太大,但是测试的数据量和类型也不复杂,那我们接下来换一条语句:

远程数据库本地执行语句:
musician=> explain analyze select * from man;
                                              QUERY PLAN                                              
------------------------------------------------------------------------------------------------------
 Seq Scan on man  (cost=0.00..116.00 rows=8000 width=8) (actual time=0.012..10.277 rows=8000 loops=1)
 Planning time: 0.036 ms
 Execution time: 18.758 ms
(3 行记录)

本地数据库本地执行语句:
music=> explain analyze select * from manid;
                                                  QUERY PLAN                                                   
---------------------------------------------------------------------------------------------------------------
 Foreign Scan on manid  (cost=100.00..186.80 rows=2560 width=8) (actual time=14.445..60.194 rows=8000 loops=1)
 Planning time: 12.400 ms
 Execution time: 64.936 ms
(3 rows)

看起来差别还是比较明显的,更别提用到量大且复杂的生产环境中了。如果是该查询用的不频繁并且查询的量不大不复杂,客户也可以忍受响应速度,那这样就OK。

如果对响应速度有相对较高的要求,则需要使用另一种武器:物化视图。

物化视图可以理解为是对目标表格的一个副本,可能是一模一样的,也可能是经过筛选的。本次咱们为了改善性能,简单的创建一个跟远程数据库表格一模一样的物化视图:

在本地数据库创建物化视图:
物化视图名称为:mv_manid,通过该视图保存manid表能查到的数据的实体:
music=> create materialized view mv_manid as select * from manid;
SELECT 8000   ---数据条目跟刚才一样为8千条

查看一下物化视图的性能如何:

music=> explain analyze select * from mv_manid;
                                                QUERY PLAN                                                
----------------------------------------------------------------------------------------------------------
 Seq Scan on mv_manid  (cost=0.00..113.04 rows=7704 width=8) (actual time=0.024..1.823 rows=8000 loops=1)
 Planning time: 0.254 ms
 Execution time: 2.864 ms
(3 rows)

music=> explain analyze select count(*) from mv_manid;
                                                   QUERY PLAN                                                   
----------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=132.30..132.31 rows=1 width=0) (actual time=1.336..1.336 rows=1 loops=1)
   ->  Seq Scan on mv_manid  (cost=0.00..113.04 rows=7704 width=0) (actual time=0.010..0.738 rows=8000 loops=1)
 Planning time: 0.032 ms
 Execution time: 1.363 ms
(4 rows)

比manid的强不少吧?~~~

物化视图需要对表进行刷新才能同步远程表的数据:

在远程数据库表里插入新数据:
musician=> insert into man select * from generate_series(8001,10000);
INSERT 0 2000
musician=> select count(*) from man;
 count 
-------
 10000
(1 行记录)

本地库查询发现还是8千条数据:
music=> select count(*) from mv_manid;
 count 
-------
  8000
(1 row)

刷新一下本地的物化视图即可看到新进来的数据:
music=> refresh materialized view mv_manid;
REFRESH MATERIALIZED VIEW
music=> select count(*) from mv_manid;
 count 
-------
 10000
(1 row)

OK!~

阅读(2441) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册