logo标签列表

如何将数据库从Postgres迁移到MySQL？

mysqlpostgresqlmigration
11
我想将Postgres数据库中的一些表转换为MySQL。理想情况下，我希望创建一个脚本来将PG中的表转换为MySQL，但是如果我们能找到非脚本方式使其成为可能，我会很高兴。
首先，我已经阅读了这里发布的类似问题：从Postgres迁移到MySQL 我尝试了那里提出的两种解决方案，但它们没有起作用。
重要的是说我想迁移结构和数据。 但首先让我们看看Postgres数据库的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）第一种方法，使用pG2Mysql： http://www.lightbox.ca/pg2mysql.php 简单快速（理论上）：不幸的是，在解析时会出现几个错误：

这里是检索到的文档：

# 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");

当我使用任何数据库管理器将此文件导入到数据库中时，我仅收到错误消息：

第一个错误

由于某种原因，解析器将这些符号之间的字符标记为''。 仔细观察，有时列名在 '' 中，而其他时候不在其中，为什么？

我删除了围绕char的符号，然后重复执行相同的导入操作，但又出现了另一个错误：

第二个错误

类型oid？那是什么？读了一下看到它是用于主键的类型。

2）第二种方法，我将尝试使用Mysql Workbench：

我在Ubuntu中安装了这个功能，并试用了一下。有一个迁移数据库的选项，非常好！

1.- 我输入源并测试连接：

源连接测试

2.- 然后输入目标并测试连接：

目标连接测试

3.- 下一个屏幕，一切正常：

一切正常

4.- 现在我被要求选择架构，我选择默认选项：

选择架构

5.- 我收到以下错误消息，在名为“Reverse engineer selected schemata”的位置失败。

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.- 如果我尝试使用其他类型的模式（见POINT 4.-），程序会突然关闭：

Exit(-1)

以上就是所有内容，我已经用Mysql Workbench以多种方式尝试过，看起来很好，但不幸的是什么都没用。

有任何想法吗？请记住，在本帖开头提供了postgres SQL，因此任何人均可尝试迁移。

5
这是一个不错的故事，但解决方案已经得到了很好的确认：先进行模式转储，然后根据每个表进行CSV转储。手动转换模式并加载到MySQL中，然后将数据导入每个表。如果您不喜欢这种方法，可以使用ETL工具，例如Pentaho Kettle或Talend Studio来进行数据迁移。也存在模式转换工具，但通常需要收费。绝对保证行不通的是仅转储数据库，然后尝试将其加载到MySQL中。 - Craig Ringer
1
是的，我只是想避免手动进行模式转换。正如Richard Huxton所评论的那样，我真的不太了解PostgreSQL和MySQL的深层知识，无法正确地完成它。但是说实话，我认为我别无选择，只能尝试一下。 - Jesus
无法回答，因为此问题已关闭，但您应该先学习SQL。如果您不想学习，可以尝试其他自动工具之一：https://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL#MySQL 或者尝试Pentaho Kettle。 - Chloe
1个回答
2

很抱歉，这对你来说可能会有些困难。

  1. 您似乎不理解PostgreSQL
  2. 您似乎不理解MySQL
  3. 您似乎不理解数据库模式

如果这是您的整个模式，只需将其复制到您选择的文本编辑器中并手动更新即可。

我无法说出“oid”列正在用于什么，但与主键无关（也许是大型对象或“blob”？）。

引号仅在您使用混合大小写标识符LikeThis时使用。默认情况下，SQL不区分大小写。

Mysql没有真正的“boolean”类型，您可能需要使用bit或tinyint。

模式中的nextval()调用用于生成唯一ID。这是PostgreSQL的内容，在MySQL中不受支持 - 您可能需要一个auto_increment修改器。

通过这些提示和pg2mysql输出，您应该能够入门了。

谢谢，我会尝试解析Schema并将数据以CSV格式从一个数据库传递到另一个数据库，以便使它可脚本化。我无法告诉你为什么在那里使用oid。我没有设计数据库模式，但我担心设计人员也不知道 :)感谢您的引用和布尔提示！ - Jesus
1
@Jesus "oid"是旧版（WITH OIDS）PostgreSQL表中的内部列。它仍在系统目录中使用，但通常不会用于其他任何操作。您很少需要使用它，并且在进行数据迁移时通常可以省略。 "oid"代表"对象标识符"。有关所有详细信息，请参阅PostgreSQL文档。 - Craig Ringer
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接