Konwertuj plik SQLITE SQL dump do POSTGRESQL
Zajmuję się tworzeniem baz danych SQLITE z produkcją w POSTGRESQL. Właśnie zaktualizowałem swoją lokalną bazę danych o ogromną ilość danych i muszę przenieść konkretną tabelę do bazy produkcyjnej.
Na podstawie działania sqlite database .dump > /the/path/to/sqlite-dumpfile.sql, SQLITE wyświetla zrzut tabeli w następującym formacie:
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;
Jak przekonwertować powyższe pliki do pliku zrzutu zgodnego z POSTGRESQL, który mogę zaimportować na mój serwer produkcyjny?
6 answers
Powinieneś być w stanie przesłać plik zrzutu bezpośrednio do psql:
/path/to/psql -d database -U username -W < /the/path/to/sqlite-dumpfile.sql
Jeśli chcesz, aby kolumna id Była "auto increment", zmień jej typ z "int" na "serial" w linii tworzenia tabeli. PostgreSQL następnie dołączy sekwencję do tej kolumny tak, że Wstawki z NULL Id zostaną automatycznie przypisane następnej dostępnej wartości. PostgreSQL również nie rozpoznaje poleceń AUTOINCREMENT, więc należy je usunąć.
Będziesz również chciał sprawdzić datetime kolumny w Schemat SQLite i zmień je na timestamp dla PostgreSQL (dzięki Clay za wskazanie tego).
Jeśli masz booleany w SQLite, możesz przekonwertować 1 i 0 oraz 1::boolean i 0::boolean (odpowiednio) lub zmienić kolumnę boolean na liczbę całkowitą w sekcji schematu zrzutu, a następnie naprawić je ręcznie wewnątrz PostgreSQL po imporcie.
Jeśli masz Bloby w SQLite, będziesz chciał dostosować schemat do użycia bytea. Prawdopodobnie będziesz trzeba wymieszać w niektórych decode połączenia, jak również . Pisanie kopiarki quick ' n ' dirty w ulubionym języku może być łatwiejsze niż zniekształcanie SQL, jeśli masz do czynienia z wieloma Plamami.
Jak zwykle, jeśli masz klucze obce to prawdopodobnie będziesz chciał zajrzeć do set constraints all deferred aby uniknąć problemów z wstawianiem kolejności, umieść polecenie wewnątrz pary BEGIN/COMMIT.
Podziękowania dla Nicolasa Rileya za notatki boolean, blob i constraints.
Jeśli mieć ` na swoim kodzie, jak generowane przez niektórych klientów SQLite3, trzeba je usunąć.
PostGRESQL również nie rozpoznaje kolumn unsigned, możesz chcieć to upuścić lub dodać niestandardowe ograniczenie, takie jak to:
CREATE TABLE tablename (
...
unsigned_column_name integer CHECK (unsigned_column_name > 0)
);
Podczas gdy SQLite domyślnie ustawia wartości null na '', PostgreSQL wymaga ich ustawienia jako NULL.
Składnia w pliku zrzutu SQLite wydaje się być w większości zgodna z PostgreSQL, więc możesz łatać kilka rzeczy i przesyłać je do psql. Importowanie a duży stos danych przez wstawki SQL może zająć trochę czasu, ale to zadziała.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-05-23 12:02:48
pgloader
Natknąłem się na ten post szukając sposobu na przekonwertowanie zrzutu SQLite do PostgreSQL. Mimo, że ten post ma zaakceptowaną odpowiedź (i dobrą na to +1), myślę, że dodanie tego jest ważne.
Zacząłem szukać rozwiązań tutaj i zdałem sobie sprawę, że szukam bardziej zautomatyzowanej metody. Zajrzałem na wiki docs:
Https://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL
I odkryte pgloader. Całkiem fajna aplikacja i jest stosunkowo łatwa w użyciu. Możesz przekonwertować płaski plik SQLITE do użytecznej bazy danych PostgreSQL. Zainstalowałem z *.deb i utworzyłem plik command taki jak ten w katalogu testowym:
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';
Jak stan docs. Następnie utworzyłem testdb z createdb:
createdb testdb
I run the pgloader polecenie tak:
pgloader command
A następnie podłączony do nowej bazy danych:
psql testdb
Po kilku zapytaniach, aby sprawdzić dane, wydaje się, że działało całkiem dobrze. Wiem, że gdybym próbował uruchomić jeden z tych skryptów lub wykonać krokową konwersję wspomnianą w niniejszym dokumencie, spędziłbym znacznie więcej czasu.
Aby udowodnić tę koncepcję, wrzuciłem to testdb i zaimportowałem do środowiska programistycznego na serwerze produkcyjnym, a dane przesyłane były ładnie.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-05-15 15:15:14
Napisałem skrypt do migracji sqlite3 do postgres. Nie obsługuje wszystkich tłumaczeń schematu/danych wymienionych w https://stackoverflow.com/a/4581921/1303625 , ale robi to, czego potrzebowałem. Mam nadzieję, że będzie to dobry punkt wyjścia dla innych.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-05-23 12:10:41
The sequel gem (Biblioteka Ruby) oferuje kopiowanie danych w różnych bazach danych: http://sequel.jeremyevans.net/rdoc/files/doc/bin_sequel_rdoc.html#label-Copy + bazy danych
W przypadku SQLite wygląda to tak:
sequel -C sqlite://db/production.sqlite3 postgres://user@localhost/db
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2015-10-26 01:08:32
Możesz użyć jednego linera, oto przykład z pomocą polecenia 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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-05-19 07:14:09
Pgloader działa cuda na konwersji bazy danych w sqlite do postgresql.
Oto przykład konwersji lokalnego SQLITEDB na zdalny PostgreSQL db:
Pgloader sqlite.db postgresql: / / username:hasło@Nazwa hosta/dbname
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-09-04 22:00:30