mysql vacuum_MySQL_Fdw 实践: 宕机一例

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

1 环境信息

PostgreSQL: 9.1.1

MySQL : 5.5.15

2 创建测试表 (on MySQL )

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27mysql> 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)

1

2

3

4

5

6adsystem=# \des

List of foreign servers

Name | Owner | Foreign-data wrapper

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

mysql_svr | adsystem | mysql_fdw

(1 row)

4 创建外部表 ( On PostgreSQL)

1

2adsystem=# 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)

1

2

3

4

5adsystem=# select * from ft_test_1;

Theconnectionto theserverwas lost. Attempting reset: WARNING: terminatingconnectionbecause of crash of anotherserverprocess

DETAIL: The postmaster has commanded thisserverprocess to roll back the current transaction and exit, because anotherserverprocess 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)

1

2

3

4

5

6

7

8

9

10

11

12

132012-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)

1

2

3

4

5

6

7

8

9

10

11

12

13adsystem=# 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 总结

在使用 mysql_fdw 迁移 MySQL 库数据到 PG 时，外部表的结构和 MySQL 的表结构必需一致；这个一致包括三方面： 字段类型一致;

字段总数一样，否则查询外部表时可能导致 PG 宕机;

字段顺序一致，否则数据可能出现问题。

在做 MySQL 转 PG项目时，外部表创建脚本务必需要仔细检查，并在测试环境全面测试。