I want to convert some tables from a Postgres database to MySQL. Ideally I would like to create an script that would convert the tables for PG to MySQL, but I am glad if we are able to figure out how to make it possible in a not-scriptable way.
我想将一些表从Postgres数据库转换为MySQL。理想情况下，我想创建一个将PG的表转换为MySQL的脚本，但是如果我们能够找到如何以无法编写脚本的方式实现它，我很高兴。
First of all, I have read a similar post here: Migrate database from Postgres to MySQL
首先，我在这里阅读了类似的帖子：将数据库从Postgres迁移到MySQL
I have tried both solutions proposed there, but they did't make the trick.
我已经尝试了那里提出的两种解决方案，但他们没有成功。
It's important to say that I want to migrate the structure and the data.
重要的是要说我想要迁移结构和数据。
But first things first, here is the SQL dump of the postgres-database:
但首先，这里是postgres-database的SQL转储：
--
-- PostgreSQL database dump
--
SET statement_timeout = 0; SET client_encoding = 'UTF8'; SET standard_conforming_strings = off; SET check_function_bodies = false; SET client_min_messages = warning; SET escape_string_warning = off;
SET search_path = public, pg_catalog;
SET default_tablespace = '';
SET default_with_oids = false;
--
-- Name: grupos; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
--
CREATE TABLE grupos (
"dirGrupo" text,
valor real,
"flagIntelli" boolean DEFAULT false,
"flagQDS" boolean DEFAULT false,
finalidad text,
"idGrupo" integer DEFAULT nextval('"idGrupo"'::regclass) NOT NULL,
"claseDomo" text,
instalado boolean DEFAULT true NOT NULL,
"codCasa" "char",
"codUnidad" integer,
protocolo text NOT NULL,
escritura boolean,
dispositivo text,
"tipoDatos" oid,
"coordX" integer DEFAULT (-1),
"coordY" integer DEFAULT (-1),
mapa text,
"reglasAsociadas" text );
ALTER TABLE public.grupos OWNER TO postgres;
--
-- Data for Name: grupos; Type: TABLE DATA; Schema: public; Owner: postgres
--
INSERT INTO grupos VALUES (NULL, 0, false, false, 'sensor', 10, 'LightSensor', true, 'E', 1, 'x10', false, 'SensorLum1', 7, -1, -1, NULL, NULL); INSERT INTO grupos VALUES (NULL, 0, false, false, 'luz', 11, 'SimpleLamp', true, 'K', 1, 'x10', true, 'Luz4', 1, -1, -1, NULL, NULL); INSERT INTO grupos VALUES (NULL, 0, false, false, 'Radiador de la sala', 298, 'Heater', true, 'B', 1, 'x10', true, 'RadiadorX10', 1, 163, 168, 'Sala Quercus', '108;111;115;117;119;123;127;131;134;136;138;140;144;148;150;152;155;157;159;162;166;169;172;176;179;182;185;188;190;193;196;199;201;205;209;214;218;222;226;230;234;238;250;254;260;264;277;278;279;280;284;293;297;301;305;309;313;315;315;315;315;315;315;315;315;315;315;315;315;315;315;315;315;'); INSERT INTO grupos VALUES ('0/0/5', 1, false, false, 'Interruptor', 213, 'Button', true, NULL, NULL, 'knx', true, 'Interruptor', 1, 301, 326, 'Plano Casa', NULL); INSERT INTO grupos VALUES ('0/0/4', 0, false, false, 'Enchufe4', 316, 'Switch', true, NULL, NULL, 'knx', true, 'Enchufe4', 1, 303, 133, 'Plano Casa', '268;272;276;'); INSERT INTO grupos VALUES ('0/0/2', 0, false, false, 'Enchufe2', 210, 'Switch', true, NULL, NULL, 'knx', true, 'Enchufe2', 1, 141, 322, 'Plano Casa', NULL); INSERT INTO grupos VALUES ('0/0/1', 0, false, false, 'LuzFlexo', 217, 'SimpleLamp', true, NULL, NULL, 'knx', true, 'LuzFlexo', 1, 80, 45, 'Plano Casa', '267;271;275;'); INSERT INTO grupos VALUES ('0/0/3', 1, false, false, 'Router', 221, 'Router', true, NULL, NULL, 'knx', true, 'Router1', 1, 467, 439, 'Plano Casa', NULL); INSERT INTO grupos VALUES ('0/0/6', 21.68, false, false, 'SensorTemperatura', 237, 'TemperatureSensor', true, NULL, NULL, 'knx', false, 'SensorTemperatura', 2, 146, 436, 'Plano Casa', NULL); INSERT INTO grupos VALUES (NULL, 0, false, false, 'SensorX10', 219, 'MotionSensor', true, 'A', 1, 'x10', false, 'SensorX10', 6, 362, 11, 'Plano Casa', '335;336;'); INSERT INTO grupos VALUES ('1/1/5', 1, false, false, '', 12, 'MotionSensor', true, NULL, NULL, 'knx', false, 'SensorPresencia', 6, -1, -1, NULL, NULL); INSERT INTO grupos VALUES ('1/1/2', 50, false, true, 'Luz Intervalo', 248, 'DimmableLight', true, NULL, NULL, 'knx', true, 'LuzHInt', 3, 97, 276, 'Plano Casa', '265;269;273;338;'); INSERT INTO grupos VALUES ('1/1/1', 0, false, false, 'LuzHBin', 215, 'SimpleLamp', true, NULL, NULL, 'knx', true, 'LuzHBin', 1, 357, 189, 'Plano Casa', '266;270;274;'); INSERT INTO grupos VALUES (NULL, 1, false, true, 'Encender la luz del flexo', 291, 'SimpleLamp', true, 'C', 1, 'x10', true, 'BombillaX10', 1, 272, 130, 'Sala Quercus', '107;109;110;112;113;114;116;118;120;121;122;124;125;126;128;129;130;132;133;135;137;139;141;142;143;145;146;147;149;151;153;154;156;158;160;161;163;164;165;167;168;170;171;173;174;175;177;178;180;181;183;184;186;187;189;191;192;194;195;197;198;200;202;203;204;206;207;208;210;211;212;213;215;216;217;219;220;221;223;224;225;227;228;229;231;232;233;235;236;237;239;240;249;251;252;253;255;256;257;258;259;261;262;263;281;282;283;290;291;292;294;295;296;298;299;300;302;303;304;306;307;308;310;311;312;314;316;317;314;316;317;314;316;317;314;316;317;314;316;317;314;316;317;314;316;317;314;316;317;314;316;317;314;316;317;314;316;317;314;316;317;314;316;317;314;316;317;314;316;317;314;316;317;335;336;');
--
-- Name: grupos_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
--
ALTER TABLE ONLY grupos
ADD CONSTRAINT grupos_pkey PRIMARY KEY ("idGrupo");
--
-- Name: grupos_tipoDatos_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--
ALTER TABLE ONLY grupos
ADD CONSTRAINT "grupos_tipoDatos_fkey" FOREIGN KEY ("tipoDatos") REFERENCES "TiposDatos"(id);
--
-- PostgreSQL database dump complete
--
1) First approach, Using pG2Mysql: http://www.lightbox.ca/pg2mysql.php Simple and fast (in theory): Unfortunately it has several errors when parsing:
1）第一种方法，使用pG2Mysql：http：//www.lightbox.ca/pg2mysql.php简单快速（理论上）：不幸的是，它在解析时有几个错误：
Here is the document retrieved:
这是检索到的文件：
# Converted with pg2mysql-1.9
# Converted on Mon, 22 Apr 2013 14:12:28 -0400
# Lightbox Technologies Inc. http://www.lightbox.ca
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; SET time_zone="+00:00";
CREATE TABLE grupos (
`dirGrupo` text,
valor real,
`flagIntelli` bool DEFAULT 0,
`flagQDS` bool DEFAULT 0,
finalidad text,
`idGrupo` int(11) auto_increment NOT NULL,
`claseDomo` text,
instalado bool DEFAULT 1 NOT NULL,
`codCasa` `char`,
`codUnidad` int(11),
protocolo text NOT NULL,
escritura bool,
dispositivo text,
`tipoDatos` oid,
`coordX` int(11) DEFAULT -1 ,
`coordY` int(11) DEFAULT -1 ,
mapa text,
`reglasAsociadas` text , PRIMARY KEY(`idGrupo`) ) TYPE=MyISAM;
INSERT INTO grupos VALUES (NULL, 0, false, false, 'sensor', 10, 'LightSensor', true, 'E', 1, 'x10', false, 'SensorLum1', 7, -1, -1, NULL, NULL); INSERT INTO grupos VALUES (NULL, 0, false, false, 'luz', 11, 'SimpleLamp', true, 'K', 1, 'x10', true, 'Luz4', 1, -1, -1, NULL, NULL); INSERT INTO grupos VALUES (NULL, 0, false, false, 'Radiador de la sala', 298, 'Heater', true, 'B', 1, 'x10', true, 'RadiadorX10', 1, 163, 168, 'Sala Quercus', '108;111;115;117;119;123;127;131;134;136;138;140;144;148;150;152;155;157;159;162;166;169;172;176;179;182;185;188;190;193;196;199;201;205;209;214;218;222;226;230;234;238;250;254;260;264;277;278;279;280;284;293;297;301;305;309;313;315;315;315;315;315;315;315;315;315;315;315;315;315;315;315;315;'); INSERT INTO grupos VALUES ('0/0/5', 1, false, false, 'Interruptor', 213, 'Button', true, NULL, NULL, 'knx', true, 'Interruptor', 1, 301, 326, 'Plano Casa', NULL); INSERT INTO grupos VALUES ('0/0/4', 0, false, false, 'Enchufe4', 316, 'Switch', true, NULL, NULL, 'knx', true, 'Enchufe4', 1, 303, 133, 'Plano Casa', '268;272;276;'); INSERT INTO grupos VALUES ('0/0/2', 0, false, false, 'Enchufe2', 210, 'Switch', true, NULL, NULL, 'knx', true, 'Enchufe2', 1, 141, 322, 'Plano Casa', NULL); INSERT INTO grupos VALUES ('0/0/1', 0, false, false, 'LuzFlexo', 217, 'SimpleLamp', true, NULL, NULL, 'knx', true, 'LuzFlexo', 1, 80, 45, 'Plano Casa', '267;271;275;'); INSERT INTO grupos VALUES ('0/0/3', 1, false, false, 'Router', 221, 'Router', true, NULL, NULL, 'knx', true, 'Router1', 1, 467, 439, 'Plano Casa', NULL); INSERT INTO grupos VALUES ('0/0/6', 21.68, false, false, 'SensorTemperatura', 237, 'TemperatureSensor', true, NULL, NULL, 'knx', false, 'SensorTemperatura', 2, 146, 436, 'Plano Casa', NULL); INSERT INTO grupos VALUES (NULL, 0, false, false, 'SensorX10', 219, 'MotionSensor', true, 'A', 1, 'x10', false, 'SensorX10', 6, 362, 11, 'Plano Casa', '335;336;'); INSERT INTO grupos VALUES ('1/1/5', 1, false, false, '', 12, 'MotionSensor', true, NULL, NULL, 'knx', false, 'SensorPresencia', 6, -1, -1, NULL, NULL); INSERT INTO grupos VALUES ('1/1/2', 50, false, true, 'Luz Intervalo', 248, 'DimmableLight', true, NULL, NULL, 'knx', true, 'LuzHInt', 3, 97, 276, 'Plano Casa', '265;269;273;338;'); INSERT INTO grupos VALUES ('1/1/1', 0, false, false, 'LuzHBin', 215, 'SimpleLamp', true, NULL, NULL, 'knx', true, 'LuzHBin', 1, 357, 189, 'Plano Casa', '266;270;274;'); INSERT INTO grupos VALUES (NULL, 1, false, true, 'Encender la luz del flexo', 291, 'SimpleLamp', true, 'C', 1, 'x10', true, 'BombillaX10', 1, 272, 130, 'Sala Quercus', '107;109;110;112;113;114;116;118;120;121;122;124;125;126;128;129;130;132;133;135;137;139;141;142;143;145;146;147;149;151;153;154;156;158;160;161;163;164;165;167;168;170;171;173;174;175;177;178;180;181;183;184;186;187;189;191;192;194;195;197;198;200;202;203;204;206;207;208;210;211;212;213;215;216;217;219;220;221;223;224;225;227;228;229;231;232;233;235;236;237;239;240;249;251;252;253;255;256;257;258;259;261;262;263;281;282;283;290;291;292;294;295;296;298;299;300;302;303;304;306;307;308;310;311;312;314;316;317;314;316;317;314;316;317;314;316;317;314;316;317;314;316;317;314;316;317;314;316;317;314;316;317;314;316;317;314;316;317;314;316;317;314;316;317;314;316;317;314;316;317;314;316;317;335;336;'); ALTER TABLE grupos
ADD CONSTRAINT grupos_pkey PRIMARY KEY ("idGrupo");
When I import this file to the database using any Database Manager I recieve nothing but errors:
当我使用任何数据库管理器将此文件导入数据库时，我只收到错误：
For some reason the parser marks chars between those symbols: ''. If we look carefully, sometimes the names of the columns are between '' and other times not, why?
由于某种原因，解析器标记这些符号之间的字符：''。如果我们仔细观察，有时列的名称介于“和其他时间之间”，为什么？
I delete those symbols around char and proceed to do the same import again, but again another error faces up:
我删除了char周围的那些符号，然后再次进行相同的导入，但是又出现了另一个错误：
Type oid? what is that? Reading I see that is a type used for primary keys.
输入oid？那是什么？阅读我看到这是一种用于主键的类型。
2) Second approach, I will try with Mysql Workbench:
2）第二种方法，我将尝试使用Mysql Workbench：
I installed this feature in Ubuntu and I gave it a try. There is an option to migrate databases, great!
我在Ubuntu中安装了这个功能，我尝试了一下。有一个迁移数据库的选项，太棒了！
1.- I introduce the source and test the connection:
1.-我介绍了源代码并测试了连接：
2.- Then the Destination and test the connection:
2.-然后是Destination并测试连接：
3.- The next screen, everything is ok:
3.-下一个屏幕，一切正常：
4.- Now I am asked to select the Schema, I select the default option:
4.-现在我被要求选择Schema，我选择默认选项：
5.- And I receive the following error, failing in the point named, "Reverse engineer selected schemata".
5.-我收到以下错误，在名为“逆向工程选择的模式”的点上失败。
Starting...
Connect to source DBMS...
- Connecting...
Connecting to postgresql@DRIVER=Postgresql;SERVER=158.49.245.68;PORT=5432...
Opening ODBC connection to DRIVER=Postgresql;SERVER=158.49.245.68;PORT=5432;DATABASE=QDSDatabase;UID=postgres...
Connected
Connect to source DBMS done
Reverse engineer selected schemata....
Reverse engineering public from QDSDatabase
- Reverse engineering catalog information
Traceback (most recent call last):
File "/usr/lib/mysql-workbench/modules/db_postgresql_re_grt.py", line 335, in reverseEngineer
return PostgresqlReverseEngineering.reverseEngineer(connection, catalog_name, schemata_list, context)
File "/usr/lib/mysql-workbench/modules/db_generic_re_grt.py", line 207, in reverseEngineer
catalog = cls.reverseEngineerCatalog(connection, catalog_name)
File "/usr/lib/mysql-workbench/modules/db_generic_re_grt.py", line 367, in reverseEngineerCatalog
cls.reverseEngineerSequences(connection, schema)
File "/usr/lib/mysql-workbench/modules/db_postgresql_re_grt.py", line 76, in reverseEngineerSequences
min_value, max_value, start_value, increment_by, last_value, is_cycled, ncache = cls.execute_query(connection, seq_details_query % (schema.name, seq_name)).fetchone()
File "/usr/lib/mysql-workbench/modules/db_generic_re_grt.py", line 56, in execute_query
return cls.get_connection(connection_object).cursor().execute(query, *args, **kwargs)
pyodbc.ProgrammingError: ('42P01', '[42P01] ERROR: no existe la relaci\xc3\xb3n \xc2\xabpublic.idtipodatos\xc2\xbb;\nError while executing the query (7) (SQLExecDirectW)')
Traceback (most recent call last):
File "/usr/share/mysql-workbench/libraries/workbench/wizard_progress_page_widget.py", line 192, in thread_work
self.func()
File "/usr/lib/mysql-workbench/modules/migration_schema_selection.py", line 160, in task_reveng
self.main.plan.migrationSource.reverseEngineer()
File "/usr/lib/mysql-workbench/modules/migration.py", line 332, in reverseEngineer
self.state.sourceCatalog = self._rev_eng_module.reverseEngineer(self.connection, self.selectedCatalogName, self.selectedSchemataNames, self.state.applicationData)
SystemError: ProgrammingError("('42P01', '[42P01] ERROR: no existe la relaci\xc3\xb3n \xc2\xabpublic.idtipodatos\xc2\xbb;\nError while executing the query (7) (SQLExecDirectW)')"): error calling Python module function DbPostgresqlRE.reverseEngineer
ERROR: Reverse engineer selected schemata: ProgrammingError("('42P01', '[42P01] ERROR: no existe la relaci\xc3\xb3n \xc2\xabpublic.idtipodatos\xc2\xbb;\nError while executing the query (7) (SQLExecDirectW)')"): error calling Python module function DbPostgresqlRE.reverseEngineer
Failed
6.- If I try with other type of schemas (POINT 4.-) the program closes suddenly :
6.-如果我尝试使用其他类型的模式（POINT 4.-），程序会突然关闭：
That's all, I have tried this in several ways with Mysql Workbench, which seemed great, but unfortunately nothing worked.
就是这样，我用Mysql Workbench在几个方面尝试了这个，看起来很棒，但遗憾的是没有用。
Any ideas? Remember that the postgres SQL is in the beginning of the post, so anyone can try the migration.
有任何想法吗？请记住，postgres SQL位于帖子的开头，因此任何人都可以尝试迁移。
1 个解决方案
#1
1
This is going to be difficult for you I'm afraid.
恐怕这对你来说很难。
- You don't seem to understand PostgreSQL
- 你好像不懂PostgreSQL
- You don't seem to understand MySQL
- 你好像不懂MySQL
- You don't seem to understand your database schema
- 您似乎不了解您的数据库架构
If that is your entire schema, just copy it into your text editor of choice and manually update it.
如果这是您的整个架构，只需将其复制到您选择的文本编辑器中并手动更新它。
What the "oid" column is being used for I couldn't say, but it's nothing to do with a primary key (perhaps a large object or "blob"?).
“oid”列用于什么我不能说，但它与主键无关（可能是一个大对象或“blob”？）。
The quoting is used only where you've used mixed-case identifies LikeThis. By default SQL is case-insensitive.
引用仅在您使用混合大小写标识LikeThis的情况下使用。默认情况下，SQL不区分大小写。
Mysql doesn't have a real "boolean" type, you'll probably want to use bit or tinyint.
Mysql没有真正的“布尔”类型，你可能想要使用bit或tinyint。
The nextval() call in the schema is to generate unique ids. This is a PostgreSQL thing and not supported in MySQL - you'll probably want an auto_increment modifier.
模式中的nextval（）调用是生成唯一ID。这是一个PostgreSQL的东西，MySQL不支持 - 你可能想要一个auto_increment修饰符。
Between those tips and the pg2mysql output that should get you started.
在那些提示和pg2mysql输出之间，应该让你开始。
#1
1
This is going to be difficult for you I'm afraid.
恐怕这对你来说很难。
- You don't seem to understand PostgreSQL
- 你好像不懂PostgreSQL
- You don't seem to understand MySQL
- 你好像不懂MySQL
- You don't seem to understand your database schema
- 您似乎不了解您的数据库架构
If that is your entire schema, just copy it into your text editor of choice and manually update it.
如果这是您的整个架构，只需将其复制到您选择的文本编辑器中并手动更新它。
What the "oid" column is being used for I couldn't say, but it's nothing to do with a primary key (perhaps a large object or "blob"?).
“oid”列用于什么我不能说，但它与主键无关（可能是一个大对象或“blob”？）。
The quoting is used only where you've used mixed-case identifies LikeThis. By default SQL is case-insensitive.
引用仅在您使用混合大小写标识LikeThis的情况下使用。默认情况下，SQL不区分大小写。
Mysql doesn't have a real "boolean" type, you'll probably want to use bit or tinyint.
Mysql没有真正的“布尔”类型，你可能想要使用bit或tinyint。
The nextval() call in the schema is to generate unique ids. This is a PostgreSQL thing and not supported in MySQL - you'll probably want an auto_increment modifier.
模式中的nextval（）调用是生成唯一ID。这是一个PostgreSQL的东西，MySQL不支持 - 你可能想要一个auto_increment修饰符。
Between those tips and the pg2mysql output that should get you started.
在那些提示和pg2mysql输出之间，应该让你开始。