Django "MigrationSchemaMissing: Unable to create the django_migrations table (no schema has been selected to create in)"

Question

I'm trying to restore a production database to my local machine, similar to the workflow described for Heroku (https://devcenter.heroku.com/articles/heroku-postgres-import-export). However, I'm using Aptible, which provides a DB tunnel for this purpose.

Using pgAdmin4, I've created a 'Custom' backup. Then, slightly modifying a pgAdmin command, I've restored it using

"/Applications/pgAdmin 4.app/Contents/SharedSupport/pg_restore" --host "localhost" --port "5432" --username "postgres" --no-password --dbname "lucy_prod" --verbose "/Users/kurtpeek/lucy-prod-backup-11-june-2018" --clean

where I've added the --clean option to drop database objects before recreating them (cf. https://www.postgresql.org/docs/9.2/static/app-pgrestore.html).

The problem is that when I now try to python manage.py migrate, I get the following error:

(venv) Kurts-MacBook-Pro-2:lucy-web kurtpeek$ python manage.py migrate
Traceback (most recent call last):
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/db/backends/utils.py", line 62, 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...
                     ^


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/db/migrations/recorder.py", line 57, in ensure_schema
    editor.create_model(self.Migration)
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/db/backends/base/schema.py", line 303, in create_model
    self.execute(sql, params or None)
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/db/backends/base/schema.py", line 120, in execute
    cursor.execute(sql, params)
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/db/backends/utils.py", line 79, in execute
    return super(CursorDebugWrapper, self).execute(sql, params)
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/db/utils.py", line 94, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/utils/six.py", line 685, in reraise
    raise value.with_traceback(tb)
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/db/backends/utils.py", line 62, in execute
    return self.cursor.execute(sql)
django.db.utils.ProgrammingError: no schema has been selected to create in
LINE 1: CREATE TABLE "django_migrations" ("id" serial NOT NULL PRIMA...
                     ^


During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "manage.py", line 29, in <module>
    execute_from_command_line(sys.argv)
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/core/management/__init__.py", line 364, in execute_from_command_line
    utility.execute()
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/core/management/__init__.py", line 356, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/core/management/base.py", line 283, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/core/management/base.py", line 330, in execute
    output = self.handle(*args, **options)
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/core/management/commands/migrate.py", line 83, in handle
    executor = MigrationExecutor(connection, self.migration_progress_callback)
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/db/migrations/executor.py", line 20, in __init__
    self.loader = MigrationLoader(self.connection)
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/db/migrations/loader.py", line 52, in __init__
    self.build_graph()
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/db/migrations/loader.py", line 209, in build_graph
    self.applied_migrations = recorder.applied_migrations()
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/db/migrations/recorder.py", line 65, in applied_migrations
    self.ensure_schema()
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/db/migrations/recorder.py", line 59, in ensure_schema
    raise MigrationSchemaMissing("Unable to create the django_migrations table (%s)" % exc)
django.db.migrations.exceptions.MigrationSchemaMissing: Unable to create the django_migrations table (no schema has been selected to create in
LINE 1: CREATE TABLE "django_migrations" ("id" serial NOT NULL PRIMA...
                     ^
)

From what I understand from MigrationSchemaMissing(Unable to create the django_migrations table (%s) % exc), I need to run

grant usage on schema public to username;
grant create on schema public to username;

However, what should I fill out for username? Should this be postgres?

Here are some more details on the public schema and the search_path:

(venv) Kurts-MacBook-Pro-2:lucy-web kurtpeek$ python manage.py dbshell
psql (10.4)
Type "help" for help.

lucy_prod=> \dn+ public
                        List of schemas
  Name  |  Owner   | Access privileges |      Description       
--------+----------+-------------------+------------------------
 public | postgres |                   | standard public schema
(1 row)

lucy_prod=> SHOW search_path;
   search_path   
-----------------
 "$user", public
(1 row)

Update

From running the select current_user; command, I determined that my current user is named lucyapp, but if I try to grant usage on schema public for this user I get a 'permission denied' error:

lucy_prod=> select current_user;
 current_user 
--------------
 lucyapp
(1 row)

lucy_prod=> grant usage on schema public to lucyapp;
ERROR:  permission denied for schema public
lucy_prod=> grant create on schema public to lucyapp;
ERROR:  permission denied for schema public

score 8 · Answer 1 · edited Jun 13 '23 at 04:10

8

This is happening due to the access issue of the role to the public schema. Running this script inside the database shell will solve the issue.

GRANT ALL ON SCHEMA public TO your_user;
GRANT ALL ON SCHEMA public TO public;

This is for Postgres DB. A similar script can be found for other SQL DB.

edited Jun 13 '23 at 04:10

Phil Dukhov

67,741
15
184
220

answered Jul 29 '20 at 15:53

Kundan Kumar

343
1
3
11

1

still got same issue – Yabesh Jan 17 '23 at 17:11
should the second one not be" TO you_user" rather than "TO public"? – wobbily_col Jan 27 '23 at 13:45
usually an explanation of the solution/code is helpful; you defined the problem, and then gave a solution (purportedly), but didn't explain or justify it. Especially granting privileges on public to public. – Life5ign Jun 11 '23 at 02:57

score 6 · Answer 2 · answered May 05 '23 at 07:20

6

Just changing the database ownership worked for me.

ALTER DATABASE database OWNER TO user;

answered May 05 '23 at 07:20

faheemkodi

249
3
8

worked for me too ! – Theophile Aug 30 '23 at 08:37

score 0 · Answer 3 · answered Jun 14 '18 at 22:40

I was able to GRANT USAGE and GRANT CREATE when I simply entered psql using the psql command instead of python manage.py dbshell. I subsequently ran into errors relating to the owner of the objects in the database (relations, etc.) not being the one in the DATABASES['USER'] settings, namely, 'lucyapp'.

I finally resolved the issues using pg_restore with the --no-owner and --role=lucyapp options, following Postgresql - backup database and restore on different owner?.

score 0 · Answer 4 · edited May 07 '23 at 10:14

0

Check the user created earlier after db creation and the one set in DATABASES configurations in settings.py is matching. Lets call it as myuser and the db created as mydb

Apply below permissions to the user -

GRANT ALL ON DATABASE mydb TO myuser; 
ALTER DATABASE mydb OWNER TO myuser;
GRANT USAGE, CREATE ON SCHEMA PUBLIC TO myuser;

edited May 07 '23 at 10:14

Sandeepa Kariyawasam

531
1
6
28

answered May 02 '23 at 20:04

Vishesh Jha

1

Django "MigrationSchemaMissing: Unable to create the django_migrations table (no schema has been selected to create in)"

4 Answers4

Linked