After importing data in PostgreSQL, duplicate key value violates unique constraint

Question

I recently migrated my rails app to PostgreSQL in order to take advantage of fulltext search.

Since the migration coincided with moving to a new webhost, the steps for migration were:

1. Deploy the application and db:create/db:schema:load on the new server, with appropriate database.yml file
2. mysqldump data only from existing MySQL production database
3. import data into PostgreSQL database

The application is running successfully but the issue comes when trying to add new content to the database. For example, when I run the rake task to update my twitter feed:

PG::Error: ERROR:  duplicate key value violates unique constraint "twitter_feeds_pkey" DETAIL:  Key (id)=(3) already exists.

This also happens for all other models, creating new articles, users etc. In development I can see that posting the insert statement n+1 times will successfully save the record without error.

My question is: How do I tell PostgreSQL to start adding indexes sequentially from the existing data?

I've read the REINDEX page but don't think that is really the operation I'm looking for.

Answer 1

In Rails you can use the command

ActiveRecord::Base.connection.reset_pk_sequence!('users')

to bring the primary key index for the User table in sync again.