Fixing django_migrations after an AWS DMS database migration

The Problem:

A few months back we had migrated the Postgres database for our Link Shortener micro-service from self-hosted on an EC2 instance to an AWS RDS managed instance.

Migrating from self hosted Postgres to an RDS hosted Postgres, helps in making our lives much easier, particularly with the storage autoscaling feature, and the ease with which we can set up read replicas.

We used AWS DMS for performing the migration. AWS DMS is easy to use and can reliably transfer data from the self hosted database to the managed database.

Recently, we got a request for the addition of a new feature on the Link Shortener micro-service. The development was done, the feature had been tested and we were ready to deploy.

The first step of deployment was running the Django migrate command, so that the schema changes could be applied in the database. (python manage.py migrate)

This command ran for about 45 minutes (since it had to apply changes to more than 900M rows) after which it returned the following error:

django.db.utils.IntegrityError: null value in column "id" of relation "django_migrations" violates not-null constraint

DETAIL: Failing row contains (null, app_name, 00XX_migration_name, 2023-03-15 21:33:43.673617+00).

On checking the database, we found that the actual migrations had been applied. All of the expected columns and indexes had been created.

So the only part of the migrate command that had failed was adding an entry in the django_migrations table.

The Cause:

The id column in the tables created by Django is an AutoField. In Django, an AutoField is simply a self incrementing integer field.

For a Postgres database, Django’s migrate command creates a sequence for the columns that are AutoFields and sets that column’s default value to the nextval of the created sequence.

This means that the value for the id column of the django_migrations table is not provided by Django since it expects the database to fill it with the next incremental value. But the required sequence was not present on our database. This is what resulted in the violation of the not-null constraint as seen above.

AWS DMS did not recreate the sequences from the old database. This has also been documented by them.

After the migration through AWS DMS, all indexes, constraints and other database objects had been recreated manually by our Infra Team on the new RDS instance. It seems that the creation of sequences was missed. None of the tables actively used by our micro-service actually use the AutoField, and so these missing sequences did not cause any issue for months, until the day we wanted to deploy our new feature.

You can run this on Postgres to check existing sequences:

SELECT * FROM information_schema.sequences

The Solution:

To solve the issue, we would simply have to recreate the missing sequence ourselves. Once the sequence has been recreated, we can run the Django migrate command with the --fake parameter since the actual migrations have already been applied in the database.

Lets assume the table django_migrations looks like this:

So, our sequence created for the id column should be such that the next entry in our table has the id 14.

Here are the steps we followed for every table that had the id field. You can replace django_migrations with your table name and replace id with your column name in the steps below:

Get the latest value of the id column. We get 13 for our example.

SELECT MAX(id) FROM django_migrations;

Create a sequence that starts with the latest value from the above step.

CREATE SEQUENCE 
  django_migrations_id_seq 
-- replace integer with bigint if your column is of type bigint
as integer 
-- replace 13 with the max(id) for your table
start with 13;

Call the nextval function on the sequence created above, to increment its current value. We could have started the sequence directly from 14, and we wouldn’t need this step, but I just wanted the last_value of the sequence to look like it would in the original database.

SELECT nextval('django_migrations_id_seq');

Alter the sequence so that it is recorded that the sequence started from 1. This step is also not necessary, but I just wanted the start_value of the sequence to look like it would in the original database.

ALTER SEQUENCE django_migrations_id_seq start with 1;

Alter the table to set default value of our id column to nextval of the sequence created above.

ALTER TABLE django_migrations ALTER id SET DEFAULT nextval('django_migrations_id_seq'::regclass);

Alter the sequence and set the django_migrations.id column as the sequence’s owner.

ALTER SEQUENCE django_migrations_id_seq OWNED BY django_migrations.id;

Now, we simply run the fake Django migration command to create an entry in the django_migrations table.

python manage.py migrate --fake

I sincerely hope that my experience can save you from the midnight troubleshooting marathon that I had to endure. Here’s to more efficient debugging and fewer 2am coding adventures. Happy coding!