Zajmuję się programowaniem przy użyciu bazy danych SQLITE z produkcją w POSTGRESQL. Właśnie zaktualizowałem moją lokalną bazę danych o ogromną ilość danych i muszę przenieść konkretną tabelę do produkcyjnej bazy danych.

Bazując na uruchomieniu sqlite database .dump > /the/path/to/sqlite-dumpfile.sql, SQLITE generuje 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 na plik zrzutu zgodny z POSTGRESQL, który mogę zaimportować na mój serwer produkcyjny?

Powinieneś być w stanie przesłać ten plik zrzutu bezpośrednio do psql:

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

Jeśli chcesz, aby idkolumna była „automatycznie zwiększana”, zmień jej typ z „int” na „serial” w linii tworzenia tabeli. PostgreSQL dołączy wtedy sekwencję do tej kolumny, tak aby wstawkom INSERT z identyfikatorami NULL automatycznie przypisywana była następna dostępna wartość. PostgreSQL również nie rozpoznaje AUTOINCREMENTpoleceń, więc należy je usunąć.

Będziesz także chciał sprawdzić datetimekolumny w schemacie SQLite i zmienić je timestampna PostgreSQL. (Dzięki Clayowi za wskazanie tego).

Jeśli masz wartości logiczne w swoim SQLite, możesz przekonwertować 1i 0na 1::booleani 0::boolean(odpowiednio) lub możesz zmienić kolumnę boolowską na liczbę całkowitą w sekcji schematu zrzutu, a następnie naprawić je ręcznie w PostgreSQL po imporcie.

Jeśli masz obiekty BLOB w swoim SQLite, będziesz chciał dostosować schemat do użycia bytea. Prawdopodobnie będziesz musiał również włączyć się do niektórych decoderozmów . Napisanie szybkiej i brudnej kserokopiarki w swoim ulubionym języku może być łatwiejsze niż manipulowanie kodem SQL, jeśli jednak masz do czynienia z wieloma BLOBami.

Jak zwykle, jeśli masz klucze obce, prawdopodobnie będziesz chciał zajrzeć, set constraints all deferredaby uniknąć problemów z kolejnością wstawiania, umieszczając polecenie wewnątrz pary BEGIN / COMMIT.

Podziękowania dla Nicolasa Rileya za notatki logiczne, blob i ograniczenia.

Jeśli masz `na swoim kodzie wygenerowanym przez niektórych klientów SQLite3, musisz je usunąć.

PostGRESQL również nie rozpoznaje unsignedkolumn, więc możesz to porzucić lub dodać niestandardowe ograniczenie, takie jak to:

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

Podczas gdy SQLite domyślnie przyjmuje wartości null na '', PostgreSQL wymaga, aby były ustawione jako NULL.

Składnia w pliku zrzutu SQLite wydaje się być w większości zgodna z PostgreSQL, więc możesz załatać kilka rzeczy i przekazać je psql. Importowanie dużej ilości danych przez SQL INSERT może zająć trochę czasu, ale zadziała.

pgloader

Natknąłem się na ten post, szukając sposobu na przekonwertowanie zrzutu SQLite na PostgreSQL. Mimo że ten post ma akceptowaną odpowiedź (i dobrą przy tym +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. Sprawdziłem dokumenty wiki:

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

i odkrył pgloader. Całkiem fajna aplikacja i jest stosunkowo łatwa w użyciu. Możesz przekonwertować płaski plik SQLite na użyteczną bazę danych PostgreSQL. Zainstalowałem z *.debi utworzyłem taki commandplik 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 w przypadku dokumentów . Następnie utworzyłem testdbz createdb:

createdb testdb

Uruchomiłem pgloaderpolecenie w ten sposób:

pgloader command

a następnie połączony z nową bazą danych:

psql testdb

Po kilku zapytaniach w celu sprawdzenia danych wygląda na to, że działało całkiem dobrze. Wiem, że gdybym próbował uruchomić jeden z tych skryptów lub wykonać wspomnianą tutaj konwersję krokową, spędziłbym znacznie więcej czasu.

Aby udowodnić tę koncepcję, porzuciłem to testdbi zaimportowałem do środowiska programistycznego na serwerze produkcyjnym, a dane zostały ładnie przesłane.

Napisałem skrypt sqlite3do postgresmigracji. Nie obsługuje wszystkich tłumaczeń schematów / danych wymienionych na https://stackoverflow.com/a/4581921/1303625 , ale robi to, czego potrzebowałem. Miejmy nadzieję, że będzie to dobry punkt wyjścia dla innych.

https://gist.github.com/2253099

Gem sequel (biblioteka Ruby) oferuje Kopiowanie danych między różnymi bazami danych: http://sequel.jeremyevans.net/rdoc/files/doc/bin_sequel_rdoc.html#label-Copy+Databases

Najpierw zainstaluj Rubiego, a następnie zainstaluj gem, uruchamiając gem install sequel.

W przypadku sqlite wyglądałoby to tak: sequel -C sqlite://db/production.sqlite3 postgres://user@localhost/db

Możesz użyć one 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 

Próbowałem edytować / regexping zrzutu sqlite, więc PostgreSQL je akceptuje, jest to żmudne i podatne na błędy.

Co mam do pracy naprawdę szybko:

Najpierw odtwórz schemat na PostgreSQL bez żadnych danych, edytując zrzut lub jeśli używasz ORM, możesz mieć szczęście i komunikuje się z obydwoma back-endami (sqlalchemy, peewee, ...).

Następnie przeprowadź migrację danych za pomocą pand. Załóżmy, że masz tabelę z polem bool (które ma wartość 0/1 w sqlite, ale musi być t / f w 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)

To działa jak urok, jest łatwe do napisania, odczytania i debugowania każdej funkcji, w przeciwieństwie do (dla mnie) wyrażeń regularnych.

Teraz możesz spróbować załadować wynikowy plik csv za pomocą PostgreSQL (nawet graficznie za pomocą narzędzia administratora), z jedynym zastrzeżeniem, że musisz załadować tabele kluczami obcymi po załadowaniu tabel z odpowiednimi kluczami źródłowymi. Nie miałem przypadku zależności cyklicznej, myślę, że można chwilowo zawiesić klucz sprawdzając, czy tak jest.

pgloader działa cuda podczas konwersji bazy danych w sqlite do postgresql.

Oto przykład konwersji lokalnej sqlitedb na zdalną bazę danych PostgreSQL:

pgloader sqlite.db PostgreSQL: // nazwa_użytkownika : hasło @ nazwa hosta / nazwa_bd