Django Postgres Multiple Schema Migrations: "No migrations to apply"

Question

Setup: one Postgres database with two schemas: 'default' and 'other'. Using:

• Django==2.0.10
• psycopg2-binary==2.7.7 (no binary verion in production)

My database configuration:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'OPTIONS': {
            'options': '-c search_path=django,default',
        },
        ...
        'TEST': {
            'NAME': 'default',
            'DEPENDENCIES': ['other'],
        },
        'ATOMIC_REQUESTS': True,
    },
    'other': {
        'ENGINE': 'django.db.backends.postgresql',
        'OPTIONS': {
            'options': '-c search_path=other,default',
        },
        ...
        'TEST': {
            'NAME': 'other',
            'DEPENDENCIES': [],
        },
        'ATOMIC_REQUESTS': True,
    }
}

Steps taken on a fresh database:

• py manage.py migrate: works as expected and the 'default' schema is migrated correctly.
• py manage.py migrate --database=other results in: No migrations to apply.

Oddly, taking these steps (again, on a fresh database) in reverse order works:

• py manage.py migrate --database=other: works as expected and the 'other' schema is migrated correctly.
• py manage.py migrate results in: works as expected and the 'default' schema is migrated correctly.

I suspect this has something to do with the django_migrations table. I don't know much about this table, is there one per Postgres schema? Or is there only one django_migrations table per project? After a lot of Googling I saw many suggestions for the use of database routers. The 'other' schema must have all the same tables as the 'default' schema so a router won't help in this case.

I have tried executing the SQL DELETE FROM django_migrations after running migrations on 'default' and then running migrations on 'other'; this works only for the first migration on a fresh database, any subsequent migration attempts result in an error as Django tries to apply already applied migrations and raises django.db.utils.ProgrammingError: relation "<relation>" already exists.

I have also tried playing around with the django.db.migrations.recorder.MigrationRecorder class to flush the migrations table between migrating the 'default' and 'other' database, again no luck.

Additionally, the only way I was able to get my test suite to run was to set 'other' as a dependency for 'default' to exploit the odd fact that migrating 'other' before 'default' works.

I'm at a loss as to why this is happening, I'm sure there's something I'm missing.

Update

Taking the same steps as mentioned earlier:

• py manage.py migrate: works as expected and the 'default' schema is migrated correctly.
• py manage.py migrate --database=other results in: No migrations to apply.

After some digging around in pgAdmin I ran the following SQL

SET search_path TO default
SELECT * FROM django_migrations

and this returned the table of applied migrations as expected. Then running the following SQL

SET search_path TO other
SELECT * FROM django_migrations

and I got an error saying that the relation "django_migrations" does not exist. So this answers my question regarding the django_migrations table: there should be one per schema and this of course makes perfect sense.

So this leads me to think that Django must be looking at the django_migrations table from the 'default' schema when trying to migrate the 'other' schema and thus sees that there are "no migrations to apply". I will keep on trying to resolve this, any pointers would go a long way as I am still unsure how to get the migrations working on both schemas.

Update

I had written this as an answer but this solution only worked locally so I have added it as an update instead.

After staring at the DATABASES configuration for a while I finally understood what was causing the issues I was seeing. I had provided a comma separated list for each search_path option like so:

DATABASES = {
    'default': {
        ...
        'OPTIONS': {
            'options': '-c search_path=django,default',
        },
        ...
    },
    'other': {
        ...
        'OPTIONS': {
            'options': '-c search_path=other,default',
        },
        ...
    }
}

My interpretation of what is going on here: when running py manage.py migrate on a fresh database Django will see that there is no django_migrations table in the 'default' schema, create one and then populate it as it applies the migrations. Then when I run py manage.py migrate --database=other it will look in 'other' for the django_migrations, not find it and then it must look in 'default' as it is the next place to look in the search path. As django_migrations already exists in the 'default' schema Django will use this table then see that there are "no migrations to apply". I may not be completely accurate here, please correct me if I have said anything blatantly wrong.

Changing the search paths in the DATABASES configuration like so:

DATABASES = {
    'default': {
        ...
        'OPTIONS': {
            'options': '-c search_path=default',
        },
        ...
    },
    'other': {
        ...
        'OPTIONS': {
            'options': '-c search_path=other',
        },
        ...
    }
}

has resulted in both schemas being migrated successfully in my local environment. However, this does not work when migrations are run on TravisCI or Heroku. Both of which give this error upon migration of either 'default' or 'other':

Traceback (most recent call last):
  File "/home/travis/virtualenv/python3.6.3/lib/python3.6/site-packages/django/db/backends/utils.py", line 83, in _execute
    return self.cursor.execute(sql)
psycopg2.ProgrammingError: no schema has been selected to create in
LINE 1: CREATE TABLE "django_migrations" ("id" serial NOT NULL PRIMA...

Answer 1

The solution to my problems comes in two parts.

Getting Migrations to Work on TravisCI

After discovering GitHub's incredible repo search feature I searched for any usage of CREATE SCHEMA <schema_name> in any .travis.yml file.

I changed these two lines in the before_script of my .travis.yml file from

- psql -c "CREATE DATABASE travisci;" -U postgres
- psql -c "CREATE SCHEMA other;" -U postgres

to

- psql -c "CREATE DATABASE travisci;" -U postgres
- psql -c "CREATE SCHEMA other;" -d travisci -U postgres

Adding the -d travisci argument resulted in both schemas migrating correctly.

Getting Migrations to Work on Heroku

I had been using this command to access the SQL client for my Heroku app:

heroku pg:psql -a <app_name>

It should have been obvious to me why this wouldn't work. You can have more than one database per Heroku app and I had not specified a database name. Using this command worked:

heroku pg:psql <database_name> -a <app_name>

Also using pgAdmin to create the schema worked.