Convertir SQLITE, SQL fichier de vidage de POSTGRESQL

J'ai été faire du développement à l'aide de la base de données SQLITE avec la production dans POSTGRESQL. Je viens de mettre à jour ma base de données locale avec une énorme quantité de données et la nécessité de transférer une table spécifique à la base de données de production.

Basée sur l'exécution de sqlite database .dump > /the/path/to/sqlite-dumpfile.sql, SQLITE sorties d'un tableau de vidage 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 ci-dessus dans un POSTGRESQL compatible fichier dump que je peux importer dans mon serveur de production?

Et bien, cette commande n'a pas fonctionné pour moi jusqu'à ce que j'ai changé de sqlite pour sqlite3

InformationsquelleAutor DevX | 2011-01-03

88

Vous devriez être en mesure à alimenter le fichier de vidage directement dans psql:
```
/path/to/psql -d database -U username -W < /the/path/to/sqlite-dumpfile.sql
```
Si vous voulez le id colonne "auto increment" puis changer son type de "int" à "série" dans la création de la table ligne. PostgreSQL fixera ensuite une séquence à la colonne afin que les Insertions avec NULL id sera attribué automatiquement la valeur suivante disponible. PostgreSQL ne sera également pas reconnaître AUTOINCREMENT de commandes, pour ces le besoin d'être enlevé.

Vous aurez également besoin de vérifier pour datetime colonnes dans l'SQLite schéma et de le modifier à timestamp pour PostgreSQL (grâce à L'argile pour le signaler).

Si vous avez des booléens dans votre SQLite, vous pouvez convertir 1 et 0 et 1::boolean et 0::boolean (respectivement) ou vous pouvez changer la colonne booléenne à un nombre entier dans le schéma de la section de la décharge et ensuite les corriger à la main à l'intérieur de PostgreSQL après l'importation.

Si vous avez des Gouttes dans votre SQLite, alors vous aurez envie de régler le schéma à utiliser bytea. Vous aurez probablement besoin de mélanger dans certains décoder appels ainsi. La rédaction d'un rapide n'dirty copieur dans votre langue préférée peut être plus facile que d'amputation SQL si vous avez beaucoup de Gouttes de traiter avec si.

Comme d'habitude, si vous avez des clés étrangères, alors vous aurez probablement envie de regarder dans définir les contraintes de toutes les pour éviter d'insérer la commande de problèmes, la passation de la commande à l'intérieur de l'BEGIN/COMMIT paire.

Grâce à Nicolas Riley pour les booléens, blob, et les contraintes de notes.

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

PostGRESQL aussi ne pas reconnaître unsigned colonnes, vous pouvez déposer, ou ajouter une mesure de contrainte comme ceci:
```
CREATE TABLE tablename (
    ...
    unsigned_column_name integer CHECK (unsigned_column_name > 0)
);
```
Alors que SQLite par défaut les valeurs null pour '', PostgreSQL oblige à être défini comme NULL.

La syntaxe dans l'SQLite fichier de vidage semble être la plupart du temps compatible avec PostgreSQL, donc vous pouvez appliquer un peu de choses et le nourrir à psql. L'importation d'un gros tas de données via SQL insert peut prendre un certain temps, mais ça va marcher.
- Merci!!!! Merci!!!! Cela a très bien fonctionné pour moi. J'ai eu un programme de cette migration au premier abord, mais il était bien trop lent et causant d'autres problèmes. La Correction de votre commande. Il devrait être -d, pas -D.
- Une note si quelqu'un d'autre a ce problème, SQLITE eu mes valeurs null en tant que ". POSTGRESQL exige NULLE. Si le feu jusqu'à vim et faire la commande: %s/"/NULL/g de fixer le fichier de vidage.
- Non, vous voulez garder la transaction pour éviter une surcharge.
- Je pense que Peter a peut-être raison. Bien que mu réponse est aujourd'hui (et comment j'en suis arrivé à la migration de travail), selon cette FAQ, il est même préférable de garder l'BEGIN/COMMIT: sqlite.org/faq.html#q19
- Opérations: on peut penser que PostgreSQL avoir à s'engager chaque INSERTION de disque sans la grande opération d'emballage de l'ensemble de la chose, mais, avec l'opération wrapper, il va tout simplement de la file d'attente d'un gros tas de trucs et de faire un grand écrire à la fin. Droit? Et merci pour la correction sur le psql commutateurs.
- Cela fonctionne très bien. Je voudrais aussi noter que si vous avez besoin de migrer sqlite datetime colonnes, que vous avez à les modifier à timestamp pour postgres.
- Merci pour ça, j'ai compris que dans la réponse à la postérité.
- Un peu plus de problèmes que j'ai rencontré: le changement de BLOB en BYTEA (stackoverflow.com/questions/3103242), la modification de 0/1 pour BOOLEAN les colonnes de '0'/'1', et à reporter les contraintes (DEFERRABLE / SET CONSTRAINTS ALL DEFERRED).
- Merci pour cela. Je vous l'ai donné à un wiki de la communauté depuis qu'il a tourné dans un effort de groupe, juste est juste.
- Aussi unsigned devrait être abandonnée.
- Vous pouvez utiliser to_timestamp() dans postgreSQL pour convertir un timestamp à un progreSQL timestamp
- J'avais remarque que le integer type de Sqlite par défaut est de 8 octets, alors que la integer type PostgreSQL est de 4 octets. Pour être sûr, vous pouvez convertir vos integertapé colonnes dans Sqlite, à la bigint type PostgreSQL.
- Sachez également que SQLite magasins horodateurs avec la zone par défaut, PostgreSQL n'est pas. Si vous avez besoin de conserver les informations de fuseau horaire, vous pouvez utiliser timestamptz comme type de colonne. Pour plus de détails, voir postgresql.org/docs/9.2/static/datatype-datetime.html
- ERROR: relation "sqlite_sequence" does not exist à DELETE FROM sqlite_sequence; comment résoudre ce problème?
- Retirer du dump SQL ou utiliser l'une des solutions? Je ne suis même pas sûr où cela vient.
- Devrait supprimer DELETE FROM sqlite_sequence; et remplacer INSERT INTO "sqlite_sequence" VALUES('locations',7); avec ALTER SEQUENCE locations_id_seq RESTART WITH 8; et ça marche...!
- "Vous devriez être en mesure à alimenter le fichier de vidage directement dans psql" Cela ne fonctionne pas à tous. Le sous-évaluées sqlite fichier sql besoins importants conversion, vos commentaires font allusion, mais ne pas expliquer totalement. Vous attendiez-vous à l'OP pour modifier manuellement une gigabyte taille fichier sql?
- Le fichier posté il y a huit ans (!) fut plutôt un simple "nettoyage et vidage en psql de travail". Dans le cas général, ce n'est pas vrai, alors j'ai donné la réponse comme un wiki de la communauté (c'est à dire je ne reçois rien pour cela et il est facile pour l'ensemble de la communauté pour mettre à jour et de maintenir et de se plaindre et de quoi que ce soit).
InformationsquelleAutor
55

pgloader

Je suis tombé sur ce post lors de la recherche d'un moyen de convertir un dump SQLite pour PostgreSQL. Même si ce post a accepté de répondre (et un bon à +1), je pense que l'ajout de ce qui est important.

J'ai commencé à regarder dans les solutions ici et réalisé que je cherchais une méthode automatique. J'ai regardé le wiki docs:

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

et découvert pgloader. Assez cool d'application et il est relativement facile à utiliser. Vous pouvez convertir le plat SQLite fichier utilisable à une base de données PostgreSQL. J'ai installé le *.deb et créé un command fichier 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 état. Ensuite, j'ai créé un testdb avec createdb:

createdb testdb

J'ai couru le pgloader de commande comme ceci:

pgloader command

puis connecté à la nouvelle base de données:

psql testdb

Après quelques requêtes pour vérifier les données, il apparaît qu'il a très bien. Je sais que si j'avais essayé d'exécuter l'un de ces scripts ou de faire la conversion progressive mentionnés dans ce document, j'aurais passé beaucoup plus de temps.

Pour prouver le concept j'ai perdu cette testdb et importés dans un environnement de développement sur un serveur de production et les données transférées sur bien.
- Méfiez-vous que (encore pris en charge) les distributions Ubuntu pourrait avoir version obsolète - v2.x.y sont déjà obsolète et ne fait pas travailler. v3.2.x pourrait fonctionner, mais v3.2.3 est recommandé. J'ai récupéré v3.2.3 à partir de la pointe et installé avec sudo dpkg -i <.deb nom de fichier>, il n'avait aucun problème avec dépendances.
- Je suis d'accord avec @silpol - assurez-vous de télécharger la dernière version stable et installer à l'aide de votre fav gestionnaire de paquet; pour la "commande" fichier c'est juste un fichier texte appelé "commande" sans le nom de l'extension (c'est à dire pas besoin de .txt à la fin du nom de fichier), vous n'avez pas besoin de mettre le nom de fichier dans angulaire entre parenthèses; j'ai dû changer la search_parth de la psql base de données afin de voir mes données; pgloader fonctionne bien et m'a sauvé de beaucoup de tracas
- cela permet d'économiser de ma journée.
- Ouais, j'ai eu du mal quand j'ai rencontré ce problème, et que l'outil fait-il si facile... Parfois les choses ne fonctionnent pas aussi bien, n'est-ce pas?
- Merci bro. Je vois cette réponse vaut pour être la accepté de répondre! très bon outil.
InformationsquelleAutor nicorellius
15

J'ai écrit un script pour faire le sqlite3 à postgres de la migration. Il ne gère pas tous le schéma/données traductions mentionnées dans https://stackoverflow.com/a/4581921/1303625, mais ça fait ce que je devais faire. J'espère que ce sera un bon point de départ pour d'autres.

https://gist.github.com/2253099
- Cela fonctionne bien! J'ai forké le Gist, et a ajouté quelques idées comme un commentaire: gist.github.com/bittner/7368128
InformationsquelleAutor Earle Clubb
11

La suite gem (Ruby library) offre de copie de 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

En cas de sqlite, ce serait comme ça:
sequel -C sqlite://db/production.sqlite3 postgres://user@localhost/db

InformationsquelleAutor lulalala
7

Vous pouvez utiliser une seule ligne, voici un exemple avec l'aide de la commande sed:
```
sqlite3 mjsqlite.db .dump | sed -e 's/INTEGER PRIMARY KEY AUTOINCREMENT/SERIAL PRIMARY KEY/' | sed -e 's/PRAGMA foreign_keys=OFF;//' | sed -e 's/unsigned big int/BIGINT/g' | sed -e 's/UNSIGNED BIG INT/BIGINT/g' | sed -e 's/BIG INT/BIGINT/g' | sed -e 's/UNSIGNED INT(10)/BIGINT/' | sed -e 's/BOOLEAN/SMALLINT/g' | sed -e 's/boolean/SMALLINT/g' | sed -e 's/UNSIGNED BIG INT/INTEGER/g' | sed -e 's/INT(3)/INT2/g' | sed -e 's/DATETIME/TIMESTAMP/g' | psql mypqdb mypguser 
```
- il n'y a pas de remplacement de type LONG, par exemple
- un élément de plus qui pourrait être ajouté sed -e 's/DATETIME/TIMESTAMP/g'
- sed -e 's/TINYINT(1)/SMALLINT/g' -- et pour une comparaison de tous les types de données, voir stackoverflow.com/questions/1942586/...
- J'ai également eu un problème avec un SMALLINT que, par défaut, 't' ou 'f' dans le sqlite. Évidemment, un booléen, mais ne sont pas assez familiers avec db système de recommander un coffre-fort correctif.
- Cela ne fonctionne pas. Je reçois le message d'erreur ERROR: syntax error at or near "AUTOINCREMENT"
InformationsquelleAutor develCuy
0

J'ai essayé d'édition/regexping l'sqlite dump pour PostgreSQL accepte, il est fastidieuse et sujette à l'erreur.

Ce que j'ai eu à travailler très vite:

Première recréer le schéma sur PostgreSQL, sans données, soit à l'édition de la décharge ou si vous utilisez un ORM vous pouvez être chanceux et il parle à la fois aux extrémités (sqlalchemy, peewee, ...).

Puis migrer les données à l'aide de pandas. Supposons que vous avez une table avec un bool champ (qui est 0/1 dans sqlite, mais doit être t/f dans PostgreSQL)
```
def int_to_strbool(df, column):
    df = df.replace({column: 0}, 'f')
    df = df.replace({column: 1}, 't')
    return df

#def other_transform(df, column):
#...

conn = sqlite3.connect(db)
df = pd.read_sql(f'select * from {table_name}', conn)

df = int_to_strbool(df, bool_column_name)
#df = other_transform(df, other_column_name)

df.to_csv(table_name + '.csv'), sep=',', header=False, index=False)
```
Cela fonctionne comme un charme, est facile à lire, écrire et déboguer chaque fonction, à la différence (pour moi) les expressions régulières.

Maintenant, vous pouvez essayer de charger le résultant csv avec PostgreSQL (même graphiquement avec l'outil d'administration), avec la seule réserve que vous devez charger les tables avec des clés étrangères après avoir chargé les tableaux avec les touches de source. Je n'ai pas eu le cas d'une dépendance circulaire, je suppose que vous pouvez suspendre temporairement la clé de vérifier si c'est le cas.

InformationsquelleAutor agomcas
-1

pgloader faire des merveilles sur la conversion de la base de données sqlite pour postgresql.

Voici un exemple sur la conversion d'un local sqlitedb à une distance de PostgreSQL db:

pgloader sqlite.db postgresql://nom d'utilisateur:mot de passe@nom d'hôte/dbname
- Pgloader est terriblement buggy et peu fiables. Il se bloque immédiatement avec l'erreur KABOOM! Control stack exhausted (no more space for function call frames).
InformationsquelleAutor kouichi

Vous devez vous connecter pour publier un commentaire.

pgloader

`pgloader`