Migrar una base de datos MySQL a PostgresQL

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.

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

 

 

Dejar una Respuesta