pgloader - Migre de MySQL a PostgreSQL en Linux(Kubuntu)
mysql to postgresql online (4)
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):
-
sudo mkdir -p /home/postgres/main
-
sudo cp -Rp /var/lib/postgresql/8.4/main /home/postgres
-
sudo chown -R postgres.postgres /home/postgres
-
sudo chmod -R 700 /home/postgres
-
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 :
-
sudo apt-get install postgresql pgadmin3
-
sudo /etc/init.d/postgresql-8.4 stop
-
sudo vi /etc/postgresql/8.4/main/postgresql.conf
- Cambie el
data_directory
dedata_directory
a/home/postgres/main
-
sudo /etc/init.d/postgresql-8.4 start
-
sudo -u postgres psql postgres
-
/password postgres
-
sudo -u postgres createdb climate
-
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 .
-
perl Makefile.PL
-
sudo make install
-
sudo apt-get install perl-doc
(extrañamente, no se llamaperldoc
) -
perldoc SQL::Translator::Manual
Extraiga una DDL compatible con PostgreSQL y todos los datos de MySQL
:
-
sqlt -f DBI --dsn dbi:mysql:climate --db-user user --db-password password -t PostgreSQL > climate-pg-ddl.sql
- 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
-
-
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:
-
select concat( ''RENAME TABLE climate.'', TABLE_NAME, '' to climate.'', lower(TABLE_NAME), '';'' ) from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA=''climate'';
- Ejecuta los comandos del paso anterior.
- 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:
-
pgadmin3
(cambia a él) - Haga clic en el icono Ejecutar consultas SQL arbitrarias.
-
climate-pg-ddl.sql
abiertoclimate-pg-ddl.sql
- Buscar
TABLE "
reemplazar conTABLE climate."
(inserte el nombre del esquemaclimate
) - Buscar
on "
reemplazar conon climate."
(inserte el nombre del esquemaclimate
) - 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
alclimate-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!
Convierta el archivo mysqldump a un formato compatible con PostgreSQL
Convierta los datos de la siguiente manera (no use mysql2pgsql.perl ):
Escapar de las citas.
sed "s////'//'/'/g" climate-my.sql | sed "s////r//r/g" | sed "s////n//n/g" > escaped-my.sql
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
Conectarse a la base de datos.
sudo su - postgres
psql climate
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;
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()
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.
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