Changing from standard sqlite to postgres database

Question

I'm trying to change from the standard sqlite database to postgresql in my Django project. Here's what I've done so far:

Installed postgresql
Created a database and a user with privileges for writing to it

Changing the database setting in settings.py:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'workout',
        'USER': 'admin',
        'PASSWORD': 'admin',
        'HOST': 'localhost',
        'PORT': '5432',
    }
}

Then, when I try to migrate, this happens (warning for long error trace):

(workout) Sahands-MBP:workout sahandzarrinkoub$ python manage.py migrate
Operations to perform:
  Apply all migrations: admin, auth, contenttypes, sessions, workoutcal
Running migrations:
  Applying contenttypes.0001_initial... OK
  Applying contenttypes.0002_remove_content_type_name... OK
  Applying auth.0001_initial... OK
  Applying auth.0002_alter_permission_name_max_length... OK
  Applying auth.0003_alter_user_email_max_length... OK
  Applying auth.0004_alter_user_username_opts... OK
  Applying auth.0005_alter_user_last_login_null... OK
  Applying auth.0006_require_contenttypes_0002... OK
  Applying auth.0007_alter_validators_add_error_messages... OK
  Applying auth.0008_alter_user_username_max_length... OK
  Applying workoutcal.0001_initial... OK
  Applying admin.0001_initial... OK
  Applying admin.0002_logentry_remove_auto_add... OK
  Applying sessions.0001_initial... OK
  Applying workoutcal.0002_auto_20171215_1102... OK
  Applying workoutcal.0003_auto_20171215_1153...Traceback (most recent call last):
  File "/Users/sahandzarrinkoub/Documents/Programming/Web/Django/workout/lib/python3.6/site-packages/django/db/backends/utils.py", line 65, in execute
    return self.cursor.execute(sql, params)
psycopg2.ProgrammingError: multiple default values specified for column "id" of table "workoutcal_user"


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

Traceback (most recent call last):
  File "manage.py", line 22, in <module>
    execute_from_command_line(sys.argv)
  File "/Users/sahandzarrinkoub/Documents/Programming/Web/Django/workout/lib/python3.6/site-packages/django/core/management/__init__.py", line 364, in execute_from_command_line
    utility.execute()
  File "/Users/sahandzarrinkoub/Documents/Programming/Web/Django/workout/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/sahandzarrinkoub/Documents/Programming/Web/Django/workout/lib/python3.6/site-packages/django/core/management/base.py", line 283, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/Users/sahandzarrinkoub/Documents/Programming/Web/Django/workout/lib/python3.6/site-packages/django/core/management/base.py", line 330, in execute
    output = self.handle(*args, **options)
  File "/Users/sahandzarrinkoub/Documents/Programming/Web/Django/workout/lib/python3.6/site-packages/django/core/management/commands/migrate.py", line 204, in handle
    fake_initial=fake_initial,
  File "/Users/sahandzarrinkoub/Documents/Programming/Web/Django/workout/lib/python3.6/site-packages/django/db/migrations/executor.py", line 115, in migrate
    state = self._migrate_all_forwards(state, plan, full_plan, fake=fake, fake_initial=fake_initial)
  File "/Users/sahandzarrinkoub/Documents/Programming/Web/Django/workout/lib/python3.6/site-packages/django/db/migrations/executor.py", line 145, in _migrate_all_forwards
    state = self.apply_migration(state, migration, fake=fake, fake_initial=fake_initial)
  File "/Users/sahandzarrinkoub/Documents/Programming/Web/Django/workout/lib/python3.6/site-packages/django/db/migrations/executor.py", line 244, in apply_migration
    state = migration.apply(state, schema_editor)
  File "/Users/sahandzarrinkoub/Documents/Programming/Web/Django/workout/lib/python3.6/site-packages/django/db/migrations/migration.py", line 129, in apply
    operation.database_forwards(self.app_label, schema_editor, old_state, project_state)
  File "/Users/sahandzarrinkoub/Documents/Programming/Web/Django/workout/lib/python3.6/site-packages/django/db/migrations/operations/fields.py", line 87, in database_forwards
    field,
  File "/Users/sahandzarrinkoub/Documents/Programming/Web/Django/workout/lib/python3.6/site-packages/django/db/backends/base/schema.py", line 429, in add_field
    self.execute(sql, params)
  File "/Users/sahandzarrinkoub/Documents/Programming/Web/Django/workout/lib/python3.6/site-packages/django/db/backends/base/schema.py", line 120, in execute
    cursor.execute(sql, params)
  File "/Users/sahandzarrinkoub/Documents/Programming/Web/Django/workout/lib/python3.6/site-packages/django/db/backends/utils.py", line 80, in execute
    return super(CursorDebugWrapper, self).execute(sql, params)
  File "/Users/sahandzarrinkoub/Documents/Programming/Web/Django/workout/lib/python3.6/site-packages/django/db/backends/utils.py", line 65, in execute
    return self.cursor.execute(sql, params)
  File "/Users/sahandzarrinkoub/Documents/Programming/Web/Django/workout/lib/python3.6/site-packages/django/db/utils.py", line 94, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/Users/sahandzarrinkoub/Documents/Programming/Web/Django/workout/lib/python3.6/site-packages/django/utils/six.py", line 685, in reraise
    raise value.with_traceback(tb)
  File "/Users/sahandzarrinkoub/Documents/Programming/Web/Django/workout/lib/python3.6/site-packages/django/db/backends/utils.py", line 65, in execute
    return self.cursor.execute(sql, params)
django.db.utils.ProgrammingError: multiple default values specified for column "id" of table "workoutcal_user"

(workout) Sahands-MBP:workout sahandzarrinkoub$ python manage.py migrate
Operations to perform:
  Apply all migrations: admin, auth, contenttypes, sessions, workoutcal
Running migrations:
  Applying workoutcal.0003_auto_20171215_1153...Traceback (most recent call last):
  File "/Users/sahandzarrinkoub/Documents/Programming/Web/Django/workout/lib/python3.6/site-packages/django/db/backends/utils.py", line 65, in execute
    return self.cursor.execute(sql, params)
psycopg2.ProgrammingError: multiple default values specified for column "id" of table "workoutcal_user"


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

Traceback (most recent call last):
  File "manage.py", line 22, in <module>
    execute_from_command_line(sys.argv)
  File "/Users/sahandzarrinkoub/Documents/Programming/Web/Django/workout/lib/python3.6/site-packages/django/core/management/__init__.py", line 364, in execute_from_command_line
    utility.execute()
  File "/Users/sahandzarrinkoub/Documents/Programming/Web/Django/workout/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/sahandzarrinkoub/Documents/Programming/Web/Django/workout/lib/python3.6/site-packages/django/core/management/base.py", line 283, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/Users/sahandzarrinkoub/Documents/Programming/Web/Django/workout/lib/python3.6/site-packages/django/core/management/base.py", line 330, in execute
    output = self.handle(*args, **options)
  File "/Users/sahandzarrinkoub/Documents/Programming/Web/Django/workout/lib/python3.6/site-packages/django/core/management/commands/migrate.py", line 204, in handle
    fake_initial=fake_initial,
  File "/Users/sahandzarrinkoub/Documents/Programming/Web/Django/workout/lib/python3.6/site-packages/django/db/migrations/executor.py", line 115, in migrate
    state = self._migrate_all_forwards(state, plan, full_plan, fake=fake, fake_initial=fake_initial)
  File "/Users/sahandzarrinkoub/Documents/Programming/Web/Django/workout/lib/python3.6/site-packages/django/db/migrations/executor.py", line 145, in _migrate_all_forwards
    state = self.apply_migration(state, migration, fake=fake, fake_initial=fake_initial)
  File "/Users/sahandzarrinkoub/Documents/Programming/Web/Django/workout/lib/python3.6/site-packages/django/db/migrations/executor.py", line 244, in apply_migration
    state = migration.apply(state, schema_editor)
  File "/Users/sahandzarrinkoub/Documents/Programming/Web/Django/workout/lib/python3.6/site-packages/django/db/migrations/migration.py", line 129, in apply
    operation.database_forwards(self.app_label, schema_editor, old_state, project_state)
  File "/Users/sahandzarrinkoub/Documents/Programming/Web/Django/workout/lib/python3.6/site-packages/django/db/migrations/operations/fields.py", line 87, in database_forwards
    field,
  File "/Users/sahandzarrinkoub/Documents/Programming/Web/Django/workout/lib/python3.6/site-packages/django/db/backends/base/schema.py", line 429, in add_field
    self.execute(sql, params)
  File "/Users/sahandzarrinkoub/Documents/Programming/Web/Django/workout/lib/python3.6/site-packages/django/db/backends/base/schema.py", line 120, in execute
    cursor.execute(sql, params)
  File "/Users/sahandzarrinkoub/Documents/Programming/Web/Django/workout/lib/python3.6/site-packages/django/db/backends/utils.py", line 80, in execute
    return super(CursorDebugWrapper, self).execute(sql, params)
  File "/Users/sahandzarrinkoub/Documents/Programming/Web/Django/workout/lib/python3.6/site-packages/django/db/backends/utils.py", line 65, in execute
    return self.cursor.execute(sql, params)
  File "/Users/sahandzarrinkoub/Documents/Programming/Web/Django/workout/lib/python3.6/site-packages/django/db/utils.py", line 94, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/Users/sahandzarrinkoub/Documents/Programming/Web/Django/workout/lib/python3.6/site-packages/django/utils/six.py", line 685, in reraise
    raise value.with_traceback(tb)
  File "/Users/sahandzarrinkoub/Documents/Programming/Web/Django/workout/lib/python3.6/site-packages/django/db/backends/utils.py", line 65, in execute
    return self.cursor.execute(sql, params)
django.db.utils.ProgrammingError: multiple default values specified for column "id" of table "workoutcal_user"

(workout) Sahands-MBP:workout sahandzarrinkoub$ python manage.py migrate
Operations to perform:
  Apply all migrations: admin, auth, contenttypes, sessions, workoutcal
Running migrations:
  Applying workoutcal.0003_auto_20171215_1153...Traceback (most recent call last):
  File "/Users/sahandzarrinkoub/Documents/Programming/Web/Django/workout/lib/python3.6/site-packages/django/db/backends/utils.py", line 65, in execute
    return self.cursor.execute(sql, params)
psycopg2.ProgrammingError: multiple default values specified for column "id" of table "workoutcal_user"


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

Traceback (most recent call last):
  File "manage.py", line 22, in <module>
    execute_from_command_line(sys.argv)
  File "/Users/sahandzarrinkoub/Documents/Programming/Web/Django/workout/lib/python3.6/site-packages/django/core/management/__init__.py", line 364, in execute_from_command_line
    utility.execute()
  File "/Users/sahandzarrinkoub/Documents/Programming/Web/Django/workout/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/sahandzarrinkoub/Documents/Programming/Web/Django/workout/lib/python3.6/site-packages/django/core/management/base.py", line 283, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/Users/sahandzarrinkoub/Documents/Programming/Web/Django/workout/lib/python3.6/site-packages/django/core/management/base.py", line 330, in execute
    output = self.handle(*args, **options)
  File "/Users/sahandzarrinkoub/Documents/Programming/Web/Django/workout/lib/python3.6/site-packages/django/core/management/commands/migrate.py", line 204, in handle
    fake_initial=fake_initial,
  File "/Users/sahandzarrinkoub/Documents/Programming/Web/Django/workout/lib/python3.6/site-packages/django/db/migrations/executor.py", line 115, in migrate
    state = self._migrate_all_forwards(state, plan, full_plan, fake=fake, fake_initial=fake_initial)
  File "/Users/sahandzarrinkoub/Documents/Programming/Web/Django/workout/lib/python3.6/site-packages/django/db/migrations/executor.py", line 145, in _migrate_all_forwards
    state = self.apply_migration(state, migration, fake=fake, fake_initial=fake_initial)
  File "/Users/sahandzarrinkoub/Documents/Programming/Web/Django/workout/lib/python3.6/site-packages/django/db/migrations/executor.py", line 244, in apply_migration
    state = migration.apply(state, schema_editor)
  File "/Users/sahandzarrinkoub/Documents/Programming/Web/Django/workout/lib/python3.6/site-packages/django/db/migrations/migration.py", line 129, in apply
    operation.database_forwards(self.app_label, schema_editor, old_state, project_state)
  File "/Users/sahandzarrinkoub/Documents/Programming/Web/Django/workout/lib/python3.6/site-packages/django/db/migrations/operations/fields.py", line 87, in database_forwards
    field,
  File "/Users/sahandzarrinkoub/Documents/Programming/Web/Django/workout/lib/python3.6/site-packages/django/db/backends/base/schema.py", line 429, in add_field
    self.execute(sql, params)
  File "/Users/sahandzarrinkoub/Documents/Programming/Web/Django/workout/lib/python3.6/site-packages/django/db/backends/base/schema.py", line 120, in execute
    cursor.execute(sql, params)
  File "/Users/sahandzarrinkoub/Documents/Programming/Web/Django/workout/lib/python3.6/site-packages/django/db/backends/utils.py", line 80, in execute
    return super(CursorDebugWrapper, self).execute(sql, params)
  File "/Users/sahandzarrinkoub/Documents/Programming/Web/Django/workout/lib/python3.6/site-packages/django/db/backends/utils.py", line 65, in execute
    return self.cursor.execute(sql, params)
  File "/Users/sahandzarrinkoub/Documents/Programming/Web/Django/workout/lib/python3.6/site-packages/django/db/utils.py", line 94, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/Users/sahandzarrinkoub/Documents/Programming/Web/Django/workout/lib/python3.6/site-packages/django/utils/six.py", line 685, in reraise
    raise value.with_traceback(tb)
  File "/Users/sahandzarrinkoub/Documents/Programming/Web/Django/workout/lib/python3.6/site-packages/django/db/backends/utils.py", line 65, in execute
    return self.cursor.execute(sql, params)
django.db.utils.ProgrammingError: multiple default values specified for column "id" of table "workoutcal_user"

So there's seems to be some kind of error with workoutcal_user. I'm not sure how django names the tables, but the only model I have defined in workoutcal/models.py is this one:

class User(AbstractUser):
    REQUIRED_FIELDS = []
    USERNAME_FIELD = 'email'
    email = models.EmailField(
        _('email address'),
        max_length=150,
        unique=True,
        help_text=_('Required. 150 characters or fewer. Must be a valid email address.'),
        error_messages={
            'unique':_("A user with that email address already exists."),
        },
    )

I'm guessing this is the model that's causing problems. My question is obviously, why? What does it mean that multiple default values have been specified for the id field of this model? And how can I make them only one?

EDIT:

As Daniel Roseman requested, the failing migration 0003_auto_20171215_1153.py:

# -*- coding: utf-8 -*-
# Generated by Django 1.11.5 on 2017-12-15 11:53
from __future__ import unicode_literals

from django.db import migrations, models


class Migration(migrations.Migration):

    dependencies = [
        ('workoutcal', '0002_auto_20171215_1102'),
    ]

    operations = [
        migrations.AddField(
            model_name='user',
            name='id',
            field=models.AutoField(auto_created=True, default=1, primary_key=True, serialize=False, verbose_name='ID'),
            preserve_default=False,
        ),
        migrations.AlterField(
            model_name='user',
            name='email',
            field=models.EmailField(error_messages={'unique': 'A user with that email address already exists.'}, help_text='Required. 150 characters of fewer. Must be a valid email address.', max_length=150, unique=True, verbose_name='email address'),
        ),
    ]

EDIT 2:

I followed cezar's instructions. Upon reaching the last step and trying to migrate, this error occured.

(workout) Sahands-MBP:workout sahandzarrinkoub$ python manage.py migrate workoutcal
Traceback (most recent call last):
  File "manage.py", line 22, in <module>
    execute_from_command_line(sys.argv)
  File "/Users/sahandzarrinkoub/Documents/Programming/Web/Django/workout/lib/python3.6/site-packages/django/core/management/__init__.py", line 364, in execute_from_command_line
    utility.execute()
  File "/Users/sahandzarrinkoub/Documents/Programming/Web/Django/workout/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/sahandzarrinkoub/Documents/Programming/Web/Django/workout/lib/python3.6/site-packages/django/core/management/base.py", line 283, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/Users/sahandzarrinkoub/Documents/Programming/Web/Django/workout/lib/python3.6/site-packages/django/core/management/base.py", line 330, in execute
    output = self.handle(*args, **options)
  File "/Users/sahandzarrinkoub/Documents/Programming/Web/Django/workout/lib/python3.6/site-packages/django/core/management/commands/migrate.py", line 86, in handle
    executor.loader.check_consistent_history(connection)
  File "/Users/sahandzarrinkoub/Documents/Programming/Web/Django/workout/lib/python3.6/site-packages/django/db/migrations/loader.py", line 298, in check_consistent_history
    connection.alias,
django.db.migrations.exceptions.InconsistentMigrationHistory: Migration admin.0001_initial is applied before its dependency workoutcal.0001_initial on database 'default'.

What is this other migration? How can I completely erase migrations history?

EDIT 3: After dropping every single table in the database with DROP TABLE 'tablename' CASCADE;, removing all files in workoutcal/migrations and running makemigrations and migrate again, it worked.

It's in the question already. I can't think of any other model that it could be, that's the only one I've defined. — Sahand
– Sahand, Commented Dec 25, 2017 at 10:27
You should show the code of the failing migration, workoutcal.0003_auto_20171215_1153. — Daniel Roseman
– Daniel Roseman, Commented Dec 25, 2017 at 10:28

cezar · Accepted Answer · 2017-12-25 13:58:16Z

3

The problem is in the following line:

field=models.AutoField(auto_created=True, default=1, primary_key=True, serialize=False, verbose_name='ID'),

The id field must be unique as it is a primary key. It means it can't have a default value that is repeating in many entries.

I'd suggest a quick and dirty solution. Go directly to your PosgreSQL command line. Delete all entries in the table django_migrations for the app workoutcal:

DELETE FROM django_migrations WHERE app = 'workoutcal';

Delete the contents of the directory migrations in your app workoutcal:

rm -rf workoutcal/migrations/*

Then generate new migrations:

python manage.py makemigrations workoutcal

This should create new initial migration. After that the command:

python manage.py migrate workoutcal

should populate the database without any problems.

EDIT:

The migration admin.0001_initial depends on workoutcal.0001_initial. I can't explain this dependency. You can use django command line tools to revert the migrations for admin:

python manage.py migrate admin zero

With the option zero you tell django to revert all migrations and establish the state before the initial migration has been run. Instead of zero you can specify the number of the migration. In your particular situation you'll need however to revert to the initial state using zero.

After that run the migrations for workoutcal and after that for admin:

python manage.py migrate workoutcal
python manage.py migrate admin

edited Dec 25, 2017 at 13:58

answered Dec 25, 2017 at 10:44

cezar

12.1k6 gold badges51 silver badges92 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sahand Over a year ago

Thank you @cezar. Unfortunately there was an error when I tried your solution. Could you please look at EDIT 2?

Collectives™ on Stack Overflow

Changing from standard sqlite to postgres database

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related