注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

PostgreSQL 中文网

 
 
 

日志

 
 

mysql_fdw 实践: 导致宕机一例  

2012-02-20 20:52:11|  分类: PG案例分析 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

 

        这两天突然想起前段时间 MySQL 转 PG 项目实施过程中曾经导致过 PostgreSQL 宕机的情况,
 但很快 PG 自己恢复,下面来模拟下当时的情形。
 

--1 环境信息
PostgreSQL: 9.1.1
MySQL :    5.5.15


--2 创建测试表 (on MySQL )
mysql> create table test_1 (id integer ,name varchar(32));
Query OK, 0 rows affected (0.02 sec)

mysql> desc test_1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(32) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into test_1 values (1,'a');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test_1 values (2,'b');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test_1 values (3,'c');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test_1;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
+------+------+
3 rows in set (0.00 sec)

   备注:为了演示,创建一张名为 test_1 的表,两个字段,并插入三条数据。


--3 查看 foreign server  ( On PostgreSQL)
adsystem=# \des
           List of foreign servers
   Name    |  Owner   | Foreign-data wrapper
-----------+----------+----------------------
 mysql_svr | adsystem | mysql_fdw
(1 row)


--4 创建外部表 ( On PostgreSQL)
adsystem=# create foreign table ft_test_1 (id integer) server mysql_svr OPTIONS (database '51mrp_adsystem', table 'test_1');
CREATE FOREIGN TABLE

   备注:在 PG 库中创建外部表时,故意只创建一张字段,与 MySQL 中的表 test_1 表结构不一样。
  

--5 查询外部表 ( On PostgreSQL)
adsystem=# select * from ft_test_1;
The connection to the server was lost. Attempting reset: WARNING:  terminating connection because of crash of another server process
DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
HINT:  In a moment you should be able to reconnect to the database and repeat your command.
Failed.

   备注:当查询外部表 ft_test_1 时, PostgreSQL 服务出现连接中断的情况,当前 session 中断,
              查看数据库服务器日志如下。


--6 csv 日志信息 ( On PostgreSQL)
2012-02-20 20:18:21.807 CST,,,13434,,4f41ebff.347a,2,,2012-02-20 14:45:19 CST,,0,LOG,00000,"server process (PID 20995) was terminated by signal 11: Segmentation fault",,,,,,,,,""
2012-02-20 20:18:21.807 CST,,,13434,,4f41ebff.347a,3,,2012-02-20 14:45:19 CST,,0,LOG,00000,"terminating any other active server processes",,,,,,,,,""
2012-02-20 20:18:21.810 CST,,,13439,,4f41ebff.347f,2,,2012-02-20 14:45:19 CST,1/0,0,WARNING,57P02,"terminating connection because of crash of another server process","The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.","In a moment you should be able to reconnect to the database and repeat your command.",,,,,,,""
2012-02-20 20:18:21.813 CST,"adsystem","adsystem",21086,"[local]",4f423a0d.525e,1,"authentication",2012-02-20 20:18:21 CST,2/0,0,WARNING,57P02,"terminating connection because of crash of another server process","The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.","In a moment you should be able to reconnect to the database and repeat your command.",,,,,,,""
2012-02-20 20:18:21.815 CST,,,13434,,4f41ebff.347a,4,,2012-02-20 14:45:19 CST,,0,LOG,00000,"all server processes terminated; reinitializing",,,,,,,,,""
2012-02-20 20:18:21.857 CST,,,21087,,4f423a0d.525f,1,,2012-02-20 20:18:21 CST,,0,LOG,00000,"database system was interrupted; last known up at 2012-02-20 17:45:19 CST",,,,,,,,,""
2012-02-20 20:18:21.858 CST,,,21087,,4f423a0d.525f,2,,2012-02-20 20:18:21 CST,,0,LOG,00000,"database system was not properly shut down; automatic recovery in progress",,,,,,,,,""
2012-02-20 20:18:21.872 CST,,,21087,,4f423a0d.525f,3,,2012-02-20 20:18:21 CST,,0,LOG,00000,"consistent recovery state reached at 2/571E7284",,,,,,,,,""
2012-02-20 20:18:21.872 CST,,,21087,,4f423a0d.525f,4,,2012-02-20 20:18:21 CST,,0,LOG,00000,"redo starts at 2/571E7284",,,,,,,,,""
2012-02-20 20:18:21.872 CST,,,21087,,4f423a0d.525f,5,,2012-02-20 20:18:21 CST,,0,LOG,00000,"record with zero length at 2/571E871C",,,,,,,,,""
2012-02-20 20:18:21.872 CST,,,21087,,4f423a0d.525f,6,,2012-02-20 20:18:21 CST,,0,LOG,00000,"redo done at 2/571E7284",,,,,,,,,""
2012-02-20 20:18:21.887 CST,,,13434,,4f41ebff.347a,5,,2012-02-20 14:45:19 CST,,0,LOG,00000,"database system is ready to accept connections",,,,,,,,,""
2012-02-20 20:18:21.889 CST,,,21090,,4f423a0d.5262,1,,2012-02-20 20:18:21 CST,,0,LOG,00000,"autovacuum launcher started",,,,,,,,,""
                            
    备注:PostgreSQL 出现短时间的中断,但又很快恢复正常,从日志文件来看,数据库首先是
              异常关闭,然后自动恢复正常,整个处理过程在 80 ms 左右,下面看下 PostgreSQL
              正常的创建外部表的方法。 

                            
--7 正确的方法 ( On PostgreSQL)
adsystem=# drop foreign table ft_test_1;
DROP FOREIGN TABLE

adsystem=#  create foreign table ft_test_1 (id integer, name varchar(32)) server mysql_svr OPTIONS (database '51mrp_adsystem', table 'test_1');
CREATE FOREIGN TABLE

adsystem=# select * from ft_test_1;
 id | name
----+------
  1 | a
  2 | b
  3 | c
(3 rows)            
                                  
     备注:当外部表和源 MySQL 表的表结构一致时,上述故障不再发生,查询正常。      
   

--8 总结
   1  在使用 mysql_fdw 迁移 MySQL 库数据到 PG 时,外部表的结构和 MySQL 的表结构必需一致;
       这个一致包括三方面:
      1) 字段类型一致;
      2) 字段总数一样,否则查询外部表时可能导致 PG 宕机;
      3) 字段顺序一致,否则数据可能出现问题。
       
   2  在做 MySQL 转 PG项目时,外部表创建脚本务必需要仔细检查,并在测试环境全面测试。   

                                                                               

  评论这张
 
阅读(26874)| 评论(3)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2016