如何将 postgres 数据库转换为 SQLite？

Question

我们正在开发一个网站，当我们在本地开发时（其中一个来自Windows），我们使用sqlite3，但在服务器（linux）上我们使用postgres。我们希望能够将生产数据库导入到我们的开发过程中，所以我想知道是否有一种方法可以将 postgres 数据库转储转换为 sqlite3 可以理解的内容（只需将 postgres 转储的 SQL 提供给它即可，许多错误）。或者在 Windows 上安装 postgres 会更容易吗？谢谢。

Answer 1

2024 更新 - 通过更新 SQL 转储来执行此操作可能非常复杂，也可能非常简单，具体取决于您使用的 Postgres 特定功能（例如 hstore 列）。

对于一个简单的用例，我发现 此博客条目，它将指导您执行以下步骤：

1. 创建 PostgreSQL 的转储数据库。

    ssh -C [电子邮件受保护] pg_dump --仅数据--插入YOUR_DB_NAME >转储.sql
   

2. 删除/修改转储。

   1. 删除以 SET 开头的行
   2. 删除以 SELECT pg_catalog.setval 开头的行
   3. 将“t”替换为 true
   4. 将“f”替换为 false

3. 添加 BEGIN; 作为第一行，添加 END; 作为最后一行

4. 重新创建一个空的开发数据库。 捆绑执行 rake db:migrate

5. 导入转储。

   <前><代码> sqlite3 db/development.sqlite3
   sqlite>从 schema_migrations 中删除；
   sqlite> .读取转储.sql

当然，通过 ssh 连接并使用 rake 创建新数据库是可选的。

如果您想对此进行扩展，请随时在此处添加更多详细信息，我将编辑答案。

Answer 2

第 1 步：转储数据库结构和数据

pg_dump --create --inserts -f myPgDump.sql \
   -d myDatabaseName -U myUserName -W myPassword

第 2 步：从 myPgDump.sql 中删除除 CREATE TABLES 和 INSERT 语句之外的所有内容（使用文本编辑器）

第 3 步：初始化 SQLite 数据库，传递 Postgres 转储的结构和数据

sqlite3 myNewSQLiteDB.db -init myPgDump.sql

第 4 步：使用数据库;)

Answer 3

您可以使用 pg2sqlite 将 pg_dump 输出转换为 sqlite。

#  Making dump
pg_dump -h host -U user -f database.dump database

#  Making sqlite database
pg2sqlite -d database.dump -o sqlite.db

pg2sqlite 不支持模式，如果转储包含模式，则需要将其删除。您可以使用这个脚本：

# sed 's/<schema name>\.//' -i  database.dump
sed 's/public\.//' -i  database.dump
pg2sqlite -d database.dump -o sqlite.db

Answer 4

尽管这里有很多非常有用的答案，但我只想将其标记为已回答。我们最终采纳了评论的建议：

我只是将你的开发环境切换到 PostgreSQL，在一个数据库（尤其是像 SQLite 这样宽松和宽容的数据库）上进行开发，但在另一个数据库（尤其是像 PostgreSQL 这样严格的数据库）上部署通常会导致恼怒和咒骂。 。 –
@mu 太短了

为了回应 mu 的回应，不要这样做..不要这样做..不要这样做。开发和部署在同一件事上。否则的话，这是不好的工程实践。 – @Kuberchaun

所以我们刚刚在我们的开发机器上安装了 postgres。上手很容易，工作也很顺利。

Answer 5

如果需要更自动化的解决方案，这里是一个好的开始：

#!/bin/bash

$table_name=TABLENAMEHERE

PGPASSWORD="PASSWORD" /usr/bin/pg_dump --file "results_dump.sql" --host "yourhost.com" --username "username" --no-password --verbose --format=p --create --clean --disable-dollar-quoting --inserts --column-inserts --table "public.${table_name}" "memseq"

# Some clean ups
perl -0777 -i.original -pe "s/.+?(INSERT)/\1/is" results_dump.sql
perl -0777 -i.original -pe "s/--.+//is" results_dump.sql

# Remove public. prefix from table name
sed -i "s/public.${table_name}/${table_name}/g" results_dump.sql

# fix binary blobs
sed -i "s/'\\\\x/x'/g" results_dump.sql

# use transactions to make it faster
echo 'BEGIN;' | cat - results_dump.sql > temp && mv temp results_dump.sql
echo 'END;' >> results_dump.sql

# clean the current table 
sqlite3 results.sqlite "DELETE FROM ${table_name};"

# finally apply changes
sqlite3 results.sqlite3 < results_dump.sql && \
rm results_dump.sql && \
rm results_dump.sql.original

Answer 6

当我遇到同样的问题时，我在互联网上没有找到任何有用的建议。我的源 PostgreSQL 数据库有非常复杂的架构。
您只需从数据库文件中手动删除除创建表之外的所有内容
更多详细信息 - 此处

Answer 7

使用 Taps gem 对我来说非常容易，如下所述：
http://railscasts.com/episodes/342-migration-to-postgresql

我已经开始在我的 Mac 上使用 Postgres.app（无需安装，将应用程序放在应用程序目录中，尽管可能需要按照文档中的说明向 PATH 环境变量添加一行），并将 Induction.app 作为用于查看/查询数据库的 GUI 工具。