I was working on some fairly big features involving new Django models for one of our apps and when I pushed to our staging environment, I got this error during deployment at the database migration step:

duplicate key value violates unique constraint "auth_permission_pkey" DETAIL: Key (id)=(55) already exists

It looks like new records were getting added to the auth_permission table. I was puzzled as I didn't make any changes related to the Django auth app. But then I realized this happens automatically when a new model is added as this table holds the CRUD permission records for every Django model.

I ran the migrate command again and noticed the primary key sequence auto-incremented, so I just kept running it until it got to a point where it was at the right ID that doesn't conflict with existing records. After that, the next migrations were fine and I didn't see any issues with the app.

Of course, I still wanted to figure out what happened here as any database error scares me. There might be something important that I missed that could potentially break some part of the app.

One thing we did recently before I encountered this issue was migrating this database from PostgreSQL 9.5 to PostgreSQL 10. The way we did it was by simply restoring a backup of the 9.5 database into version 10.

After some digging around, I found out that others had the same issue with this, where the primary key sequences were out of sync after a database restore. It turned out Django even has a built-in management command to handle this called sqlsequencereset.

So the fix is to basically run this command for the Django app that was giving the error which would generate the SQL commands to run to reset the sequences. You can then copy and paste these commands in the database shell. In my case I needed to run it for the auth app:

python manage.py sqlsequencereset auth

Which would output something like this:

BEGIN;
SELECT setval(pg_get_serial_sequence('"auth_permission"','id'), coalesce(max("id"), 1), max("id") IS NOT null) FROM "auth_permission";
SELECT setval(pg_get_serial_sequence('"auth_group_permissions"','id'), coalesce(max("id"), 1), max("id") IS NOT null) FROM "auth_group_permissions";
SELECT setval(pg_get_serial_sequence('"auth_group"','id'), coalesce(max("id"), 1), max("id") IS NOT null) FROM "auth_group";
SELECT setval(pg_get_serial_sequence('"auth_user_groups"','id'), coalesce(max("id"), 1), max("id") IS NOT null) FROM "auth_user_groups";
SELECT setval(pg_get_serial_sequence('"auth_user_user_permissions"','id'), coalesce(max("id"), 1), max("id") IS NOT null) FROM "auth_user_user_permissions";
SELECT setval(pg_get_serial_sequence('"auth_user"','id'), coalesce(max("id"), 1), max("id") IS NOT null) FROM "auth_user";
COMMIT;

You can actually even pipe this output to the Django dbshell management command to execute it right away instead of having to manually execute it in the psql shell:

python manage.py sqlsequencereset auth | python manage.py dbshell

To make sure it completely works, I rolled back the code on stage, restored a backup of the stage database before the migration, ran the command above, then re-deployed and got a clean output this time.

Times like this makes me really glad that we've invested the time in building a staging environment to catch these issues early on and having regular database backups automatically run in the background as well. :)

Source 1

Source 2