Frage SQLITE-SQL-Dump-Datei in POSTGRESQL konvertieren


Ich mache Entwicklung mit SQLITE-Datenbank mit Produktion in POSTGRESQL. Ich habe gerade meine lokale Datenbank mit einer großen Menge an Daten aktualisiert und muss eine bestimmte Tabelle in die Produktionsdatenbank übertragen.

Basierend auf dem Laufen sqlite database .dump > /the/path/to/sqlite-dumpfile.sql, SQLITE gibt einen Tabellen-Dump im folgenden Format aus:

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;

Wie konvertiere ich das oben genannte in eine POSTGRESQL-kompatible Speicherauszugsdatei, die ich in meinen Produktionsserver importieren kann?


75
2018-01-03 02:33


Ursprung


Antworten:


Sie sollten in der Lage sein, diese Dump-Datei direkt in zu füttern psql:

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

Wenn du willst id Spalte auf "auto increment" und ändern Sie den Typ von "int" in "serial" in der Tabellenerstellungszeile. PostgreSQL wird dann eine Sequenz an diese Spalte anhängen, so dass INSERTs mit NULL-IDs automatisch den nächsten verfügbaren Wert zugewiesen bekommt. PostgreSQL wird auch nicht erkennen AUTOINCREMENT Befehle, damit diese entfernt werden müssen.

Sie sollten auch nachsehen datetime Spalten im SQLite-Schema und ändern Sie diese in timestamp für PostgreSQL (danke an Lehm um dies zu verdeutlichen).

Wenn Sie Booleans in Ihrer SQLite haben, könnten Sie konvertieren 1 und 0 und 1::boolean und 0::boolean (bzw.) oder Sie könnten die boolesche Spalte im Schema-Bereich des Dumps in eine Ganzzahl ändern und sie dann nach dem Import per Hand in PostgreSQL korrigieren.

Wenn Sie in Ihrer SQLite BLOBs haben, sollten Sie das zu verwendende Schema anpassen bytea. Sie müssen wahrscheinlich einige mischen decode Anrufe auch. Das Schreiben eines Schnellkopierers in Ihrer Lieblingssprache ist möglicherweise einfacher als die SQL-Bearbeitung, wenn Sie viele BLOBs verwenden.

Wie immer, wenn Sie ausländische Schlüssel haben, werden Sie wahrscheinlich nachsehen wollen set constraints all deferred Um Probleme beim Einfügen zu vermeiden, platzieren Sie den Befehl im BEGIN / COMMIT-Paar.

Dank an Nicolas Riley für die booleschen, Blob- und Constraints-Notizen.

Wenn Sie haben ` In Ihrem Code, wie er von einigen SQLite3-Clients generiert wird, müssen Sie sie entfernen.

PostGRESQL erkennt auch nicht unsigned Spalten, möchten Sie dies möglicherweise löschen, oder fügen Sie eine benutzerdefinierte Einschränkung wie folgt hinzu:

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

Während SQLite standardmäßig Nullwerte angibt '', PostgreSQL erfordert, dass sie als gesetzt werden NULL.

Die Syntax in der SQLite-Dump-Datei scheint größtenteils mit PostgreSQL kompatibel zu sein, so dass Sie ein paar Dinge patchen und einspeisen können psql. Das Importieren eines großen Datenstapels durch SQL-INSERTs kann eine Weile dauern, aber es wird funktionieren.


81
2018-05-30 09:30



pgloader

Ich bin auf diesen Beitrag gestoßen, als ich nach einer Möglichkeit suchte, einen SQLite-Dump in PostgreSQL zu konvertieren. Obwohl dieser Post eine akzeptierte Antwort hat (und eine gute bei +1), denke ich, dass das Hinzufügen wichtig ist.

Ich fing an, die Lösungen hier zu untersuchen und erkannte, dass ich nach einer automatisierten Methode suchte. Ich habe die Wiki-Dokumente nachgeschlagen:

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

und entdeckt pgloader. Ziemlich coole Anwendung und es ist relativ einfach zu bedienen. Sie können die flache SQLite-Datei in eine verwendbare PostgreSQL-Datenbank konvertieren. Ich installierte von der *.debund erstellt a command Datei wie folgt in einem Testverzeichnis:

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';

wie Dokumente Zustand. Ich habe dann eine erstellt testdb mit createdb:

createdb testdb

Ich lief die pgloader Befehl wie folgt:

pgloader command

und dann mit der neuen Datenbank verbunden:

psql testdb

Nach einigen Abfragen, um die Daten zu überprüfen, scheint es recht gut zu funktionieren. Ich weiß, wenn ich versucht hätte, eines dieser Skripte auszuführen oder die hier erwähnte schrittweise Umwandlung durchzuführen, hätte ich viel mehr Zeit dafür aufgewendet.

Um das Konzept zu beweisen, habe ich das aufgegeben testdb und in eine Entwicklungsumgebung auf einem Produktionsserver importiert werden und die Daten schön übertragen werden.


42
2018-03-30 17:21



Ich habe ein Skript geschrieben, um das zu tun sqlite3 zu postgres Migration. Es behandelt nicht alle Schema / Datenübersetzungen, die in erwähnt werden https://stackoverflow.com/a/4581921/1303625, aber es tut, was ich tun musste. Hoffentlich wird es ein guter Ausgangspunkt für andere sein.

https://gist.github.com/2253099


15
2017-07-20 16:16



Das Fortsetzung Juwel (eine Ruby-Bibliothek) bietet Daten kopieren über verschiedene Datenbanken: http://sequel.jeremyevans.net/rdoc/files/doc/bin_sequel_rdoc.html#label-Copy+Databases

Im Falle von SQLite wäre es so: sequel -C sqlite://db/production.sqlite3 postgres://user@localhost/db


10
2017-09-18 23:30



Sie können einen einzigen Liner verwenden, hier ein Beispiel mit Hilfe des sed-Befehls:

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 

8