将 SQLITE SQL 转储文件转换为 POSTGRESQL [英] Convert SQLITE SQL dump file to POSTGRESQL

问题描述

我一直在使用 SQLITE 数据库进行开发，并在 POSTGRESQL 中进行生产.我刚刚用大量数据更新了本地数据库，需要将特定表传输到生产数据库.

I've been doing development using SQLITE database with production in POSTGRESQL. I just updated my local database with a huge amount of data and need to transfer a specific table to the production database.

基于运行sqlite database .dump >/the/path/to/sqlite-dumpfile.sql，SQLITE以如下格式输出一个表转储:

Based on running sqlite database .dump > /the/path/to/sqlite-dumpfile.sql, SQLITE outputs a table dump in the following format:

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 兼容转储文件?

How do I convert the above into a POSTGRESQL compatible dump file that I can import into my production server?

推荐答案

您应该能够将该转储文件直接提供给 psql:

You should be able to feed that dump file straight into psql:

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

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

If you want the id column to "auto increment" then change its type from "int" to "serial" in the table creation line. PostgreSQL will then attach a sequence to that column so that INSERTs with NULL ids will be automatically assigned the next available value. PostgreSQL will also not recognize AUTOINCREMENT commands, so these need to be removed.

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

You'll also want to check for datetime columns in the SQLite schema and change them to timestamp for PostgreSQL. (Thanks to Clay for pointing this out.)

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

If you have booleans in your SQLite then you could convert 1 and 0 to 1::boolean and 0::boolean (respectively) or you could change the boolean column to an integer in the schema section of the dump and then fix them up by hand inside PostgreSQL after the import.

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

If you have BLOBs in your SQLite then you'll want to adjust the schema to use bytea. You'll probably need to mix in some decode calls as well. Writing a quick'n'dirty copier in your favorite language might be easier than mangling the SQL if you a lot of BLOBs to deal with though.

像往常一样，如果您有外键，那么您可能需要查看 设置所有延迟的约束以避免插入顺序问题，将命令放置在 BEGIN/COMMIT 对中.

As usual, if you have foreign keys then you'll probably want to look into set constraints all deferred to avoid insert ordering problems, placing the command inside the BEGIN/COMMIT pair.

感谢 Nicolas Riley 的布尔值、blob 和约束说明.

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

如果您的代码上有 `(由某些 SQLite3 客户端生成)，则需要删除它们.

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

PostGRESQL 也无法识别 unsigned 列，因此您可能希望删除它或添加自定义约束，例如:

PostGRESQL also doesn't recognize unsigned columns, so you might want to drop that or add a custom-made constraint such as this:

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

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

While SQLite defaults null values to '', PostgreSQL requires them to be set as NULL.

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

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.

这篇关于将 SQLITE SQL 转储文件转换为 POSTGRESQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！