mysql - 将 postgres 表与 mysql 表链接在测试中失败

Question

I am trying to reflect/mirror data from a Postgres table to a MySQL table. I found mysql_fwd and after connection everything to a localhost, I cannot enter data into the postgres FOREIGN TABLE. nor does data entered into MySQL, show up in postgres (backwards test)?? Data from MySQL does not reflect in postgres and if I test the other way (the correct direction) post_sync=# INSERT INTO mysql_sync (user_name, user_id) VALUES ('Joe', 3); ERROR: cannot change foreign table "mysql_sync"

********* ** 构建过程*********** ***

CREATE SERVER mysql_svr
FOREIGN DATA WRAPPER mysql_fdw
OPTIONS (address '127.0.0.1', port '3306');


CREATE FOREIGN TABLE mysql_sync (id float4, user_name text, user_id integer)
SERVER mysql_svr OPTIONS ( database 'postgres_link', query 'SELECT * FROM mysql_sync');


CREATE USER MAPPING FOR mysqlLink SERVER mysql_svr OPTIONS (username 'some_dude', pass '');

********* ********结构****** ******

Foreign table "public.mysql_sync"
Column | Type | Modifiers | FDW Options | Storage | Stats target | Description
-----------+---------+-----------+-------------+----------+--------------+-------------
id | real | | | plain | |
user_name | text | | | extended | |
user_id | integer | | | plain | |
Server: mysql_svr
FDW Options: (database 'postgres_link', query 'SELECT * FROM mysql_sync')
Has OIDs: no

********* *********** mySQL 结构 *************

mysql> desc mysql_sync;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    92
Current database: postgres_link

+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| id        | int(11)     | NO   | PRI | NULL    | auto_increment |
| user_name | varchar(25) | NO   |     | NULL    |                |
| user_id   | varchar(10) | NO   |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

Answer 1

这里有一大堆不同的转换方法 -> http://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL