Aurora Postgresql's insert statements aren't recognizing existing key IDs-CodePudding

After migrating from RDS Postgresql to RDS Aurora PostgreSQL, I am having an issue to where new inserts are trying to start from key ID 2 instead of the last record.

In my rails app, here's what I'm seeing when I try to create a new record:

ActiveRecord::RecordNotUnique (PG::UniqueViolation: ERROR:  duplicate key value violates unique constraint "global_options_pkey"
DETAIL:  Key (id)=(3) already exists.
):

app/controllers/schedules_controller.rb:45:in `create'

Not sure why this would be the case. To set up Aurora PSQL in Rails, I followed this tutorial: https://guides.rubyonrails.org/active_record_multiple_databases.html. It seems like everything is working fine (auto switching between reader/writer instances, etc.).

With the migration, I specifically used the AWS Schema Conversion Tool (SCT), followed by a migration with the Database Migration Service.

Is this error caused by something that was done incorrectly in the migration process or do I need to some post-migration processes to have this fixed?

CodePudding user response：

Definitely a case of when you migrated old data, but not with INSERT and your sequences are still on the initial values.

Use sequence manipulation functions to check what is the current value with lastval(), and set it to max(id) with setval()

CodePudding user response：

You can try

ActiveRecord::Base.connection.tables.each do |table_name|
  ActiveRecord::Base.connection.reset_pk_sequence!(table_name)
end