database 将 SQLITE SQL 转储文件转换为 POSTGRESQL

Convert SQLITE SQL dump file to POSTGRESQL

提问by DevX

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 数据库在 POSTGRESQL 中进行开发。我刚刚用大量数据更新了我的本地数据库，需要将特定表传输到生产数据库。

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

基于 running 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;

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

如何将上述内容转换为可以导入生产服务器的 POSTGRESQL 兼容转储文件？

回答by mu is too short

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

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

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

If you want the idcolumn 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 AUTOINCREMENTcommands, so these need to be removed.

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

You'll also want to check for datetimecolumns in the SQLite schema and change them to timestampfor PostgreSQL (thanks to Clayfor pointing this out).

您还需要检查datetimeSQLite 模式中的列并将它们更改timestamp为 PostgreSQL（感谢Clay指出这一点）。

If you have booleans in your SQLite then you could convert 1and 0and 1::booleanand 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 中有布尔值，那么您可以转换1和0和1::boolean和0::boolean（分别），或者您可以将布尔列更改为转储模式部分中的整数，然后在导入后在 PostgreSQL 中手动修复它们。

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 decodecalls 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.

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

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

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

Thanks to Nicolas Rileyfor the boolean, blob, and constraints notes.

感谢Nicolas Riley的布尔值、blob 和约束注释。

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

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

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

PostGRESQL 也无法识别unsigned列，您可能想要删除它，或添加自定义约束，例如：

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

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

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 awhile but it'll work.

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

回答by nicorellius

pgloader

pgloader

I came across this post when searching for a way to convert an SQLite dump to PostgreSQL. Even though this post has an accepted answer (and a good one at that +1), I think adding this is important.

我在寻找将 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

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

and discovered pgloader. Pretty cool application and it's relatively easy to use. You can convert the flat SQLite file into a usable PostgreSQL database. I installed from the *.deband created a commandfile like this in a test directory:

并发现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';

like the docsstate. I then created a testdbwith createdb:

就像文档状态一样。然后我创建了一个testdb具有createdb：

createdb testdb

createdb testdb

I ran the pgloadercommand like this:

我pgloader像这样运行命令：

pgloader command

pgloader command

and then connected to the new database:

然后连接到新数据库：

psql testdb

psql testdb

After some queries to check the data, it appears it worked quite well. I know if I had tried to run one of these scripts or do the stepwise conversion mentioned herein, I would have spent much more time.

经过一些查询以检查数据后，它似乎运行良好。我知道如果我尝试运行这些脚本之一或进行此处提到的逐步转换，我会花费更多时间。

To prove the concept I dumped this testdband imported into a development environment on a production server and the data transferred over nicely.

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

回答by Earle Clubb

I wrote a script to do the sqlite3to postgresmigration. It doesn't handle all the schema/data translations mentioned in https://stackoverflow.com/a/4581921/1303625, but it does what I needed it to do. Hopefully it will be a good starting point for others.

我写了一个脚本来sqlite3进行postgres迁移。它不处理https://stackoverflow.com/a/4581921/1303625 中提到的所有模式/数据转换，但它完成了我需要它做的事情。希望这对其他人来说是一个很好的起点。

https://gist.github.com/2253099

https://gist.github.com/2253099

回答by lulalala

The sequel gem(a Ruby library) offers data copying across different databases: http://sequel.jeremyevans.net/rdoc/files/doc/bin_sequel_rdoc.html#label-Copy+Databases

在续集宝石（一个Ruby库）提供的数据在不同的数据库复制： http://sequel.jeremyevans.net/rdoc/files/doc/bin_sequel_rdoc.html#label-Copy+Databases

First install Ruby, then install the gem by running gem install sequel.

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

In case of sqlite, it would be like this: sequel -C sqlite://db/production.sqlite3 postgres://user@localhost/db

在 sqlite 的情况下，它会是这样的： sequel -C sqlite://db/production.sqlite3 postgres://user@localhost/db

回答by develCuy

You can use a one liner, here is an example with the help of sed command:

您可以使用单衬，这是一个借助 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 

回答by agomcas

I have tried editing/regexping the sqlite dump so PostgreSQL accepts it, it is tedious and prone to error.

我已经尝试编辑/正则表达式 sqlite 转储，以便 PostgreSQL 接受它，它很乏味并且容易出错。

What I got to work really fast:

我工作得非常快：

First recreate the schema on PostgreSQL without any data, either editing the dump or if you were using an ORM you may be lucky and it talks to both back-ends (sqlalchemy, peewee, ...).

首先在没有任何数据的情况下在 PostgreSQL 上重新创建模式，或者编辑转储，或者如果你使用的是 ORM，你可能很幸运，它会与两个后端（sqlalchemy、peewee 等）对话。

Then migrate the data using pandas. Suppose you have a table with a bool field (which is 0/1 in sqlite, but must be t/f in PostgreSQL)

然后使用 pandas 迁移数据。假设您有一个带有 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)

This works like a charm, is easy to write, read and debug each function, unlike (for me) the regular expressions.

这就像一个魅力，易于编写、阅读和调试每个函数，不像（对我而言）正则表达式。

Now you can try to load the resulting csv with PostgreSQL (even graphically with the admin tool), with the only caveat that you must load the tables with foreign keys after you have loaded the tables with the corresponding source keys. I did not have the case of a circular dependency, I guess you can suspend temporarily the key checking if that is the case.

现在，您可以尝试使用 PostgreSQL 加载生成的 csv（甚至使用管理工具以图形方式加载），唯一需要注意的是，您必须在使用相应的源键加载表后加载带有外键的表。我没有循环依赖的情况，如果是这种情况，我想您可以暂时暂停密钥检查。

回答by kouichi

pgloader work wonders on converting database in sqlite to postgresql.

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

Here's an example on converting a local sqlitedb to a remote PostgreSQL db:

这是将本地 sqlitedb 转换为远程 PostgreSQL db 的示例：

pgloader sqlite.dbpostgresql://username:password@hostname/dbname

pgloader sqlite.dbPostgreSQL的：//用户名：密码@主机名/ DBNAME