pgloader - Migre de MySQL a PostgreSQL en Linux(Kubuntu)

mysql to postgresql online

Question

Hace mucho tiempo en un sistema muy, muy lejos ...

Intentando migrar una base de datos de MySQL a PostgreSQL. Toda la documentación que he leído cubre, con gran detalle, cómo migrar la estructura. He encontrado muy poca documentación sobre la migración de los datos. El esquema tiene 13 tablas (que se han migrado correctamente) y 9 GB de datos.

Versión de MySQL: 5.1.x
Versión de PostgreSQL: 8.4.x

Quiero usar el lenguaje de programación R para analizar los datos mediante sentencias de selección de SQL; PostgreSQL tiene PL / R, pero MySQL no tiene nada (por lo que puedo decir).

Una nueva esperanza

Cree la ubicación de la base de datos ( /var no tiene espacio suficiente; también le disgusta tener el número de versión de PostgreSQL en todas partes: la actualización rompería los scripts):

1. sudo mkdir -p /home/postgres/main
2. sudo cp -Rp /var/lib/postgresql/8.4/main /home/postgres
3. sudo chown -R postgres.postgres /home/postgres
4. sudo chmod -R 700 /home/postgres
5. sudo usermod -d /home/postgres/ postgres

Todo bien aquí. A continuación, reinicie el servidor y configure la base de datos usando estas instrucciones de instalación :

1. sudo apt-get install postgresql pgadmin3
2. sudo /etc/init.d/postgresql-8.4 stop
3. sudo vi /etc/postgresql/8.4/main/postgresql.conf
4. Cambie el data_directory de data_directory a /home/postgres/main
5. sudo /etc/init.d/postgresql-8.4 start
6. sudo -u postgres psql postgres
7. /password postgres
8. sudo -u postgres createdb climate
9. pgadmin3

Utilice pgadmin3 para configurar la base de datos y crear un esquema.

El episodio continúa en un shell remoto conocido como bash , con ambas bases de datos en ejecución y la instalación de un conjunto de herramientas con un logotipo bastante inusual: SQL Fairy .

1. perl Makefile.PL
2. sudo make install
3. sudo apt-get install perl-doc (extrañamente, no se llama perldoc )
4. perldoc SQL::Translator::Manual

Extraiga una DDL compatible con PostgreSQL y todos los datos de MySQL :

1. sqlt -f DBI --dsn dbi:mysql:climate --db-user user --db-password password -t PostgreSQL > climate-pg-ddl.sql
2. Edite climate-pg-ddl.sql y convierta los identificadores a minúsculas, e inserte la referencia del esquema (usando VIM):
   • :%s/"/([A-Z_]*/)"//L/1/g
   • :%s/ TABLE / TABLE climate./g
   • :%s/ on / on climate./g
3. mysqldump --skip-add-locks --complete-insert --no-create-db --no-create-info --quick --result-file="climate-my.sql" --databases climate --skip-comments -u root -p

Podría valer la pena simplemente cambiar el nombre de las tablas y columnas en MySQL a minúsculas:

1. select concat( ''RENAME TABLE climate.'', TABLE_NAME, '' to climate.'', lower(TABLE_NAME), '';'' ) from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA=''climate'';
2. Ejecuta los comandos del paso anterior.
3. Probablemente hay una manera de hacer lo mismo para las columnas; Los cambié manualmente porque era más rápido que averiguar cómo escribir la consulta.

La base de datos contraataca

Vuelva a crear la estructura en PostgreSQL de la siguiente manera:

1. pgadmin3 (cambia a él)
2. Haga clic en el icono Ejecutar consultas SQL arbitrarias.
3. climate-pg-ddl.sql abierto climate-pg-ddl.sql
4. Buscar TABLE " reemplazar con TABLE climate." (inserte el nombre del esquema climate )
5. Buscar on " reemplazar con on climate." (inserte el nombre del esquema climate )
6. Presiona F5 para ejecutar

Esto resulta en:

Query returned successfully with no result in 122 ms.

Respuestas de los Jedi

En este punto estoy perplejo.

• ¿A dónde voy desde aquí ( cuáles son los pasos ) para convertir el climate-my.sql al climate-pg.sql para que puedan ejecutarse contra PostgreSQL?
• ¿Cómo me aseguro de que los índices se copien correctamente (para mantener la integridad referencial; no tengo restricciones en este momento para facilitar la transición)?
• ¿Cómo me aseguro de que la adición de nuevas filas en PostgreSQL comience a enumerar desde el índice de la última fila insertada (y no entre en conflicto con una clave primaria existente de la secuencia)?
• ¿Cómo se asegura de que el nombre del esquema llegue al transformar los datos de MySQL a las inserciones de PostgreSQL?

Recursos

Se necesitaba un poco de información para llegar hasta aquí:

• https://help.ubuntu.com/community/PostgreSQL
• http://articles.sitepoint.com/article/site-mysql-postgresql-1
• http://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL#MySQL
• http://pgfoundry.org/frs/shownotes.php?release_id=810
• http://sqlfairy.sourceforge.net/

¡Gracias!

Answer 1

Convierta el archivo mysqldump a un formato compatible con PostgreSQL

Convierta los datos de la siguiente manera (no use mysql2pgsql.perl ):

1. Escapar de las citas.

   sed "s////'//'/'/g" climate-my.sql | sed "s////r//r/g" | sed "s////n//n/g" > escaped-my.sql

2. Reemplace el USE "climate"; Con una ruta de búsqueda y comentar los comentarios:

   sed "s/USE /"climate/";/SET search_path TO climate;/g" escaped-my.sql | sed "s/^///*/--/" > climate-pg.sql

3. Conectarse a la base de datos.

   sudo su - postgres
psql climate

4. Establezca la codificación (mysqldump ignora su parámetro de codificación) y luego ejecute el script.

   /encoding iso-8859-1
/i climate-pg.sql

Esta serie de pasos probablemente no funcionará para bases de datos complejas con muchos tipos mixtos. Sin embargo, funciona para integer s, varchar s y float s.

Índices, claves primarias y secuencias.

Dado que mysqldump incluyó las claves primarias al generar las INSERT , éstas prevalecerán en la secuencia automática de la tabla. Las secuencias para todas las tablas permanecieron 1 después de la inspección.

Establecer la secuencia después de la importación

El uso del comando ALTER SEQUENCE los establecerá en el valor que sea necesario.

Prefijo de esquema

No es necesario prefijar las tablas con el nombre del esquema. Utilizar:

SET search_path TO climate;

Answer 2

Echa un vistazo a etlalchemy . Le permite migrar de MySQL a PostgreSQL , o entre otras bases de datos, en 4 líneas de Python. Puedes leer más sobre esto here .

Para instalar: pip install etlalchemy

Correr:

from etlalchemy import ETLAlchemySource, ETLAlchemyTarget
# Migrate from MySQL to PostgreSQL
src = ETLAlchemySource("mysql://user:passwd@hostname/dbname")
tgt = ETLAlchemyTarget("postgresql://user:passwd@hostname/dbname",
drop_database=True)
tgt.addSource(src)
tgt.migrate()

Answer 3

Lo que normalmente hago para tales migraciones es doble:

• Extraiga toda la definición de la base de datos de MySQL y adáptela a la sintaxis de PostgreSQL.
• Repase la definición de la base de datos y transfórmelo para aprovechar la funcionalidad de PostgreSQL que no existe en MySQL.

Luego realice la conversión y escriba un programa en el idioma con el que se sienta más cómodo para lograr lo siguiente:

• Lee los datos de la base de datos MySQL.
• Realiza cualquier transformación que sea necesaria en los datos que se almacenarán en la base de datos PostgreSQL.
• Guarda los datos ahora transformados en la base de datos PostgreSQL.

Rediseñe las tablas para que PostgreSQL aproveche sus características.

Si solo hace algo como usar un script sed para convertir el volcado de SQL de un formato al siguiente, todo lo que está haciendo es poner una base de datos MySQL en un servidor PostgreSQL. Puede hacerlo, y todavía habrá algún beneficio al hacerlo, pero si va a migrar, migre completamente.

Implicará un poco más de tiempo adelantado, pero todavía tengo que encontrar una situación en la que no vale la pena.

Answer 4

Si ha convertido un esquema, la migración de datos sería la parte fácil:

• volcado del esquema de PostgreSQL (usted dijo que ha convertido el esquema a postgres, por lo que lo volcaremos por ahora, ya que estaremos eliminando y volviendo a crear la base de datos de destino para limpiarlo):

  pg_dump dbname > /tmp/dbname-schema.sql

• dividir el esquema en 2 partes: /tmp/dbname-schema-1.sql contiene las declaraciones de la tabla de creación, /tmp/dbname-schema-2.sql - el resto. PostgreSQL necesita importar datos antes de que se importen claves externas, desencadenadores, etc., pero después de que se importen las definiciones de la tabla.

• recrear la base de datos con solo 1 parte del esquema:

  drop database dbname create database dbname /i /tmp/dbname-schema-1.sql -- now we have tables without data, triggers, foreign keys etc.

• datos de importacion:

  ( echo ''start transaction''; mysqldump --skip-quote-names dbname | grep ^INSERT; echo ''commit'' ) | psql dbname -- now we have tables with data, but without triggers, foreign keys etc.

  Se agrega una opción --skip-quote-names en MySQL 5.1.3, por lo tanto, si tiene una versión anterior, instale temporalmente mysql más reciente en /tmp/mysql ( configure --prefix=/tmp/mysql && make install debería funcionar) y usa /tmp/mysql/bin/mysqldump .

• importar el resto del esquema:

  psql dbname start transaction /i /tmp/dbname-schema-2.sql commit -- we''re done