Migrar una base de datos MySQL a PostgresQL

Publicado en 7 Ene 2018 el 8:37 am.

Cualquiera sea la razón, migrar de MySQL a PostgreSQL no es muy complicado si se tienen a la mano algunos scripts que hagan el trabajo sucio por nosotros, pero aun así hay que hacer modificaciones manuales durante la migración.

El problema radica en la diferencia que hay en la sintaxis del lenguaje SQL de ambos, se parecen mucho pero no son iguales, yo creo que lo mas complicado es migrar los campos que están señalados como AUTO_INCREMENT en MySQL, en PostgreSQL no existe AUTO_INCREMENT como atributo de campo, para ello es necesario crear una secuencia y enlazarlo al campo.

Migrar MySQL A PostgreSQL

Manos a la obra!

Paso 1. Generar un respaldo de la ESTRUCTURA de la base de datos MySQL

El primer paso para migrar nuestra base de datos MySQL a PostgreSQL es generar una copia de seguridad mediante mysqldump de la siguiente forma:

mysqldump -u [usuario] -p [base_de_datos] --no-data > [archivo_salida].sql

Donde:

[usuario] el el nombre de usuario con el que accedemos a la base de datos (usualmente root)
[base_de_datos] es el nombre de la base de datos que vamos a migrar (en este caso la base de datos de ejemplo World).
[archivo_salida] es el nombre de nuestro archivo de salida que contendrá los comandos SQL generados por mysqldump
El parámetro -p es para que mysqldump nos pregunte por la contraseña del usuario, si no hay contraseña de base de datos entonces obviar este parametro.
El parámetro –no-data omitirá los datos, por que sólo necesitamos la estructura de las tablas, una vez migrada la estructura seguimos con los datos más adelante.

El comando se vería de la siguiente forma:

mysqldump -u root -p world –no-data > world-mysql.sql

El comando producirá un archivo parecido al siguiente:

-- MySQL dump 10.13  Distrib 5.1.54, for debian-linux-gnu (i686)
--
-- Host: localhost    Database: world
-- ------------------------------------------------------
-- Server version       5.1.54-1ubuntu4
 
--
-- Table structure for table `City`
--
 
DROP TABLE IF EXISTS `City`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `City` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` char(35) NOT NULL DEFAULT '',
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `District` char(20) NOT NULL DEFAULT '',
  `Population` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
 
--
-- Table structure for table `Country`
--
 
DROP TABLE IF EXISTS `Country`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `Country` (
  `Code` char(3) NOT NULL DEFAULT '',
  `Name` char(52) NOT NULL DEFAULT '',
  `Continent` enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL DEFAULT 'Asia',
  `Region` char(26) NOT NULL DEFAULT '',
  `SurfaceArea` float(10,2) NOT NULL DEFAULT '0.00',
  `IndepYear` smallint(6) DEFAULT NULL,
  `Population` int(11) NOT NULL DEFAULT '0',
  `LifeExpectancy` float(3,1) DEFAULT NULL,
  `GNP` float(10,2) DEFAULT NULL,
  `GNPOld` float(10,2) DEFAULT NULL,
  `LocalName` char(45) NOT NULL DEFAULT '',
  `GovernmentForm` char(45) NOT NULL DEFAULT '',
  `HeadOfState` char(60) DEFAULT NULL,
  `Capital` int(11) DEFAULT NULL,
  `Code2` char(2) NOT NULL DEFAULT '',
  PRIMARY KEY (`Code`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
 
--
-- Table structure for table `CountryLanguage`
--
 
DROP TABLE IF EXISTS `CountryLanguage`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `CountryLanguage` (
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `Language` char(30) NOT NULL DEFAULT '',
  `IsOfficial` enum('T','F') NOT NULL DEFAULT 'F',
  `Percentage` float(4,1) NOT NULL DEFAULT '0.0',
  PRIMARY KEY (`CountryCode`,`Language`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
 
-- Dump completed on 2011-09-17 11:36:57

Paso 2. Traducir SQL de MySQL a SQL de PostgresSQL utilizando mysql2pgsql.perl

mysql2pgsql.perl es un script escrito en Perl que «traduce» la sintaxis del código SQL de MySQL a un SQL que pueda entender PostgreSQL, es muy fácil de usar:

perl mysql2pgsql.perl [opciones] sql_formato_mysql.sql sql_formato_postgresql.sql

Para nuestro caso, el comando que ejecutamos es:

perl mysql2pgsql.perl --nodrop world-mysql.sql world-postgresql.sql

Donde: La opción –nodrop hace que el script no incluya los comandos DROP TABLE antes de crear las tablas.

Puedes descargar mysql2pgsql.perl de http://pgfoundry.org/projects/mysql2pgsql/

El archivo world-postgresql.sql generado por el script se verá asi:

--
-- Generated from mysql2pgsql.perl
-- http:// gborg.postgresql.org/project/mysql2psql/
-- (c) 2001 - 2007 Jose M. Duarte, Joseph Speigle
--
 
-- warnings are printed for drop tables if they do not exist
-- please see http:// archives.postgresql.org/pgsql-novice/2004-10/msg00158.php
 
-- ##############################################################
 
-- MySQL dump 10.13  Distrib 5.1.54, for debian-linux-gnu (i686)
--
-- Host: localhost    Database: world
-- ------------------------------------------------------
-- Server version       5.1.54-1ubuntu4
 
 
--
-- Table structure for table City
--
 
DROP SEQUENCE "city_id_seq" CASCADE ;
 
CREATE SEQUENCE "city_id_seq"  START WITH 4080 ;
 
CREATE TABLE  "city" (
   "id" integer DEFAULT nextval('"city_id_seq"') NOT NULL,
   "name"   char(35) NOT NULL DEFAULT '',
   "countrycode"   char(3) NOT NULL DEFAULT '',
   "district"   char(20) NOT NULL DEFAULT '',
   "population"   int NOT NULL DEFAULT '0',
   primary key ("id")
)   ;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
 
--
-- Table structure for table Country
--
 
CREATE TABLE  "country" (
   "code"   char(3) NOT NULL DEFAULT '',
   "name"   char(52) NOT NULL DEFAULT '',
 "continent" varchar CHECK ("continent" IN ( 'Asia','Europe','North America','Africa','Oceania','Antarctica','South America' )) NOT NULL DEFAULT 'Asia',
   "region"   char(26) NOT NULL DEFAULT '',
   "surfacearea"   double precision NOT NULL DEFAULT '0.00',
   "indepyear"   smallint DEFAULT NULL,
   "population"   int NOT NULL DEFAULT '0',
   "lifeexpectancy"   double precision DEFAULT NULL,
   "gnp"   double precision DEFAULT NULL,
   "gnpold"   double precision DEFAULT NULL,
   "localname"   char(45) NOT NULL DEFAULT '',
   "governmentform"   char(45) NOT NULL DEFAULT '',
   "headofstate"   char(60) DEFAULT NULL,
   "capital"   int DEFAULT NULL,
   "code2"   char(2) NOT NULL DEFAULT '',
   primary key ("code")
)  ;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
 
--
-- Table structure for table CountryLanguage
--
 
CREATE TABLE  "countrylanguage" (
   "countrycode"   char(3) NOT NULL DEFAULT '',
   "language"   char(30) NOT NULL DEFAULT '',
 "isofficial" varchar CHECK ("isofficial" IN ( 'T
','F' )) NOT NULL DEFAULT 'F',
   "percentage"   double precision NOT NULL DEFAULT '0.0',
   primary key ("countrycode", "language")
) ;

Notar el cambio en la forma de declarar los campos y la creación de secuencias. Los comentarios en PostgreSQL son solo eso comentarios.

Paso 3: Enviar los datos generados desde MySQL a PostgreSQL

Ahora debemos volcar el contenido del archivo generado hacia postgresql, para ello nos logueamos con el usuario postgres y ejecutamos los comandos necesarios:

$su postgres

createdb world

psql -f world_postgresql.sql -u usuario-db word

Ya casi todo esta listo, ahora solo nos queda migrar los datos. Al igual que en el paso 1, tenemos que sacar un respaldo pero esta vez solamente de los datos sin la creación de la estructura de las tablas, para ellos utilizamos el siguiente comando:

mysqldump -u root -p world --no-create-info --complete-insert --skip-add-locks > world-mysql-data.sql

Donde:

–no-create-info Omite los scripts de creación de tabla (no las necesitamos)
–complete-insert Hace que los comandos INSERT contengan también los nombres de las columnas, PostgreSQL no permite comandos INSERT sin los nombres de las columnas.
–skip-add-locks omite el comando LOCK TABLE … ese comando también es diferente en PostgreSQL.

Como resultado tendremos el archivo world-mysql-data.sql, va a ser algo parecido a esto:

-- MySQL dump 10.13  Distrib 5.1.54, for debian-linux-gnu (i686)
--
-- Host: localhost    Database: world
-- ------------------------------------------------------
-- Server version       5.1.54-1ubuntu4
 
--
-- Dumping data for table `City`
--
 
INSERT INTO `City` (`ID`, `Name`, `CountryCode`, `District`, `Population`)
   VALUES (1,'Kabul','AFG','Kabol',1780000),
          (2,'Qandahar','AFG','Qandahar',237500),
          (3,'Herat','AFG','Herat',186800)
          ...
 
--
-- Dumping data for table `Country`
--
 
 
INSERT INTO `Country` ( `Code`, `Name`, `Continent`, `Region`, `SurfaceArea`, `IndepYear`,
                        `Population`, `LifeExpectancy`, `GNP`, `GNPOld`, `LocalName`, `GovernmentForm`,
                        `HeadOfState`, `Capital`, `Code2`)
VALUES ('ABW','Aruba','North America','Caribbean',193.00,NULL,103000,78.4,828.00,793.00,
           'Aruba','Nonmetropolitan Territory of The Netherlands','Beatrix',129,'AW'),
        ('AFG','Afghanistan','Asia','Southern and Central Asia',652090.00,1919,22720000,
           45.9,5976.00,NULL,'Afganistan/Afqanestan','Islamic Emirate','Mohammad Omar',1,'AF'),
        ('AGO','Angola','Africa','Central Africa',1246700.00,1975,12878000,38.3,6648.00,7984.00,
          'Angola','Republic','José Eduardo dos Santos',56,'AO'),
        ('AIA','Anguilla','North America','Caribbean',96.00,NULL,8000,76.1,63.20,NULL,
          'Anguilla','Dependent Territory of the UK','Elisabeth II',62,'AI'),
        ('ALB','Albania','Europe','Southern Europe',28748.00,1912,3401200,71.6,3205.00,2500.00,
          'Shqipëria','Republic','Rexhep Mejdani',34,'AL'),
...
 
--
-- Dumping data for table `CountryLanguage`
--
 
INSERT INTO `CountryLanguage` (`CountryCode`, `Language`, `IsOfficial`, `Percentage`)
VALUES ('ABW','Dutch','T',5.3),
       ('ABW','English','F',9.5),
       ('ABW','Papiamento','F',76.7),
       ('ABW','Spanish','F',7.4)
       ...
 
-- Dump completed on 2011-09-18 17:41:21

Es probable que tengas problemas con el caracter `, para ello vamos a reemplazar el caracter con la doble comilla » utilizando:

sed -i 's/`/'"'/g' world-mysql-data.sql

Pueden también utilizar el editor de texto de tu preferencia y reemplazar el carácter, el resultado debe ser el siguiente:

-- MySQL dump 10.13  Distrib 5.1.54, for debian-linux-gnu (i686)
--
-- Host: localhost    Database: world
-- ------------------------------------------------------
-- Server version       5.1.54-1ubuntu4
 
--
-- Dumping data for table "City"
--
 
INSERT INTO "City" ("ID", "Name", "CountryCode", "District", "Population")
   VALUES (1,'Kabul','AFG','Kabol',1780000),
          (2,'Qandahar','AFG','Qandahar',237500),
          (3,'Herat','AFG','Herat',186800)
          ...
 
--
-- Dumping data for table "Country"
--
 
 
INSERT INTO "Country" ( "Code", "Name", "Continent", "Region", "SurfaceArea", "IndepYear",
                        "Population", "LifeExpectancy", "GNP", "GNPOld", "LocalName", "GovernmentForm",
                        "HeadOfState", "Capital", "Code2")
VALUES ('ABW','Aruba','North America','Caribbean',193.00,NULL,103000,78.4,828.00,793.00,
           'Aruba','Nonmetropolitan Territory of The Netherlands','Beatrix',129,'AW'),
        ('AFG','Afghanistan','Asia','Southern and Central Asia',652090.00,1919,22720000,
           45.9,5976.00,NULL,'Afganistan/Afqanestan','Islamic Emirate','Mohammad Omar',1,'AF'),
        ('AGO','Angola','Africa','Central Africa',1246700.00,1975,12878000,38.3,6648.00,7984.00,
          'Angola','Republic','José Eduardo dos Santos',56,'AO'),
        ('AIA','Anguilla','North America','Caribbean',96.00,NULL,8000,76.1,63.20,NULL,
          'Anguilla','Dependent Territory of the UK','Elisabeth II',62,'AI'),
        ('ALB','Albania','Europe','Southern Europe',28748.00,1912,3401200,71.6,3205.00,2500.00,
          'Shqipëria','Republic','Rexhep Mejdani',34,'AL'),
...
 
--
-- Dumping data for table "CountryLanguage"
--
 
INSERT INTO "CountryLanguage" ("CountryCode", "Language", "IsOfficial", "Percentage")
VALUES ('ABW','Dutch','T',5.3),
       ('ABW','English','F',9.5),
       ('ABW','Papiamento','F',76.7),
       ('ABW','Spanish','F',7.4)
       ...
 
-- Dump completed on 2011-09-18 17:41:21

Una vez listo el archivo que contiene los datos, los volcamos a PostgreSQL, al igual que el paso 3:

$su postgres

psql -f world_mysql-data.sql -u usuario-db word

13 Respuestas a “Migrar una base de datos MySQL a PostgresQL”

Raul 12 May 2018
1 Reply

Interesante pana pero de donde descargo mysql2pgsl

12 May 2018 at 2:36 pm
- Ivan Cachicatari 12 May 2018
  
  Acabo de actualizar el artículo
  gracias por la observación Raul 🙂
  
  12 May 2018 at 9:48 pm
Oscar 5 Abr 2019

Alguna idea sobre el proceso inverso?
O sea de postgresql a mysql.

5 Abr 2019 at 4:06 pm
- José 23 Jul 2019
  
  De Postgres a MySQL: La heramienta MySQLWorbench realiza esto. Para instalar en distribuciones derivadas de Debian: sudo apt install mysqlworkbench.
  
  23 Jul 2019 at 2:58 pm
fede espinosa pined 28 Mar 2019

como me logueo en el cmd de windows para poder crear la base de datos (world) y seguir con los demás pasos?

28 Mar 2019 at 11:20 am
federico 28 Mar 2019

como me logueo por cmd en windows para crear la base de datos world ?

28 Mar 2019 at 11:18 am
edgar 6 Feb 2019

como resolver los errores de relacion? ejemplo: ERROR: relation «workorder» does not exist

6 Feb 2019 at 3:25 pm
juan mavesoy 2 Oct 2018

en que parte se escriben esos comandos? en el cmd de windows, en la consola de mysql, donde?

2 Oct 2018 at 3:04 pm
- Ivan Cachicatari 7 Oct 2018
  
  Los comandos son sobre Linux… pero creo que algunos pueden ejecutarse en windows.
  
  Los scripts Perl creo que solo pueden ejecutarse sobre Linux (por las librerías que usa)
  
  7 Oct 2018 at 9:13 am
angel 21 Ago 2018

me figura este error
root@test:/var/www# su asasql
asasql@test:/var/www$ createdb agencias
asasql@test:/var/www$ psql -f asa-postgresql.sql -u asasql agencias
/usr/lib/postgresql/9.5/bin/psql: invalid option — ‘u’
Try «psql –help» for more information.

21 Ago 2018 at 3:13 pm
- Ivan Cachicatari 21 Ago 2018
  
  Intenta con -U en mayúsculas
  
  21 Ago 2018 at 7:21 pm
  - angel 22 Ago 2018
    
    me funciono pero me figura esto:
    
    CREATE SEQUENCE
    CREATE TABLE
    CREATE INDEX
    psql:atsa-postgresql.sql:80: ERROR: relation «reservas» does not exist
    psql:atsa-postgresql.sql:86: ERROR: sequence «checkin_cambiofecha_id_seq» does not exist
    CREATE SEQUENCE
    CREATE TABLE
    
    22 Ago 2018 at 11:53 am
luis 11 Jun 2018

saludos cordiales como ejecuto ese comando en windows 10
gracias

11 Jun 2018 at 2:24 pm

Migrar una base de datos MySQL a PostgresQL

Paso 1. Generar un respaldo de la ESTRUCTURA de la base de datos MySQL

Paso 2. Traducir SQL de MySQL a SQL de PostgresSQL utilizando mysql2pgsql.perl

Paso 3: Enviar los datos generados desde MySQL a PostgreSQL

Me gusta esto:

13 Respuestas a “Migrar una base de datos MySQL a PostgresQL”

Dejar una Respuesta