将 SQLITE SQL 转储文件转换为 POSTGRESSQL

我一直在使用 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 兼容转储文件？

107990 次浏览
小开
最佳答案

您应该能够将这个转储文件直接输入到 psql:

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

如果您希望 id列“自动增量”，那么在表创建行中将其类型从“ int”更改为“ Series”。然后 PostgreSQL 将向该列附加一个序列，这样具有 NULL id 的 INSERT 将自动分配下一个可用值。PostgreSQL 也不能识别 AUTOINCREMENT命令，因此需要删除这些命令。

您还需要检查 SQLite 模式中的 datetime列，并将它们更改为用于 PostgreSQL 的 timestamp。(感谢 克雷指出这一点。)

如果你的 SQLite 中有布尔值，那么你可以将 1和 0分别转换成 1::boolean和 0::boolean，或者你可以在转储的 schema 部分中将布尔值列转换成一个整数，然后在导入之后在 PostgreSQL 中手动修复它们。

如果 SQLite 中有 BLOB，则需要调整模式以使用 bytea。你可能需要加入一些 decode也在呼叫。如果需要处理大量的 BLOB，那么用自己喜欢的语言编写一个快速的、不干净的复印机可能比处理 SQL 更容易。

通常，如果您有外键，那么您可能需要查看 set constraints all deferred以避免插入排序问题，将命令放在 BEGIN/COMMIT 对中。

Thanks to Nicolas Riley for the boolean, blob, and constraints notes.

If you have ` on your code, as generated by some SQLite3 clients, you need to remove them.

PostGRESQL 也不能识别 unsigned列，所以您可能需要删除它，或者添加一个定制的约束，比如:

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

虽然 SQLite 将空值默认为 ''，但 PostgreSQL 要求将它们设置为 NULL。

The syntax in the SQLite dump file appears to be mostly compatible with PostgreSQL so you can patch a few things and feed it to psql. Importing a big pile of data through SQL INSERTs might take a while but it'll work.

小开

我编写了一个脚本来完成从 sqlite3到 postgres的迁移。它不能处理 https://stackoverflow.com/a/4581921/1303625中提到的所有模式/数据转换，但是它可以完成我需要它完成的任务。希望这对其他人来说是一个好的开始。

Https://gist.github.com/2253099

小开

您可以使用一行程序，下面是一个借助 sed 命令的示例:

sqlite3 mjsqlite.db .dump | sed -e 's/INTEGER PRIMARY KEY AUTOINCREMENT/SERIAL PRIMARY KEY/g;s/PRAGMA foreign_keys=OFF;//;s/unsigned big int/BIGINT/g;s/UNSIGNED BIG INT/BIGINT/g;s/BIG INT/BIGINT/g;s/UNSIGNED INT(10)/BIGINT/g;s/BOOLEAN/SMALLINT/g;s/boolean/SMALLINT/g;s/UNSIGNED BIG INT/INTEGER/g;s/INT(3)/INT2/g;s/DATETIME/TIMESTAMP/g' | psql mypqdb mypguser
小开

pgloader

我是在寻找将 SQLite 转储转换为 PostgreSQL 的方法时看到这篇文章的。尽管这篇文章已经有了一个公认的答案(而且是 + 1的好答案) ，我还是觉得加上这个很重要。

I started looking into the solutions here and realized that I was looking for a more automated method. I looked up the wiki docs:

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

然后我用 createdb创建了一个 testdb:

createdb testdb

I ran the pgloader command like this:

pgloader command

and then connected to the new database:

psql testdb

经过一些查询以检查数据，似乎它工作得相当不错。我知道如果我尝试运行这些脚本之一或者进行本文中提到的逐步转换，我会花费更多的时间。

为了证明这个概念，我转储了这个 testdb，并将其导入到生产服务器上的开发环境中，并很好地传输了数据。

小开

续集宝石(一个 Ruby 库)提供跨不同数据库的数据复制: Http://sequel.jeremyevans.net/rdoc/files/doc/bin_sequel_rdoc.html#label-copy+databases

首先安装 Ruby，然后运行 gem install sequel安装 gem。

对于 sqlite，它是这样的: sequel -C sqlite://db/production.sqlite3 postgres://user@localhost/db

小开

Pgliader 在将 sqlite 中的数据库转换为 postgreql 方面创造了奇迹。

下面是一个将本地 sqlitedb 转换为远程 PostgreSQL db 的示例:

Pgliader Sqlite.db postgreql://username: password@主机名/dbname

小开

我曾经尝试过编辑/重新指定 sqlite 转储，所以 PostgreSQL 接受它，这很乏味，而且容易出错。

What I got to work really fast:

首先在没有任何数据的情况下在 PostgreSQL 上重新创建模式，要么编辑转储文件，要么使用 ORM，你可能很幸运，因为它可以同时与两个后端(sql 炼金术，peewee，...)进行通信。

然后使用熊猫迁移数据。假设您有一个带有 bool 字段的表(在 sqlite 中是0/1，但在 PostgreSQL 中必须是 t/f)

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)

这就像一个魅力，很容易编写，读取和调试每个函数，不像(对我来说)正则表达式。

现在，您可以尝试用 PostgreSQL 加载生成的 csv (甚至使用管理工具进行图形化加载) ，唯一需要注意的是，在加载具有相应源键的表之后，必须使用外键加载表。我没有循环依赖的情况，我想你可以暂时挂起的关键检查，如果是这种情况。

小开

试试这些步骤..。

步骤01: 将 sqlite db 转储到 json

python3 manage.py dumpdata > data.json

步骤02: 创建没有迁移的表

python3 manage.py migrate --run-syncdb

步骤03: 打开 django shell。然后排除内容类型数据

python3 manage.py shell
from django.contrib.contenttypes.models import ContentType
ContentType.objects.all().delete()
quit()

步骤04: 加载数据

python3 manage.py loaddata data.json

提交答案