title: How to migrate database from Postgres to MySQL?
body: <p>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.</p>
  
  <p>First of all, I have read a similar post here: <a href="https://stackoverflow.com/questions/5909223/migrate-database-from-postgres-to-mysql">Migrate database from Postgres to MySQL</a></p>
  
  <p>I have tried both solutions proposed there, but they did't make the trick.</p>
  
  <p>It's important to say that <strong>I want to migrate the structure and the data.</strong></p>
  
  <p>But first things first, here is the SQL dump of the postgres-database:</p>
  
  <pre><code>--
  -- 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
  --
  </code></pre>
  
  <p><strong>1) First approach, Using pG2Mysql</strong>: <a href="http://www.lightbox.ca/pg2mysql.php" rel="nofollow noreferrer">http://www.lightbox.ca/pg2mysql.php</a>
  Simple and fast (in theory): Unfortunately it has several errors when parsing:</p>
  
  <p>Here is the document retrieved:</p>
  
  <pre><code># 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");
  </code></pre>
  
  <p>When I import this file to the database using any Database Manager I recieve nothing but errors:</p>
  
  <p><img src="https://i.stack.imgur.com/nbK0T.png" alt="The first error"></p>
  
  <p>For some reason the parser marks <em>chars</em> between those symbols: ''.
  If we look carefully, sometimes the names of the columns are between  ''  and other times not, why?</p>
  
  <p>I delete those symbols around <em>char</em> and proceed to do the same import again, but again another error faces up:</p>
  
  <p><img src="https://i.stack.imgur.com/SWI8t.png" alt="The second error"></p>
  
  <p>Type oid? what is that? Reading I see that is a type used for primary keys.</p>
  
  <p><strong>2) Second approach, I will try with <a href="http://www.mysql.com/downloads/workbench/" rel="nofollow noreferrer">Mysql Workbench</a>:</strong> </p>
  
  <p>I installed this feature in Ubuntu and I gave it a try. There is an option to migrate databases, great!</p>
  
  <p>1.- I introduce the source and test the connection:</p>
  
  <p><img src="https://i.stack.imgur.com/uSNsz.png" alt="The source connection test"></p>
  
  <p>2.- Then the Destination and test the connection:</p>
  
  <p><img src="https://i.stack.imgur.com/rTWDZ.png" alt="The destination connection test"></p>
  
  <p>3.- The next screen, everything is ok:</p>
  
  <p><img src="https://i.stack.imgur.com/3x1ja.png" alt="Things are working"></p>
  
  <p>4.- Now I am asked to select the Schema, I select the default option:</p>
  
  <p><img src="https://i.stack.imgur.com/OMOrq.png" alt="Select the schema"></p>
  
  <p>5.- And I receive the following error, failing in the point named, "Reverse engineer selected schemata".</p>
  
  <pre><code>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
  </code></pre>
  
  <p>6.- If I try with other type of schemas (POINT 4.-) the program closes suddenly :</p>
  
  <p><img src="https://i.stack.imgur.com/dBXf8.png" alt="Exit(-1)"></p>
  
  <p>That's all, I have tried this in several ways with Mysql Workbench, which seemed great, but unfortunately nothing worked.</p>
  
  <p>Any ideas? Remember that the postgres SQL is in the beginning of the post, so anyone can try the migration.</p>