Django [SOLVED]: Changing from standard sqlite to postgres database

Django [SOLVED]: Changing from standard sqlite to postgres database

Home Forums Frameworks Django Tutorials Django [SOLVED]: Changing from standard sqlite to postgres database

Viewing 2 posts - 1 through 2 (of 2 total)
  • Author
    Posts
  • #246524

    Cloudy Point
    Keymaster

    QuestionQuestion

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

    1. Installed postgresql
    2. Created a database and a user with privileges for writing to it
    3. 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.

    #246525

    Cloudy Point
    Keymaster

    Accepted AnswerAnswer

    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
    

    Source: https://stackoverflow.com/questions/47967705/changing-from-standard-sqlite-to-postgres-database
    Author: cezar
    Creative Commons License
    This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic.