如何将SQLite SQL转储文件转换为PostgreSQL？

Question

开发时我使用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兼容的转储文件？

- DevX

---

那个命令在我尝试将 sqlite 改为 sqlite3 后才生效。 - Celal Ergün

Answer 1

您应该能够直接将那个转储文件输入到psql中：

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

如果您希望"id"列实现"自动递增"，请在表创建行中将其类型从"int"更改为"serial"。PostgreSQL将会为该列附加一个序列，以便将NULL id的INSERT自动分配下一个可用值。PostgreSQL也不会识别"AUTOINCREMENT"命令，因此需要将其删除。

您还需要检查SQLite架构中的"datetime"列，并将其更改为PostgreSQL的"timestamp"。(感谢Clay指出这一点。)

如果您的SQLite中有布尔值，则可以将1和0转换为1::boolean和0::boolean(分别)，或者您可以在转储的模式部分中将布尔列更改为整数，然后在导入后手动修复它们。

如果您的SQLite中有BLOBs，则需要调整模式以使用"bytea"。您可能还需要混合一些decode calls as well。如果您要处理大量BLOBs，那么编写一个快速且简单的复制程序可能比搞乱SQL更容易。

通常来说，如果你有外键，那么你可能需要考虑使用 set constraints all deferred 来避免插入顺序问题，在 BEGIN/COMMIT 对之间放置该命令。

感谢Nicolas Riley提供布尔、blob和约束注释。

如果你的代码中有 ` ，如一些SQLite3客户端生成的代码，则需要将它们移除。

PostGRESQL也不识别 unsigned 列，所以你可能想要删除该列或添加一个自定义约束，例如：

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

虽然SQLite默认将null值设置为''，但PostgreSQL需要将其设置为NULL。

在SQLite转储文件中的语法似乎与PostgreSQL大多兼容，因此您可以修补一些内容并将其提供给psql。通过SQL INSERT导入大量数据可能需要一段时间，但它会起作用。

- mu is too short

---

不，你想保留这笔交易以避免一些额外开销。 - Peter Eisentraut

иҝҷеҫҲжңүж•ҲгҖӮжҲ‘иҝҳиҰҒжіЁж„ҸпјҢеҰӮжһңдҪ йңҖиҰҒиҝҒ移SQLiteзҡ„datetimeеҲ—пјҢдҪ еҝ…йЎ»е°Ҷе®ғ们жӣҙж”№дёәPostgresзҡ„timestampгҖӮ - Clay

我遇到了几个问题：将BLOB更改为BYTEA(https://dev59.com/InA75IYBdhLWcg3wy8Ui)，将`BOOLEAN`列的0/1更改为'0'/'1'，以及延迟约束（`DEFERRABLE` / SET CONSTRAINTS ALL DEFERRED）。 - Nicholas Riley

@NicholasRiley：谢谢你。由于这已经成为一个团队合作，所以我将其交给了社区维基，公平是公正的。 - mu is too short

你应该能够直接将那个转储文件导入到psql中。但是，这并不起作用。转储的sqlite SQL文件需要进行重大转换，而你的评论暗示了这一点，但并没有完全解释清楚。难道你希望OP手动编辑一个占用几个G的SQL文件吗？ - Cerin

Answer 2

pgloader

我在寻找将SQLite转换为PostgreSQL的方法时发现了这篇文章。虽然这篇文章已经有了一个被接受的答案（而且是个好答案，+1），但我认为添加这个信息很重要。

我开始研究这里的解决方案，意识到我正在寻找更自动化的方法。我查阅了维基文档：

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

并发现了 pgloader。这是一个相当不错的应用程序，并且使用起来相对容易。你可以将平面的SQLite文件转换为可用的PostgreSQL数据库。我从*.deb安装，并在测试目录中创建了一个类似于以下命令的文件：

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的数据库：

然后我运行以下pgloader命令：

pgloader command

接着连接到新的数据库：

psql testdb

查询数据后，看起来转换非常成功。如果我尝试运行这些脚本或按步骤进行转换，会花费更多时间。

为了证明概念，我将此testdb导出并导入到生产服务器上的开发环境中，数据成功传输。

- nicorellius

---

请注意，（仍在支持的）Ubuntu发行版可能已经过时 - v2.x.y已被弃用且实际上无法使用。v3.2.x可能可用，但建议使用v3.2.3版本。我从最新版本中获取了v3.2.3并使用“sudo dpkg -i <.deb文件名>”进行了安装，它在依赖项方面没有问题。 - silpol

我同意@silpol的观点 - 一定要下载最新的稳定版并使用您喜欢的包管理器进行安装; 对于“command”文件，这只是一个名为“command”的无扩展名的文本文件（即文件名末尾不需要.txt），您不需要在尖括号中放置文件名; 我必须更改psql数据库的search_path才能看到我的数据; pgloader运行良好，使我省了很多麻烦。 - BenKoshy

是的，当我遇到这个问题时，我很苦恼，但那个工具让它变得非常容易...有时候事情就会顺利解决，不是吗？ - nicorellius

很遗憾，它在Windows上无法运行。 - sveri

Answer 3

Sequel gem是一个Ruby库，可实现不同数据库之间的数据拷贝： http://sequel.jeremyevans.net/rdoc/files/doc/bin_sequel_rdoc.html#label-Copy+Databases

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

以sqlite为例： sequel -C sqlite://db/production.sqlite3 postgres://user@localhost/db

- lulalala

---

很棒的解决方案。比折腾pgloader容易得多。 - michaeldever

绝对的，pgloader很混乱，GC似乎会在大型数据库上崩溃：https://github.com/dimitri/pgloader/issues/962 - hasufell

请随意在 https://dev59.com/8G025IYBdhLWcg3wNTAV#64222183 上发布您的答案，这是我复制您的答案的地方。然后@我，如果您想要它的声望，我将撤销我的答案。 - Felix

@Felix 谢谢！你可以把功劳记在自己名下。能否交换一下数据库引用的顺序（因为它需要从PG转到SQLite），还有在我的ID中再加一个“la”。答案可能也不太有帮助，因为它要求他们在开发机器上安装PG，而此时他们只需使用PG进行开发即可。 - lulalala

@lulalala 谢谢。我做了。但是关于理由，我不同意。例如，他们可以在 Linux 机器上转换数据库，然后将其复制到开发机器上（作为 sqlite db 文件）。但无论如何，总的来说这是个坏主意 :) 但是 Sequel 在这种困难情况下帮了我的大忙。 - Felix

续集宝石运作得非常顺利。 - rahul

Answer 4

你可以使用一行命令，这里有一个用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

- develCuy

---

没有LONG类型的替代品，例如。 - yetanothercoder

可以再添加一项： sed -e 's/DATETIME/TIMESTAMP/g'。 - silpol

sed -e 's/TINYINT(1)/SMALLINT/g' -- 并且要查看所有数据类型的比较，请参见 https://dev59.com/T3I-5IYBdhLWcg3wQWCc - Purplejacket

用;替换' | sed -e ' :) - AstraSerg

发生错误 错误信息：列“time”的类型为没有时区的时间戳，但表达式类型为bigint 位于第1行: ...o":null,"notNullable":false,"unsigned":true}]}]}',1673471190.. - dasfacc

Answer 5

我编写了一个脚本，用于进行从 sqlite3 到 postgres 的迁移。它不能处理https://dev59.com/wm455IYBdhLWcg3wAvbQ#4581921中提到的所有架构/数据转换，但它实现了我需要的功能。希望它能成为其他人的好的起点。 请查看https://gist.github.com/2253099获取更多信息。

- Earle Clubb

---

这个很好用！我已经fork了这个Gist并添加了一些见解作为评论：https://gist.github.com/bittner/7368128 - Peterino

Answer 6

尝试以下步骤...

步骤01：将sqlite数据库转换为json格式

python3 manage.py dumpdata > data.json

步骤二：创建不需要迁移的表格。

python3 manage.py migrate --run-syncdb

步骤三：打开 Django shell。然后排除 contentype 数据。

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

步骤04：加载数据

python3 manage.py loaddata data.json

- Kusal Thiwanka

---

处理大型GB级JSON文件需要大量的内存。 - giveJob

Answer 7

pgloader在将sqlite数据库转换为postgresql方面表现出色。

以下是将本地sqlitedb转换为远程PostgreSQL数据库的示例：

使用pgloader sqlite.db postgresql://用户名:密码@主机名/数据库名

- kouichi

---

Pgloader非常有bug且不可靠。它会立即崩溃并显示错误信息“KABOOM！控制堆栈耗尽（没有更多的函数调用帧空间）”。 - Cerin

Answer 8

我尝试过编辑/正则表达式处理sqlite转储文件，以便PostgreSQL可以接受它，但这很繁琐且容易出错。

我成功的方法是：

首先在PostgreSQL上重新创建模式而不包含任何数据，可以通过编辑转储文件，或者如果您使用ORM，则可能会有运气，因为它可以连接到两个后端（如sqlalchemy、peewee等）。

然后使用pandas迁移数据。假设您有一个带有布尔字段的表（在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文件，唯一要注意的是，必须在先加载源键对应的表格之后再加载外键对应的表格。如果出现循环依赖的情况，可以暂时挂起键检查。

- agomcas