Преобразование файла дампа SQLITE SQL в POSTGRESQL

Я занимаюсь разработкой с использованием базы данных SQLITE с производством в POSTGRESQL. Я просто обновил свою локальную базу данных с огромным объемом данных и вам нужно перенести определенную таблицу в производственную базу данных.

Основываясь на запуске sqlite database .dump > /the/path/to/sqlite-dumpfile.sql, SQLITE выводит дамп таблицы в следующем формате:

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;

Как мне преобразовать вышеуказанное в файл дампа, совместимый с POSTGRESQL, который я могу импортировать на свой производственный сервер?

Ответ 1

Вы должны иметь возможность подавать этот файл дампа прямо в psql:

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

Если вы хотите, чтобы столбец id имел значение "auto increment", измените его тип с "int" на "serial" в строке создания таблицы. PostgreSQL затем присоединяет последовательность к этому столбцу, так что INSERT с идентификаторами NULL будет автоматически назначено следующее доступное значение. PostgreSQL также не распознает команды AUTOINCREMENT, поэтому их необходимо удалить.

Вы также захотите проверить столбцы datetime в схеме SQLite и изменить их на timestamp для PostgreSQL (благодаря Clay для указания это).

Если у вас есть booleans в вашем SQLite, вы можете конвертировать 1 и 0 и 1::boolean и 0::boolean (соответственно), или вы можете изменить булевский столбец на целое число в разделе схемы дампа и затем закрепите их вручную в PostgreSQL после импорта.

Если у вас есть BLOB в вашем SQLite, вам нужно настроить схему для использования bytea. Вероятно, вам нужно будет перемешать несколько вызовов decode. Написание quick'n'dirty copier на вашем любимом языке может быть проще, чем манипулировать SQL, если вам нужно много BLOB файлов.

Как обычно, если у вас есть внешние ключи, вы, вероятно, захотите заглянуть в set constraints all deferred, чтобы избежать проблем с упорядочением порядка размещения, команды внутри пары BEGIN/COMMIT.

Благодаря Nicolas Riley для заметок boolean, blob и constraints.

Если у вас есть ` в вашем коде, созданный некоторыми клиентами SQLite3, вам необходимо удалить их.

PostGRESQL также не распознает столбцы unsigned, вы можете отказаться от этого или добавить настраиваемое ограничение, такое как:

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

В то время как значения SQLite по умолчанию равны нулю для '', PostgreSQL требует, чтобы они были установлены как NULL.

Синтаксис в файле дампа SQLite, по-видимому, в основном совместим с PostgreSQL, поэтому вы можете исправлять несколько вещей и передавать их на psql. Импорт большой кучи данных через SQL INSERT может занять некоторое время, но он будет работать.

Ответ 2

`pgloader`

Я наткнулся на этот пост, когда искал способ конвертировать дамп SQLite в PostgreSQL. Хотя этот пост имеет приемлемый ответ (и хороший ответ на этот вопрос +1), я думаю, что добавить это важно.

Я начал искать решения здесь и понял, что я искал более автоматизированный метод. Я посмотрел вики-документы:

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

и обнаружил pgloader. Довольно крутое приложение и оно относительно простое в использовании. Вы можете преобразовать плоский файл SQLite в удобную базу данных PostgreSQL. Я установил из *.deb и создал такой command файл в тестовой директории:

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

как государство документов. Затем я создал testdb с помощью createdb:

createdb testdb

Я запустил команду pgloader следующим образом:

pgloader command

и затем подключается к новой базе данных:

psql testdb

После некоторых запросов, чтобы проверить данные, кажется, это работало довольно хорошо. Я знаю, если бы я попытался запустить один из этих сценариев или выполнить пошаговое преобразование, упомянутое здесь, я бы потратил гораздо больше времени.

Чтобы подтвердить концепцию, я сбросил этот testdb и импортировал его в среду разработки на производственном сервере, и данные были перенесены.

Ответ 3

Я написал script для переноса sqlite3 в postgres. Он не обрабатывает все переводы схем/данных, упомянутые в fooobar.com/questions/81318/..., но он делает то, что мне нужно для этого. Надеюсь, это станет хорошей отправной точкой для других.

https://gist.github.com/2253099

Ответ 4

продолжение gem (библиотека Ruby) предлагает копирование данных в разных базах данных: http://sequel.jeremyevans.net/rdoc/files/doc/bin_sequel_rdoc.html#label-Copy+Databases

В случае sqlite это будет выглядеть так: sequel -C sqlite://db/production.sqlite3 postgres://[email protected]/db

Ответ 5

Вы можете использовать один лайнер, вот пример с помощью команды 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

Ответ 6

Я попытался отредактировать/отредактировать дамп sqlite, чтобы PostgreSQL принял его, это утомительно и подвержено ошибкам.

Что я получил на работу очень быстро:

Сначала воссоздайте схему в PostgreSQL без каких-либо данных, либо отредактировав дамп, либо, если вы использовали ORM, вам может повезти, и он общается с обоими бэкэндами (sqlalchemy, peewee,...).

Затем перенесите данные с помощью панд. Предположим, у вас есть таблица с полем bool (0/1 в sqlite, но должно быть t/f в 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)

Это работает как шарм, легко писать, читать и отлаживать каждую функцию, в отличие от (для меня) регулярных выражений.

Теперь вы можете попытаться загрузить получившийся csv с PostgreSQL (даже графически с помощью инструмента администратора), с единственной оговоркой, что вы должны загружать таблицы с внешними ключами после того, как вы загрузили таблицы с соответствующими исходными ключами. У меня не было случая циклической зависимости, я думаю, вы можете временно приостановить проверку ключа, если это так.

Ответ 7

pgloader творит чудеса по конвертации базы данных в sqlite в postgresql.

Вот пример преобразования локальной sqlitedb в удаленную базу данных PostgreSQL:

pgloader sqlite.db postgresql://имя пользователя: пароль @имя хоста/имя_базы