Cannot migrate from SQLite to PostgreSQL

Using Django
1

I try to migrate database from SQLite to PostGre… but I get an error when running “migrate” :

I :
Created a PostgreSQL db (it’s owned by postres for the moment)
Changed the settings.py with theses values :

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': 'django',
        'USER': 'postgres',
        'PASSWORD': '',
        'HOST': '127.0.0.1',
        'PORT': '5432',
    }
  • Ran migrate --run-syncdb

I get this error :

bash -cl "/home/jbal/PycharmProjects/djangoProject/venv/bin/python /opt/pycharm-professional/plugins/python/helpers/pycharm/django_manage.py migrate /home/jbal/PycharmProjects/djangoProject"
Tracking file by folder pattern:  migrations
Traceback (most recent call last):
  File "/home/jbal/PycharmProjects/djangoProject/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
psycopg2.errors.UndefinedTable: ERREUR:  la relation « Citatio_quote » n'existe pas
LINE 1: ...tatio_quote"."id", "Citatio_quote"."content" FROM "Citatio_q...
                                                         ^

I’m not able to see if the problem comes from :

  • PostgreSQL configuration
  • Migration files
  • Django settings
  • etc…

Any help ?

Thank you :slight_smile:

2

I don’t think there’s enough information here to give you any specific advice.

I might suggest running migrate with the --plan parameter to see what migrate is trying to do, along with the showmigrations command to see what the current status is.

I might also try running migrate without the --run-syncdb parameter first to see if the “non-migration” tables are causing this.

Beyond that, I’d be looking hard at the relationships between tables and verifying that the sequence of migrations being applied accounts for all the foreign key dependencies.

3

Here is the results of the two command (spoiler : I get the same error with the two… mayble it’s not due to migrate ?) :

manage.py@djangoProject > showmigrations
bash -cl "/home/jbal/PycharmProjects/djangoProject/venv/bin/python /opt/pycharm-professional/plugins/python/helpers/pycharm/django_manage.py showmigrations /home/jbal/PycharmProjects/djangoProject"
Tracking file by folder pattern:  migrations
Traceback (most recent call last):
  File "/home/jbal/PycharmProjects/djangoProject/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
psycopg2.errors.UndefinedTable: ERREUR:  la relation « Citatio_quote » n'existe pas
LINE 1: ...tatio_quote"."id", "Citatio_quote"."content" FROM "Citatio_q...
                                                             ^


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

Traceback (most recent call last):
  File "/opt/pycharm-professional/plugins/python/helpers/pycharm/django_manage.py", line 52, in <module>
    run_command()
  File "/opt/pycharm-professional/plugins/python/helpers/pycharm/django_manage.py", line 46, in run_command
    run_module(manage_file, None, '__main__', True)
  File "/usr/lib/python3.8/runpy.py", line 207, in run_module
    return _run_module_code(code, init_globals, run_name, mod_spec)
  File "/usr/lib/python3.8/runpy.py", line 97, in _run_module_code
    _run_code(code, mod_globals, init_globals,
  File "/usr/lib/python3.8/runpy.py", line 87, in _run_code
    exec(code, run_globals)
  File "/home/jbal/PycharmProjects/djangoProject/manage.py", line 22, in <module>
    main()
  File "/home/jbal/PycharmProjects/djangoProject/manage.py", line 18, in main
    execute_from_command_line(sys.argv)
  File "/home/jbal/PycharmProjects/djangoProject/venv/lib/python3.8/site-packages/django/core/management/__init__.py", line 401, in execute_from_command_line
    utility.execute()
  File "/home/jbal/PycharmProjects/djangoProject/venv/lib/python3.8/site-packages/django/core/management/__init__.py", line 395, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/home/jbal/PycharmProjects/djangoProject/venv/lib/python3.8/site-packages/django/core/management/base.py", line 330, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/home/jbal/PycharmProjects/djangoProject/venv/lib/python3.8/site-packages/django/core/management/base.py", line 368, in execute
    self.check()
  File "/home/jbal/PycharmProjects/djangoProject/venv/lib/python3.8/site-packages/django/core/management/base.py", line 392, in check
    all_issues = checks.run_checks(
  File "/home/jbal/PycharmProjects/djangoProject/venv/lib/python3.8/site-packages/django/core/checks/registry.py", line 70, in run_checks
    new_errors = check(app_configs=app_configs, databases=databases)
  File "/home/jbal/PycharmProjects/djangoProject/venv/lib/python3.8/site-packages/django/core/checks/urls.py", line 13, in check_url_config
    return check_resolver(resolver)
  File "/home/jbal/PycharmProjects/djangoProject/venv/lib/python3.8/site-packages/django/core/checks/urls.py", line 23, in check_resolver
    return check_method()
  File "/home/jbal/PycharmProjects/djangoProject/venv/lib/python3.8/site-packages/django/urls/resolvers.py", line 408, in check
    for pattern in self.url_patterns:
  File "/home/jbal/PycharmProjects/djangoProject/venv/lib/python3.8/site-packages/django/utils/functional.py", line 48, in __get__
    res = instance.__dict__[self.name] = self.func(instance)
  File "/home/jbal/PycharmProjects/djangoProject/venv/lib/python3.8/site-packages/django/urls/resolvers.py", line 589, in url_patterns
    patterns = getattr(self.urlconf_module, "urlpatterns", self.urlconf_module)
  File "/home/jbal/PycharmProjects/djangoProject/venv/lib/python3.8/site-packages/django/utils/functional.py", line 48, in __get__
    res = instance.__dict__[self.name] = self.func(instance)
  File "/home/jbal/PycharmProjects/djangoProject/venv/lib/python3.8/site-packages/django/urls/resolvers.py", line 582, in urlconf_module
    return import_module(self.urlconf_name)
  File "/usr/lib/python3.8/importlib/__init__.py", line 127, in import_module
    return _bootstrap._gcd_import(name[level:], package, level)
  File "<frozen importlib._bootstrap>", line 1014, in _gcd_import
  File "<frozen importlib._bootstrap>", line 991, in _find_and_load
  File "<frozen importlib._bootstrap>", line 975, in _find_and_load_unlocked
  File "<frozen importlib._bootstrap>", line 671, in _load_unlocked
  File "<frozen importlib._bootstrap_external>", line 783, in exec_module
  File "<frozen importlib._bootstrap>", line 219, in _call_with_frames_removed
  File "/home/jbal/PycharmProjects/djangoProject/djangoProject/urls.py", line 19, in <module>
    from Citatio import views
  File "/home/jbal/PycharmProjects/djangoProject/Citatio/views.py", line 7, in <module>
    from .forms import QuoteForm, DeleteQuotesForm
  File "/home/jbal/PycharmProjects/djangoProject/Citatio/forms.py", line 12, in <module>
    class DeleteQuotesForm(forms.Form):
  File "/home/jbal/PycharmProjects/djangoProject/Citatio/forms.py", line 14, in DeleteQuotesForm
    picked = forms.MultipleChoiceField(choices=Choices, widget=forms.CheckboxSelectMultiple)
  File "/home/jbal/PycharmProjects/djangoProject/venv/lib/python3.8/site-packages/django/forms/fields.py", line 780, in __init__
    self.choices = choices
  File "/home/jbal/PycharmProjects/djangoProject/venv/lib/python3.8/site-packages/django/forms/fields.py", line 797, in _set_choices
    value = list(value)
  File "/home/jbal/PycharmProjects/djangoProject/venv/lib/python3.8/site-packages/django/db/models/query.py", line 269, in __len__
    self._fetch_all()
  File "/home/jbal/PycharmProjects/djangoProject/venv/lib/python3.8/site-packages/django/db/models/query.py", line 1303, in _fetch_all
    self._result_cache = list(self._iterable_class(self))
  File "/home/jbal/PycharmProjects/djangoProject/venv/lib/python3.8/site-packages/django/db/models/query.py", line 142, in __iter__
    return compiler.results_iter(tuple_expected=True, chunked_fetch=self.chunked_fetch, chunk_size=self.chunk_size)
  File "/home/jbal/PycharmProjects/djangoProject/venv/lib/python3.8/site-packages/django/db/models/sql/compiler.py", line 1106, in results_iter
    results = self.execute_sql(MULTI, chunked_fetch=chunked_fetch, chunk_size=chunk_size)
  File "/home/jbal/PycharmProjects/djangoProject/venv/lib/python3.8/site-packages/django/db/models/sql/compiler.py", line 1154, in execute_sql
    cursor.execute(sql, params)
  File "/home/jbal/PycharmProjects/djangoProject/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 98, in execute
    return super().execute(sql, params)
  File "/home/jbal/PycharmProjects/djangoProject/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 66, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/home/jbal/PycharmProjects/djangoProject/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 75, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/home/jbal/PycharmProjects/djangoProject/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/home/jbal/PycharmProjects/djangoProject/venv/lib/python3.8/site-packages/django/db/utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/home/jbal/PycharmProjects/djangoProject/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.ProgrammingError: ERREUR:  la relation « Citatio_quote » n'existe pas
LINE 1: ...tatio_quote"."id", "Citatio_quote"."content" FROM "Citatio_q...
                                                             ^

And :

manage.py@djangoProject > migrate --plan
bash -cl "/home/jbal/PycharmProjects/djangoProject/venv/bin/python /opt/pycharm-professional/plugins/python/helpers/pycharm/django_manage.py migrate --plan /home/jbal/PycharmProjects/djangoProject"
Tracking file by folder pattern:  migrations
Traceback (most recent call last):
  File "/home/jbal/PycharmProjects/djangoProject/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
psycopg2.errors.UndefinedTable: ERREUR:  la relation « Citatio_quote » n'existe pas
LINE 1: ...tatio_quote"."id", "Citatio_quote"."content" FROM "Citatio_q...
                                                             ^


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

Traceback (most recent call last):
  File "/opt/pycharm-professional/plugins/python/helpers/pycharm/django_manage.py", line 52, in <module>
    run_command()
  File "/opt/pycharm-professional/plugins/python/helpers/pycharm/django_manage.py", line 46, in run_command
    run_module(manage_file, None, '__main__', True)
  File "/usr/lib/python3.8/runpy.py", line 207, in run_module
    return _run_module_code(code, init_globals, run_name, mod_spec)
  File "/usr/lib/python3.8/runpy.py", line 97, in _run_module_code
    _run_code(code, mod_globals, init_globals,
  File "/usr/lib/python3.8/runpy.py", line 87, in _run_code
    exec(code, run_globals)
  File "/home/jbal/PycharmProjects/djangoProject/manage.py", line 22, in <module>
    main()
  File "/home/jbal/PycharmProjects/djangoProject/manage.py", line 18, in main
    execute_from_command_line(sys.argv)
  File "/home/jbal/PycharmProjects/djangoProject/venv/lib/python3.8/site-packages/django/core/management/__init__.py", line 401, in execute_from_command_line
    utility.execute()
  File "/home/jbal/PycharmProjects/djangoProject/venv/lib/python3.8/site-packages/django/core/management/__init__.py", line 395, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/home/jbal/PycharmProjects/djangoProject/venv/lib/python3.8/site-packages/django/core/management/base.py", line 330, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/home/jbal/PycharmProjects/djangoProject/venv/lib/python3.8/site-packages/django/core/management/base.py", line 371, in execute
    output = self.handle(*args, **options)
  File "/home/jbal/PycharmProjects/djangoProject/venv/lib/python3.8/site-packages/django/core/management/base.py", line 85, in wrapped
    res = handle_func(*args, **kwargs)
  File "/home/jbal/PycharmProjects/djangoProject/venv/lib/python3.8/site-packages/django/core/management/commands/migrate.py", line 75, in handle
    self.check(databases=[database])
  File "/home/jbal/PycharmProjects/djangoProject/venv/lib/python3.8/site-packages/django/core/management/base.py", line 392, in check
    all_issues = checks.run_checks(
  File "/home/jbal/PycharmProjects/djangoProject/venv/lib/python3.8/site-packages/django/core/checks/registry.py", line 70, in run_checks
    new_errors = check(app_configs=app_configs, databases=databases)
  File "/home/jbal/PycharmProjects/djangoProject/venv/lib/python3.8/site-packages/django/core/checks/urls.py", line 13, in check_url_config
    return check_resolver(resolver)
  File "/home/jbal/PycharmProjects/djangoProject/venv/lib/python3.8/site-packages/django/core/checks/urls.py", line 23, in check_resolver
    return check_method()
  File "/home/jbal/PycharmProjects/djangoProject/venv/lib/python3.8/site-packages/django/urls/resolvers.py", line 408, in check
    for pattern in self.url_patterns:
  File "/home/jbal/PycharmProjects/djangoProject/venv/lib/python3.8/site-packages/django/utils/functional.py", line 48, in __get__
    res = instance.__dict__[self.name] = self.func(instance)
  File "/home/jbal/PycharmProjects/djangoProject/venv/lib/python3.8/site-packages/django/urls/resolvers.py", line 589, in url_patterns
    patterns = getattr(self.urlconf_module, "urlpatterns", self.urlconf_module)
  File "/home/jbal/PycharmProjects/djangoProject/venv/lib/python3.8/site-packages/django/utils/functional.py", line 48, in __get__
    res = instance.__dict__[self.name] = self.func(instance)
  File "/home/jbal/PycharmProjects/djangoProject/venv/lib/python3.8/site-packages/django/urls/resolvers.py", line 582, in urlconf_module
    return import_module(self.urlconf_name)
  File "/usr/lib/python3.8/importlib/__init__.py", line 127, in import_module
    return _bootstrap._gcd_import(name[level:], package, level)
  File "<frozen importlib._bootstrap>", line 1014, in _gcd_import
  File "<frozen importlib._bootstrap>", line 991, in _find_and_load
  File "<frozen importlib._bootstrap>", line 975, in _find_and_load_unlocked
  File "<frozen importlib._bootstrap>", line 671, in _load_unlocked
  File "<frozen importlib._bootstrap_external>", line 783, in exec_module
  File "<frozen importlib._bootstrap>", line 219, in _call_with_frames_removed
  File "/home/jbal/PycharmProjects/djangoProject/djangoProject/urls.py", line 19, in <module>
    from Citatio import views
  File "/home/jbal/PycharmProjects/djangoProject/Citatio/views.py", line 7, in <module>
    from .forms import QuoteForm, DeleteQuotesForm
  File "/home/jbal/PycharmProjects/djangoProject/Citatio/forms.py", line 12, in <module>
    class DeleteQuotesForm(forms.Form):
  File "/home/jbal/PycharmProjects/djangoProject/Citatio/forms.py", line 14, in DeleteQuotesForm
    picked = forms.MultipleChoiceField(choices=Choices, widget=forms.CheckboxSelectMultiple)
  File "/home/jbal/PycharmProjects/djangoProject/venv/lib/python3.8/site-packages/django/forms/fields.py", line 780, in __init__
    self.choices = choices
  File "/home/jbal/PycharmProjects/djangoProject/venv/lib/python3.8/site-packages/django/forms/fields.py", line 797, in _set_choices
    value = list(value)
  File "/home/jbal/PycharmProjects/djangoProject/venv/lib/python3.8/site-packages/django/db/models/query.py", line 269, in __len__
    self._fetch_all()
  File "/home/jbal/PycharmProjects/djangoProject/venv/lib/python3.8/site-packages/django/db/models/query.py", line 1303, in _fetch_all
    self._result_cache = list(self._iterable_class(self))
  File "/home/jbal/PycharmProjects/djangoProject/venv/lib/python3.8/site-packages/django/db/models/query.py", line 142, in __iter__
    return compiler.results_iter(tuple_expected=True, chunked_fetch=self.chunked_fetch, chunk_size=self.chunk_size)
  File "/home/jbal/PycharmProjects/djangoProject/venv/lib/python3.8/site-packages/django/db/models/sql/compiler.py", line 1106, in results_iter
    results = self.execute_sql(MULTI, chunked_fetch=chunked_fetch, chunk_size=chunk_size)
  File "/home/jbal/PycharmProjects/djangoProject/venv/lib/python3.8/site-packages/django/db/models/sql/compiler.py", line 1154, in execute_sql
    cursor.execute(sql, params)
  File "/home/jbal/PycharmProjects/djangoProject/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 98, in execute
    return super().execute(sql, params)
  File "/home/jbal/PycharmProjects/djangoProject/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 66, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/home/jbal/PycharmProjects/djangoProject/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 75, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/home/jbal/PycharmProjects/djangoProject/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/home/jbal/PycharmProjects/djangoProject/venv/lib/python3.8/site-packages/django/db/utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/home/jbal/PycharmProjects/djangoProject/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.ProgrammingError: ERREUR:  la relation « Citatio_quote » n'existe pas
LINE 1: ...tatio_quote"."id", "Citatio_quote"."content" FROM "Citatio_q...
                                                             ^


Process finished with exit code 1
4

Well. I finally managed to migrate to PostgreSQL… but I’m not sure what was the source of the problem.

To do it, I emptied any file in the app : views, urls, forms, serializers, … until I only had models. Then, I migrated.

When I put back the content of the files, I could see that views (and references to views) could be in cause. But I’m not sure.

Here is my views.py :

from django.http import HttpResponseRedirect, HttpResponse
from django.shortcuts import render, redirect
from django.urls import reverse, reverse_lazy
from django.views import generic, decorators
from django.contrib.auth.models import User, Group

from .forms import QuoteForm, DeleteQuotesForm
from .models import Quote, Author, Source
from .serializers import AuthorList, AuthorDetailAPI, GroupSerializer, UserSerializer

def index(request):
    latest_quote_list = Quote.objects.select_related('source', 'source__author')
    context = {'latest_quote_list': latest_quote_list}
    return render(request, 'Citatio/index.html', context)


class QuoteDetail(generic.DetailView):
    model = Quote
    template_name = 'Citatio/quote_detail.html'


class AuthorDetail(generic.DetailView):
    model = Author
    template_name = 'Citatio/author_detail.html'

    def get_context_data(self, **kwargs):
        context = super().get_context_data(**kwargs)
        context['author_source_list'] = Source.objects.all()
        return context


class SourceDetail(generic.DetailView):
    model = Source


def create_quote(request):
    if request.method == 'POST':
        form = QuoteForm(request.POST)
        if form.is_valid():
            form.save()
            return HttpResponseRedirect(reverse('Citatio/quote_list.html'))
    else:
        form = QuoteForm()
    return render(request, 'Citatio/quote_new.html', {'form': form})


class UpdateQuote(generic.UpdateView):
    model = Quote
    fields = ['content', 'source']
    template_name = 'Citatio/quote_update.html'


def quote_list(request):
    quote_list = Quote.objects.select_related('source', 'source__author')
    return render(request, 'Citatio/quote_list.html', {'quote_list': quote_list})


def delete_quote(request):
    if request.method == 'POST':
        form = DeleteQuotesForm(request.POST)
        if form.is_valid():
            todelete = form.cleaned_data['picked']
            Quote.objects.filter(id__in=todelete).delete()
        return HttpResponseRedirect(reverse('Citatio:quote_list'))
    else:
        form = DeleteQuotesForm
    return render(request, 'Citatio/delete_quote.html', {'form': form})

App urls.py :

from django.urls import path
from django.conf.urls import url
from . import views

app_name = 'Citatio'
urlpatterns = [
     path('', views.index, name='index'),
     path('quote/<int:pk>', views.QuoteDetail.as_view(), name='quote_detail'),
     path('author/<int:pk>', views.AuthorDetail.as_view(), name='detail_author'),
     path('source/<int:pk>', views.SourceDetail.as_view(), name='detail_source'),
     path('create_quote/', views.create_quote, name='quote_new'),
     path('quote/<int:pk>/update', views.UpdateQuote.as_view(), name='update_quote'),
     path('quote_list/', views.quote_list, name='quote_list'),
     path('delete_quote/', views.delete_quote, name='delete_quote'),
     url(r'^api/$', views.author_list_api),
     url(r'^api/(?P<pk>[0-9]+)/$', views.author_detail_api)
          ]

Projet urls.py :

from django.contrib import admin
from django.urls import include, path
from rest_framework import routers
from Citatio import views

router = routers.DefaultRouter()
#router.register(r'users', views.UserViewSet)
#router.register(r'groups', views.GroupViewSet)

urlpatterns = [
    path('Citatio/', include('Citatio.urls')),
    path('admin/', admin.site.urls),
    path('accounts/', include('django.contrib.auth.urls')),
    path('api-auth/', include('rest_framework.urls')),
    path('', include(router.urls)),
]

If someone can help me understand why it works now, I would be glad to learn :slight_smile:

5

It seems to me that the problem comes from your DeleteQuotesForm.

I assume Django needs to do some get some data from the DB to create that form and it raises an error because the relation doesn’t exists (in the DB).

Anyway, take this with a grain of salt.

6

I’d check that your migrations have the correct dependencies defined on them. It sounds like the migration had a data integrity issue where a FK field did not have a corresponding PK in the referenced table. Given that you were able to eventually get this to a working state leads me to believe that your migrations are correct, but they were running out of order.

The best place to start are any migrations that move data around and the migrations for the models and/or fields that would then depend on that data being moved over.