postgresql - SQLite 到 PostgreSQL 的仅数据传输(以保持 alembic 功能)

Question

关于 PostgreSQL 导入(以及具体的 SQLite->PostgreSQL 情况)已经有一些问题和答案。这个问题是关于一个特定的极端情况。

背景

我有一个用 python( Pyramid )编写并使用 alembic 来轻松迁移模式的现有生产中网络应用程序。由于数据库在异常高的写入负载下吱吱作响(可能是由于我自己代码的复杂性)，我决定迁移到 PostgreSQL。

数据迁移

有一些关于数据迁移的建议。最简单的涉及使用

sqlite3 my.db .dump > sqlitedumpfile.sql

然后导入

psql -d newpostgresdb < sqlitedumpfile.sql

这需要对 sqlitedumpfile 进行一些编辑。特别是，删除一些不兼容的操作，更改值(sqlite 将 bool 值表示为 0/1)等。最终以编程方式对我的数据进行操作太复杂了，手动处理的工作量太大(某些表有 20k 行左右) .

我最终选择的一个很好的数据迁移工具是 pgloader ，立即“起作用”。然而，对于这种类型的数据迁移来说，这是典型的，这暴露了我的数据库中的各种数据不一致，我必须在进行迁移之前从源头解决这些问题(特别是，删除非唯一列的外键，这在当时似乎是个好主意)方便连接和删除依赖于已删除的其他表中的行的孤立行的时间)。这些都解决了，我就可以了

pgloader my.db postgresql:///newpostgresdb

并适本地获取我的所有数据。

问题是什么？

pgloader 对数据工作得很好，但对表结构本身就不太好。这导致了三个问题:-

1. 我不得不创建一个包含大量更改的新 alembic 修订版(大部分与数据类型相关，但也有一些与问题 2 相关)。

2. 约束/索引名称不可靠(生成了唯一的数字名称)。实际上有 an option禁用它，这是一个问题，因为我需要一个可靠的升级路径，该路径可在生产中复制，而无需手动调整 alembic 代码。

3. 大多数主键的序列/自动增量都失败了。这破坏了我的网络应用程序，因为我无法为某些(不是全部)数据库添加新行。

相比之下，使用 alembic 重新创建一个空白数据库来维护模式效果很好，而无需更改我的任何 webapps 代码。但是 pgloader 默认覆盖现有表，所以这会让我无处可去，因为数据才是真正需要迁移的。

如何使用我已经定义(并且有效)的模式进行正确的数据迁移？

Answer 1

总而言之，最终起作用的是:-

1. 在 postgresql://newpostgresdb 中创建适当的数据库结构(我刚刚为此使用了 alembic upgrade head)

2. 使用 pgloader 将数据从 sqlite 移动到 postgresql 中的另一个数据库。正如问题中提到的，在这一步之前需要解决一些数据不一致的问题，但这与本问题本身无关。

   createdb tempdb

   pgloader my.db postgresql:///tempdb

3. 将数据转储到tempdb中使用 pg_dump

pg_dump -a -d tempdb > dumped_postgres_database

1. 编辑生成的转储以完成以下操作:-

   • SET session_replication_role = replica因为我的一些行是循环引用同一个表中的其他行

   • 删除 alembic_version表，因为我们正在为 alembic 重新启动一个新分支。

   • 重新生成任何序列，相当于 SELECT pg_catalog.setval('"table_colname_seq"', (select max(colname) from table));

2. 最后，psql可用于将数据加载到您的实际数据库中

psql -d newpostgresdb < dumped_postgres_database