Migrer de MySQL à PostgreSQL sur Linux (Kubuntu)

il y a très longtemps sur un système très, très éloigné...

Essayer de migrer une base de données de MySQL vers PostgreSQL. Toute la documentation que j'ai lu couvre, en détail, comment migrer la structure. J'ai trouvé très peu de documentation sur la migration des données. Le schéma comporte 13 tableaux (qui ont été migrés avec succès) et 9 Go de données.

MySQL version: 5.1.x

PostgreSQL version: 8.4.x

je veux utiliser le langage de programmation R pour analyser les données en utilisant SQL select statements; PostgreSQL a PL/R, mais MySQL n'a rien (autant que je puisse dire).

Un Nouvel Espoir

crée l'emplacement de la base de données ( /var n'a pas assez d'espace; aussi n'aime pas avoir le numéro de version PostgreSQL partout -- la mise à jour briserait les 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

tout est bon pour ici. Ensuite, redémarrez le serveur et configurez la base de données en utilisant ces instructions d'installation :

  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. changement data_directory à /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

utilisez pgadmin3 pour configurer la base de données et créer un schéma.

l'épisode continue dans un shell distant connu sous le nom de bash , avec les deux bases de données en cours d'exécution, et l'installation d'un ensemble d'outils avec un logo assez inhabituel: SQL Fairy .

  1. perl Makefile.PL
  2. sudo make install
  3. sudo apt-get install perl-doc (étrangement, il n'est pas appelé perldoc )
  4. perldoc SQL::Translator::Manual

extraire un DDL PostgreSQL-friendly et toutes les MySQL données:

  1. sqlt -f DBI --dsn dbi:mysql:climate --db-user user --db-password password -t PostgreSQL > climate-pg-ddl.sql
  2. éditer climate-pg-ddl.sql et convertir les identificateurs en minuscules, et insérer la référence de schéma (en utilisant VIM):
    • :%s/"([A-Z_]*)"/L/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

il pourrait être intéressant de simplement renommer les tables et les colonnes de MySQL en minuscules:

  1. select concat( 'RENAME TABLE climate.', TABLE_NAME, ' to climate.', lower(TABLE_NAME), ';' ) from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='climate';
  2. exécute les commandes de l'étape précédente.
  3. il y a probablement un moyen de faire la même chose pour les colonnes; je les ai changées manuellement parce que c'était plus rapide que de comprendre comment écrire la requête.

La Base De Données Strikes Back

recréer la structure dans PostgreSQL comme suit:

  1. pgadmin3 (commutateur)
  2. cliquez sur le exécuter des requêtes SQL arbitraires icône
  3. Ouvrir climate-pg-ddl.sql
  4. recherche de TABLE " remplacer par TABLE climate." (insérer le nom de schéma climate )
  5. recherche de on " remplacer par on climate." (insérer le nom de schéma climate )
  6. Appuyez sur F5 pour exécuter

il en résulte: 

Query returned successfully with no result in 122 ms.

réponses des Jedi

à ce point, je suis perplexe.

  • Où dois-je aller à partir d'ici ( quelles sont les étapes ) pour convertir climate-my.sql en climate-pg.sql afin qu'ils puissent être exécutés contre PostgreSQL?
  • comment m'assurer que les index sont copiés correctement (pour maintenir l'intégrité référentielle; Je n'ai pas de contraintes pour le moment pour faciliter la transition)?
  • Comment puis-je m'assurer que l'ajout de nouvelles lignes dans PostgreSQL commencera à énumérer à partir de l'index de la dernière ligne insérée (et ne sera pas en conflit avec une clé primaire existante de la séquence)?
  • comment vous assurez-vous que le nom de schéma passe lors de la transformation des données de MySQL en inserts PostgreSQL?

ressources

un bon bout d'information a été nécessaire pour obtenir jusqu'ici:

Merci!

18
demandé sur Dave Jarvis 2010-05-14 03:48:44

4 réponses

ce que je fais habituellement pour de telles migrations est double:

  • extraire la définition complète de la base de données de MySQL et l'adapter à la syntaxe PostgreSQL.
  • passez en revue la définition de la base de données et transformez-la pour profiter des fonctionnalités de PostgreSQL qui n'existent pas dans MySQL.

alors faire la conversion, et écrire un programme dans n'importe quelle langue que vous êtes le plus à l'aise avec qui accomplit le

  • lit les données de la base de données MySQL.
  • effectue toute transformation nécessaire sur les données à stocker dans la base de données PostgreSQL.
  • enregistre les données maintenant transformées dans la base de données PostgreSQL.

redessinez les tableaux pour que PostgreSQL tire profit de ses caractéristiques.

si vous faites juste quelque chose comme utiliser un script sed pour convertir le dump SQL d'un format à l'autre, tout ce que vous faites est de mettre une base de données MySQL dans un serveur PostgreSQL. Vous pouvez le faire, et il y aura encore des avantages à le faire, mais si vous allez migrer, migrez complètement.

cela impliquera un peu plus de temps passé à l'avance, mais je n'ai pas encore trouvé une situation où cela n'en vaut pas la peine.

4
répondu Michael Trausch 2015-06-15 19:24:40

Convertir la commande mysqldump fichier PostgreSQL format convivial

Convertissez les données comme suit (n'utilisez pas mysql2pgsql.perl ):

  1. Échapper les guillemets.

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

  2. remplacer le USE "climate"; par un chemin de recherche et commenter les commentaires:

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

  3. connectez-vous à la base de données.

    sudo su - postgres

    psql climate

  4. définit l'encodage (mysqldump ignore son paramètre d'encodage) puis exécute le script.

    \encoding iso-8859-1

    \i climate-pg.sql

Cette série d'étapes ne fonctionnera probablement pas pour le complexe bases de données avec de nombreux types mixtes. Toutefois, il travaille pour integer s, varchar s et float .

Index, les clés primaires et les séquences

étant donné que mysqldump incluait les touches primaires lors de la génération des instructions INSERT , elles supplanteront la séquence automatique de la table. Les séquences pour toutes les tables sont demeurées 1 lors de l'inspection.

définir la séquence après l'importation

à l'Aide de la commande ALTER SEQUENCE les définira à n'importe quelle valeur nécessaire.

Préfixe Du Schéma

Il n'est pas nécessaire de préfixer les tables avec le nom de schéma. Use: 

SET search_path TO climate;
2
répondu Dave Jarvis 2010-05-14 16:11:59

si vous avez converti un schéma, la migration des données serait la partie facile:

  • dump schema from PostgreSQL (vous avez dit que vous avez converti schema en postgres, donc nous allons le dump pour l'instant, comme nous allons supprimer et recréer la base de données cible, pour le faire nettoyer): 

    pg_dump dbname > /tmp/dbname-schema.sql

  • split schéma 2 pièces - /tmp/dbname-schema-1.sql contenant les instructions create table, /tmp/dbname-schema-2.sql - le reste. PostgreSQL doit importer des données avant les clés étrangères, les déclencheurs, etc. sont importés, mais les définitions après table sont importées.

  • recréer la base de données avec seulement 1 partie de schéma: 

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

  • importation de données: 

    (
   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.

    a --skip-quote-names option est ajoutée dans MySQL 5.1.3, donc si vous avez une version plus ancienne, puis installer mysql plus récente temporairement dans /tmp/mysql ( configure --prefix=/tmp/mysql && make install devrait faire) et utiliser /tmp/mysql/bin/mysqldump .

  • importer le reste du schéma: 

    psql dbname
start transaction
\i /tmp/dbname-schema-2.sql
commit
-- we're done
2
répondu Tometzky 2010-05-17 13:38:03

Check out etlalchemy . Il vous permet de migrer de MySQL à PostgreSQL , ou entre plusieurs autres bases de données, en 4 lignes de Python. Vous pouvez lire plus à propos de it ici .

à installer: pip install etlalchemy

: 

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()
0
répondu The Aelfinn 2016-07-22 14:31:41