Exportando datos en vivo desde mySQL hasta postgreSQL usando mysqldump
Tuesday, June 17th, 2008En muchos casos el proceso de migración de datos de una base de datos a otra se convierte en un tedioso proceso de convertir cosas de un punto a otro (ejemplo: crear archivos separados por coma o dumps SQL que debemos modificar) y muchas veces la gente no explota las capacidades de mySQL como “agente de migración” rápido y efectivo.
MySQL goza de una capacidad interesante para “emular” el comportamiento de otras bases de datos, esto se logra via la variable sql_mode; a nivel de la herramienta de migración mysqldump es a través del parámetro –compatible; con esta capacidad, podremos generar “volcados” (dumps) de tablas completas de una manera “compatible” con otras bases de datos, incluyendo postgreSQL.
La situación de ejemplo:
Tengo dos bases de datos, una en mysql llamada dpt_venezuela (División Político Territorial Venezolana), la obtuve a partir de interesantes transformaciones de la data que entrega el INE (Instituto Nacional de Estadística) que es una vulgar hoja de excel a la cual hay que hacerle muchas transformaciones en los datos (y que gracias a LOAD DATA INFILE en mySQL eso es un paseo); tengo las 4 tablas (entidades, municipios, parroquias y ciudades) en un schema “dpt” dentro de una DB en postgreSQL para un proyecto (iut); la idea ahora es llenar esas tablas sin tener que hacerlo a mano ni tener que generar un DUMP para trabajar con COPY, sino hacerlo directo y “en vivo”.
Describe de la tabla dpt_entidades (mySQL)
CREATE TABLE `dpt_entidades` ( `id_entidad` int(8) unsigned NOT NULL AUTO_INCREMENT, `entidad` varchar(50) DEFAULT NULL, `cod_entidad` char(3) DEFAULT NULL, `capital` varchar(128) DEFAULT NULL, `mostrar` tinyint(1) unsigned NOT NULL DEFAULT '1', PRIMARY KEY (`id_entidad`) ) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8
Tabla dpt.entidades (postgreSQL):
CREATE TABLE dpt.entidades ( id_entidad int4 NOT NULL, entidad varchar(65) NOT NULL, cod_entidad char(3) NOT NULL, capital varchar(128), mostrar int2 NOT NULL DEFAULT 1, CONSTRAINT dpt_entidades_pk PRIMARY KEY (id_entidad) ) WITHOUT OIDS; ALTER TABLE dpt.entidades OWNER TO postgres;
Entendiendo a mysqldump:
mysqldump puede generar archivos separados por coma, xml o en su defecto (que usaremos) sentencias INSERT INTO; como hay diferencias sintácticas entre mySQL y postgreSQL usaremos un parámetro de mysqldump conocido como “–compatible” y que recibe como valor la palabra “postgresql”, el resto de parámetros son útilitarios de mysqldump (–skip-opt elimina comentarios y opciones “solo mysql” y -n me permite obviar las sentencias de CREATE TABLE de la tabla respectiva, –tables me permite generar una lista de tablas que serán exportadas).
Nota: debemos agregar –default-character-set=utf8 ya que por defecto, mysql hará el dump en latin1 y postgreSQL es por defecto utf8.
Inicialmente, la sentencia de mysqldump quedaría asi:
mysqldump -u root --password=paswd001! --database dpt_venezuela -t -n --compact --skip-opt --default-character-set=utf8 --compatible=postgresql --tables dpt_entidades
El único problema que tenemos ahora, es que la sentencia INSERT INTO generada:
INSERT INTO "dpt_entidades" VALUES (1,'Distrito Capital','DCA','Caracas',1);
No es aún compatible con postgresql, ya que como verán, la relación public.dpt_entidades no existe, sino que es dpt.entidades (schema: dpt, tabla: entidades); pero no hay nada que el comando linux sed no pueda arreglar.
Arreglando la salida con sed:
sed es en resumidas cuentas un comando editor; nos permite hacer sustituciones y reemplazos sobre archivos o sobre la salida std de nuestra consola; por ende, podemos pasar toda la salida del comando mysqldump a través de sed y modificar lo que queramos, en nuestro caso, un reemplazo de “dpt_entidades” > dpt.entidades.
Para ello, adicionalmente usamos pipe ( | ) un “concatenador” de la consola Unix, que nos permite indicar que la salida de un comando irá integramente hacia la entrada del comando siguiente; de esta manera, nuestra sentencia mysqldump ahora dice:
mysqldump -u root --password=paswd001! --database dpt_venezuela -t -n --compact --skip-opt --default-character-set=utf8 --compatible=postgresql --tables dpt_entidades | sed -e "s|\"dpt_entidades\"|dpt.entidades|"
Ahora si vemos, nuestras sentencias INSERT tienen la forma que postgreSQL necesita:
INSERT INTO dpt.entidades VALUES (1,'Distrito Capital','DCA','Caracas',1);
Ahora solo falta entregarle estas sentencias a psql (el comando de entrada de postgreSQL).
psql y entrando datos a postgreSQL:
Usar psql es bastante simple, solamente convocamos la DB (iut) usando el usuario owner de la misma (jesuslara) y pidiendo password (-W), en nuestro caso, recibirá datos de entrada usando PIPE ( | ) que viene desde el comando mysqldump+sed anteriormente ejecutado:
psql -U jesuslara -W -h localhost -d iut
y la cosa al final queda:
mysqldump -u root --password=paswd001! --database dpt_venezuela -t -n --compact --skip-opt --default-character-set=utf8 --compatible=postgresql --tables dpt_entidades | sed -e "s|\"dpt_entidades\"|dpt.entidades|" | psql -U jesuslara -W -h localhost -d iut
Entrando los datos correctamente hasta la tabla dpt.entidades de la base de datos iut.
Repetimos esto para municipios, parroquias o ciudades (o de plano, hacemos un dump de todas las tablas, agrandando acorde el sed para transformar todas las salidas a la vez) y tendremos migrados nuestras tablas desde mysql a postgresql sin necesidad de archivos intermedios.
Usando mySQL como gestor de migraciones:
sql_mode y –compatible son grandes aliados a la hora de migrar datos; LOAD DATA INFILE es mucho más poderoso que COPY y por ejemplo, hay muchas cosas allá afuera en MS Access o MS Fox Pro que ameritan ser migradas y mySQL ayuda muchisimo para cargar (además de normalizar y transformar) la data que venga de orígenes heterogéneos y llevarla limpia y sin problemas a postgreSQL.
Que disfruten este truco de la abuela!.
Nota: recuerden que sed es un comando muy poderoso y pueden por ejemplo, aplicar expresiones regulares de reemplazo para la transformación de los datos y los metadatos.