Pertanyaan Konversi file dump SQLITE SQL ke POSTGRESQL


Saya sudah melakukan pengembangan menggunakan database SQLITE dengan produksi di POSTGRESQL. Saya baru saja memperbarui database lokal saya dengan sejumlah besar data dan perlu mentransfer tabel spesifik ke basis data produksi.

Berdasarkan berjalan sqlite database .dump > /the/path/to/sqlite-dumpfile.sql, SQLITE menghasilkan tabel dump dalam format berikut:

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;

Bagaimana cara mengonversi di atas menjadi file dump POSTGRESQL yang kompatibel yang dapat saya impor ke server produksi saya?


75
2018-01-03 02:33


asal


Jawaban:


Anda harus dapat memberi makan file dump itu langsung psql:

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

Jika Anda menginginkan id kolom ke "kenaikan otomatis" kemudian ubah jenisnya dari "int" menjadi "serial" di baris pembuatan tabel. PostgreSQL kemudian akan melampirkan urutan ke kolom itu sehingga INSERTs dengan NULL id akan secara otomatis diberi nilai yang tersedia berikutnya. PostgreSQL juga tidak akan dikenali AUTOINCREMENT perintah, jadi ini harus dihapus.

Anda juga harus memeriksanya datetime kolom dalam skema SQLite dan mengubahnya menjadi timestamp untuk PostgreSQL (terima kasih kepada Tanah liat untuk menunjukkan ini).

Jika Anda memiliki boolean di SQLite Anda maka Anda dapat mengkonversi 1 dan 0 dan 1::boolean dan 0::boolean (Masing-masing) atau Anda dapat mengubah kolom boolean menjadi integer di bagian skema dump dan kemudian memperbaikinya dengan tangan di dalam PostgreSQL setelah impor.

Jika Anda memiliki BLOB di SQLite Anda maka Anda akan ingin menyesuaikan skema yang akan digunakan bytea. Anda mungkin perlu mencampurkan beberapa decode panggilan juga. Menulis mesin fotokopi cepat dalam bahasa favorit Anda mungkin lebih mudah daripada mengelola SQL jika Anda banyak BLOBs untuk menangani sekalipun.

Seperti biasa, jika Anda memiliki kunci asing maka Anda mungkin ingin melihatnya set constraints all deferred untuk menghindari memasukkan masalah pemesanan, menempatkan perintah di dalam pasangan BEGIN / COMMIT.

Terimakasih untuk Nicolas Riley untuk catatan boolean, gumpalan, dan kendala.

Jika Anda memiliki ` pada kode Anda, seperti yang dihasilkan oleh beberapa klien SQLite3, Anda harus menghapusnya.

PostGRESQL juga tidak dikenali unsigned kolom, Anda mungkin ingin menjatuhkan itu, atau menambahkan kendala custom-made seperti ini:

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

Sementara SQLite menetapkan nilai null ke '', PostgreSQL mengharuskannya ditetapkan sebagai NULL.

Sintaks dalam file dump SQLite tampaknya sebagian besar kompatibel dengan PostgreSQL sehingga Anda dapat menambal beberapa hal dan memberikannya ke psql. Mengimpor tumpukan data besar melalui SQL INSERT mungkin membutuhkan waktu tetapi akan berhasil.


81
2018-05-30 09:30



pgloader

Saya menemukan posting ini ketika mencari cara untuk mengkonversi dump SQLite ke PostgreSQL. Meskipun posting ini memiliki jawaban yang diterima (dan yang bagus pada +1), saya pikir menambahkan ini penting.

Saya mulai mencari solusi di sini dan menyadari bahwa saya sedang mencari metode yang lebih otomatis. Saya mencari dokumen wiki:

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

dan ditemukan pgloader. Aplikasi keren dan relatif mudah digunakan. Anda dapat mengonversi file datar SQLite ke dalam database PostgreSQL yang dapat digunakan. Saya menginstal dari *.debdan membuat a command file seperti ini di direktori uji coba:

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

seperti dokumen negara. Saya kemudian membuat sebuah testdb dengan createdb:

createdb testdb

Saya menjalankan pgloader perintah seperti ini:

pgloader command

dan kemudian terhubung ke database baru:

psql testdb

Setelah beberapa pertanyaan untuk memeriksa data, tampaknya itu berfungsi dengan baik. Saya tahu jika saya mencoba menjalankan salah satu skrip ini atau melakukan konversi bertahap yang disebutkan di sini, saya akan menghabiskan lebih banyak waktu.

Untuk membuktikan konsep saya membuang ini testdb dan diimpor ke lingkungan pengembangan di server produksi dan data ditransfer dengan baik.


42
2018-03-30 17:21



Saya menulis naskah untuk melakukan itu sqlite3 untuk postgres migrasi. Ia tidak menangani semua terjemahan skema / data yang disebutkan di dalamnya https://stackoverflow.com/a/4581921/1303625, tetapi melakukan apa yang saya butuhkan untuk dilakukan. Semoga ini akan menjadi titik awal yang baik untuk orang lain.

https://gist.github.com/2253099


15
2017-07-20 16:16



Itu permata sekuel (Pustaka Ruby) menawarkan penyalinan data di berbagai basis data: http://sequel.jeremyevans.net/rdoc/files/doc/bin_sequel_rdoc.html#label-Copy+Databases

Dalam kasus sqlite, itu akan menjadi seperti ini: sequel -C sqlite://db/production.sqlite3 postgres://user@localhost/db


10
2017-09-18 23:30



Anda dapat menggunakan satu liner, di sini adalah contoh dengan bantuan perintah 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 

8