database - 将 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 兼容转储文件？

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 中有 bool 值，那么您可以将 1 和 0 转换为 1::boolean 和 0:: bool 值(分别)，或者您可以在转储的模式部分将 bool 值列更改为整数，然后在导入后在 PostgreSQL 中手动修复它们。

如果您的 SQLite 中有 BLOB，那么您需要调整架构以使用 bytea。您可能需要混合一些 decode calls as well .如果您有很多 BLOB 需要处理，那么用您最喜欢的语言编写一个快速的'n'dirty 复制程序可能比修改 SQL 更容易。

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

感谢Nicolas Riley对于 bool 值、blob 和约束注释。

如果您的代码中有 `(由某些 SQLite3 客户端生成)，您需要将其删除。

PostGRESQL 也不识别unsigned 列，因此您可能想要删除它或添加一个自定义约束，例如:

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

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

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