使用AWS DMS迁移后PostgreSQL主键序列丢失 [英] PostgreSQL Primary Key sequence lost after migration using AWS DMS

问题描述

我最近使用AWS Migration Service将自托管的Postgres数据库迁移到AWS RDS.
Postgres版本:10.6

I recently migrated a self hosted Postgres database to AWS RDS using the AWS Migration Service.
Postgres Version: 10.6

我注意到我的所有主键都不再设置为序列"，并且当我尝试手动添加序列时，它从1开始而不是继续进行已设置的计数.

I have noticed that all of my primary keys are no longer set to "sequence", and when I attempt to manually add a sequence it starts at 1 instead of continuing the count that is already set.

我在数据库中使用Rails，因此我的SQL技能很低.我通常可以找到解决插入和更新问题的方法，但这不是我有很多经验的领域.

I use Rails with the database, so my sql skills are pretty low. I can generally find my way around inserts and updates, but this is not a realm I have much experience in.

我的问题分为两部分:

1. 如何修复单个表?在继续之前，我想了解并测试我在做什么.
2. 有没有一种方法可以将此修复程序应用于我拥有的每个表，而无需手动修改每个表?

推荐答案

@a_horse_with_no_name指出正确的方向并与AWS聊天后，我至少可以回答我自己的问题，至少在使用AWS Database Migration Service的情况下(DMS).

After @a_horse_with_no_name pointed me in the right direction and chatting with AWS I am able to answer my own question, at least if you are using AWS Database Migration Service (DMS).

问题在于，DMS仅关注数据本身，而不关注模式(在我看来，这是主要的疏忽，特别是如果您使用相同的数据库技术，但这是另一个问题).因此，架构本身不会迁移.该文档并没有真正弄清楚这一点.

The problem is, DMS only focuses on the data itself and not really the schema (which to me seems like a major oversight, especially if your using the same database technology but that is another issue). So the schema itself is not migrated. The documentation does not really make this clear.

要解决此问题:

1. 停止(如果仍然存在)现有的AWS DMS迁移
2. 删除现有的已迁移数据库，并创建一个新的空模式以供使用
3. 按照此处的步骤 https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Installing.html 来安装和设置Amazon Schema Conversation Tool(SCT)
4. 一旦连接到两个数据库，请按照此处的步骤 https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Converting.html 来转换"您的模式(我为该数据库做了整个公共"模式，以确保覆盖了所有内容
5. 创建或修改您的AWS DMS迁移，确保目标表准备模式="TRUNCATE"并在目标数据库上禁用外键.如果要修改，请确保在询问您重新启动"时是否继续

1. Stop (if it still exists) the existing AWS DMS migration
2. Drop the existing migrated database, and create a new empty schema to use
3. Follow the steps here https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Installing.html to install and setup the Amazon Schema Conversation Tool (SCT)
4. Once you are connected to both databases, follow the steps here https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Converting.html to "convert" your schema (I did the entire "public" schema for this database to ensure everything is covered
5. Create or modify your AWS DMS Migration, ensuring Target Table Preparation Mode = "TRUNCATE" and disable foreign keys on the target database. If modifying, make sure when asked you "RESTART" not resume

我尚未测试的是如何处理我正在迁移实时数据库的事实.因此，完成迁移后，序列在目标数据库上可能已过期.我相信我稍后可以进入SCT并仅迁移序列，但我尚未对此进行测试.

What I have not yet tested is how to handle the fact that I am migrating a live database. So the sequences may be out of date on the target database when the migration is done. I believe I can just later go into SCT and only migrate the sequences but I have not tested this yet.

这篇关于使用AWS DMS迁移后PostgreSQL主键序列丢失的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！