Convertir le fichier de vidage SQL SQLITE en POSTGRESQL

Question

J'ai fait du développement en utilisant la base de données SQLITE avec la production en POSTGRESQL. Je viens de mettre à jour ma base de données locale avec une énorme quantité de données et j'ai besoin de transférer une table spécifique vers la base de données de production.

Sur la base de l'exécution sqlite database .dump > /the/path/to/sqlite-dumpfile.sql SQLITE produit un vidage de table dans le format suivant :

BEGIN TRANSACTION;
CREATE TABLE "courses_school" ("id" integer PRIMARY KEY, "department_count" integer NOT NULL DEFAULT 0, "the_id" integer UNIQUE, "school_name" varchar(150), "slug" varchar(50));
INSERT INTO "courses_school" VALUES(1,168,213,'TEST Name A',NULL);
INSERT INTO "courses_school" VALUES(2,0,656,'TEST Name B',NULL);
....
COMMIT;

Comment puis-je convertir ce qui précède en un fichier de vidage compatible POSTGRESQL que je peux importer dans mon serveur de production ?

Answer 1

Vous devriez être en mesure d'alimenter ce fichier de vidage directement en psql :

/path/to/psql -d database -U username -W < /the/path/to/sqlite-dumpfile.sql

Si vous voulez le id pour "auto increment" puis changez son type de "int" à "serial" dans la ligne de création de la table. PostgreSQL attachera alors une séquence à cette colonne afin que les INSERTS avec des identifiants NULL se voient automatiquement attribuer la prochaine valeur disponible. PostgreSQL ne reconnaîtra pas non plus AUTOINCREMENT Il faut donc les supprimer.

Vous devez également vérifier datetime dans le schéma SQLite et les changer en timestamp pour PostgreSQL. (Merci à Argile pour l'avoir signalé).

Si vous avez des booléens dans votre SQLite, vous pourriez convertir 1 y 0 a 1::boolean y 0::boolean (respectivement) ou vous pourriez changer la colonne booléenne en un entier dans la section schéma du dump et ensuite les corriger à la main dans PostgreSQL après l'importation.

Si vous avez des BLOBs dans votre SQLite, vous devrez ajuster le schéma pour qu'il utilise bytea . Vous aurez probablement besoin de mélanger dans certains decode appels également . Il peut être plus facile d'écrire un copieur rapide dans votre langage préféré que de manipuler le SQL si vous avez beaucoup de BLOB à gérer.

Comme d'habitude, si vous avez des clés étrangères, vous voudrez probablement vous pencher sur la question suivante set constraints all deferred pour éviter les problèmes d'ordre d'insertion, en plaçant la commande à l'intérieur de la paire BEGIN/COMMIT.

Merci à Nicolas Riley pour les notes booléennes, les blobs et les contraintes.

Si vous avez ` sur votre code, tel que généré par certains clients SQLite3, vous devez les supprimer.

PostGRESQL ne reconnaît pas non plus unsigned Il est donc préférable d'abandonner cette contrainte ou d'en ajouter une autre, comme celle-ci :

CREATE TABLE tablename (
    ...
    unsigned_column_name integer CHECK (unsigned_column_name > 0)
);

Alors que SQLite met par défaut les valeurs nulles à '' PostgreSQL exige qu'ils soient définis en tant que NULL .

La syntaxe du fichier de vidage SQLite semble être compatible avec PostgreSQL, de sorte que vous pouvez corriger quelques éléments et le transmettre à psql . L'importation d'un gros tas de données par le biais d'INSERTs SQL peut prendre un certain temps, mais cela fonctionnera.

Answer 2

pgloader

Je suis tombé sur cet article en cherchant un moyen de convertir un dump SQLite en PostgreSQL. Même si cet article a une réponse acceptée (et une bonne réponse en plus +1), je pense qu'il est important d'ajouter ceci.

J'ai commencé à examiner les solutions proposées ici et je me suis rendu compte que je recherchais une méthode plus automatisée. J'ai regardé les documents du wiki :

https://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL

et a découvert pgloader . Une application plutôt cool et relativement facile à utiliser. Vous pouvez convertir le fichier SQLite plat en une base de données PostgreSQL utilisable. J'ai installé à partir du *.deb et a créé un command comme ceci dans un répertoire de test :

load database  
    from 'db.sqlite3'  
    into postgresql:///testdb 

with include drop, create tables, create indexes, reset sequences  

set work_mem to '16MB', maintenance_work_mem to '512 MB';

comme le docs l'État. J'ai ensuite créé un testdb con createdb :

createdb testdb

J'ai lancé le pgloader comme ceci :

pgloader command

et ensuite connecté à la nouvelle base de données :

psql testdb

Après quelques requêtes pour vérifier les données, il s'avère que cela a plutôt bien fonctionné. Je sais que si j'avais essayé d'exécuter un de ces scripts ou de faire la conversion par étapes mentionnée ici, j'aurais passé beaucoup plus de temps.

Pour prouver le concept, j'ai jeté ceci testdb et importé dans un environnement de développement sur un serveur de production et les données ont été transférées sans problème.

Answer 3

En bijou de suite (une bibliothèque Ruby) permet de copier des données entre différentes bases de données : http://sequel.jeremyevans.net/rdoc/files/doc/bin_sequel_rdoc.html#label-Copy+Bases de données

Installez d'abord Ruby, puis installez la gemme en exécutant gem install sequel .

Dans le cas de sqlite, ce serait comme ceci : sequel -C sqlite://db/production.sqlite3 postgres://user@localhost/db

Answer 4

Vous pouvez utiliser une phrase unique, voici un exemple avec l'aide de la commande sed :

sqlite3 mjsqlite.db .dump | sed -e 's/INTEGER PRIMARY KEY AUTOINCREMENT/SERIAL PRIMARY KEY/g;s/PRAGMA foreign_keys=OFF;//;s/unsigned big int/BIGINT/g;s/UNSIGNED BIG INT/BIGINT/g;s/BIG INT/BIGINT/g;s/UNSIGNED INT(10)/BIGINT/g;s/BOOLEAN/SMALLINT/g;s/boolean/SMALLINT/g;s/UNSIGNED BIG INT/INTEGER/g;s/INT(3)/INT2/g;s/DATETIME/TIMESTAMP/g' | psql mypqdb mypguser

Answer 5

J'ai écrit un script pour faire la sqlite3 a postgres la migration. Il ne gère pas toutes les traductions de schémas et de données mentionnées dans le document intitulé https://stackoverflow.com/a/4581921/1303625 mais il fait ce dont j'ai besoin. J'espère que ce sera un bon point de départ pour d'autres.

https://gist.github.com/2253099