"ProgrammingError" after Database Migration

[duggym122]

Issue

I upgraded from postgres 11 to postgres 16 successfully, tested using a blank db before clearing the db, letting postgres 16 set it up empty, and then loading in the old postgres 11 dump, all per the Thomas Brandt guide.

After importing, restarting the db container, then allowing the tandoor container to start and perform its migration steps, it shows a "Programming Error" screen with the following key points (full traceback below that)

Exception Type: ProgrammingError at /accounts/login/
Exception Value: column socialaccount_socialapp.provider_id does not exist
LINE 1: ...lapp"."id", "socialaccount_socialapp"."provider", "socialacc...
                                                             ^
HINT:  Perhaps you meant to reference the column "socialaccount_socialapp.provider".

Environment:


Request Method: GET
Request URL: https://recipes.dougmelvin.me/accounts/login/?next=/search/

Django Version: 4.2.11
Python Version: 3.12.2
Installed Applications:
['django.contrib.admin',
 'django.contrib.auth',
 'django.contrib.contenttypes',
 'django.contrib.sessions',
 'django.contrib.messages',
 'django.contrib.sites',
 'django.contrib.staticfiles',
 'django.contrib.postgres',
 'oauth2_provider',
 'django_tables2',
 'corsheaders',
 'crispy_forms',
 'crispy_bootstrap4',
 'rest_framework',
 'rest_framework.authtoken',
 'django_cleanup.apps.CleanupConfig',
 'webpack_loader',
 'django_js_reverse',
 'hcaptcha',
 'allauth',
 'allauth.account',
 'allauth.socialaccount',
 'cookbook.apps.CookbookConfig',
 'treebeard',
 'debug_toolbar']
Installed Middleware:
['corsheaders.middleware.CorsMiddleware',
 'django.middleware.security.SecurityMiddleware',
 'whitenoise.middleware.WhiteNoiseMiddleware',
 'django.contrib.sessions.middleware.SessionMiddleware',
 'django.middleware.common.CommonMiddleware',
 'django.middleware.csrf.CsrfViewMiddleware',
 'django.contrib.auth.middleware.AuthenticationMiddleware',
 'django.contrib.messages.middleware.MessageMiddleware',
 'django.middleware.locale.LocaleMiddleware',
 'django.middleware.clickjacking.XFrameOptionsMiddleware',
 'cookbook.helper.scope_middleware.ScopeMiddleware',
 'allauth.account.middleware.AccountMiddleware',
 'debug_toolbar.middleware.DebugToolbarMiddleware']


Template error:
In template /opt/recipes/cookbook/templates/account/login.html, error at line 45
   column socialaccount_socialapp.provider_id does not exist
LINE 1: ...lapp"."id", "socialaccount_socialapp"."provider", "socialacc...
                                                             ^
HINT:  Perhaps you meant to reference the column "socialaccount_socialapp.provider".

   35 :                 {% endif %}
   36 : 
   37 :                 {% if EMAIL_ENABLED %}
   38 :                     <p>{% trans 'Lost your password?' %} <a
   39 :                             href="{% url 'account_reset_password' %}">{% trans "Reset My Password" %}</a></p>
   40 :                 {% endif %}
   41 :             </form>
   42 :         </div>
   43 :     </div>
   44 : 
   45 :      {% get_providers as socialaccount_providers %} 
   46 : 
   47 :     {% if socialaccount_providers %}
   48 :         <div class="row" style="margin-top: 2vh">
   49 :             <div class="col-sm-12 col-lg-6 col-md-6 offset-lg-3 offset-md-3">
   50 :                 <h5>{% trans "Social Login" %}</h5>
   51 :                 <span>{% trans 'You can use any of the following providers to sign in.' %}</span>
   52 : 
   53 :                 <br/>
   54 :                 <br/>
   55 :                 <ul class="socialaccount_providers list-unstyled">


Traceback (most recent call last):
  File "/opt/recipes/venv/lib/python3.12/site-packages/django/db/backends/utils.py", line 89, in _execute
    return self.cursor.execute(sql, params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

The above exception (column socialaccount_socialapp.provider_id does not exist
LINE 1: ...lapp"."id", "socialaccount_socialapp"."provider", "socialacc...
                                                             ^
HINT:  Perhaps you meant to reference the column "socialaccount_socialapp.provider".
) was the direct cause of the following exception:
  File "/opt/recipes/venv/lib/python3.12/site-packages/django/core/handlers/exception.py", line 55, in inner
    response = get_response(request)
               ^^^^^^^^^^^^^^^^^^^^^
  File "/opt/recipes/venv/lib/python3.12/site-packages/django/core/handlers/base.py", line 220, in _get_response
    response = response.render()
               ^^^^^^^^^^^^^^^^^
  File "/opt/recipes/venv/lib/python3.12/site-packages/django/template/response.py", line 114, in render
    self.content = self.rendered_content
                   ^^^^^^^^^^^^^^^^^^^^^
  File "/opt/recipes/venv/lib/python3.12/site-packages/django/template/response.py", line 92, in rendered_content
    return template.render(context, self._request)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/recipes/venv/lib/python3.12/site-packages/django/template/backends/django.py", line 61, in render
    return self.template.render(context)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/recipes/venv/lib/python3.12/site-packages/django/template/base.py", line 175, in render
    return self._render(context)
           ^^^^^^^^^^^^^^^^^^^^^
  File "/opt/recipes/venv/lib/python3.12/site-packages/django/test/utils.py", line 112, in instrumented_test_render
    return self.nodelist.render(context)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/recipes/venv/lib/python3.12/site-packages/django/template/base.py", line 1005, in render
    return SafeString("".join([node.render_annotated(context) for node in self]))
                               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/recipes/venv/lib/python3.12/site-packages/django/template/base.py", line 966, in render_annotated
    return self.render(context)
           ^^^^^^^^^^^^^^^^^^^^
  File "/opt/recipes/venv/lib/python3.12/site-packages/django/template/loader_tags.py", line 157, in render
    return compiled_parent._render(context)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/recipes/venv/lib/python3.12/site-packages/django/test/utils.py", line 112, in instrumented_test_render
    return self.nodelist.render(context)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/recipes/venv/lib/python3.12/site-packages/django/template/base.py", line 1005, in render
    return SafeString("".join([node.render_annotated(context) for node in self]))
                               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/recipes/venv/lib/python3.12/site-packages/django/template/base.py", line 966, in render_annotated
    return self.render(context)
           ^^^^^^^^^^^^^^^^^^^^
  File "/opt/recipes/venv/lib/python3.12/site-packages/django/template/loader_tags.py", line 63, in render
    result = block.nodelist.render(context)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/recipes/venv/lib/python3.12/site-packages/django/template/base.py", line 1005, in render
    return SafeString("".join([node.render_annotated(context) for node in self]))
                               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/recipes/venv/lib/python3.12/site-packages/django/template/base.py", line 966, in render_annotated
    return self.render(context)
           ^^^^^^^^^^^^^^^^^^^^
  File "/opt/recipes/venv/lib/python3.12/site-packages/django/template/library.py", line 237, in render
    output = self.func(*resolved_args, **resolved_kwargs)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/recipes/venv/lib/python3.12/site-packages/allauth/socialaccount/templatetags/socialaccount.py", line 79, in get_providers
    providers = adapter.list_providers(request)
                ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/recipes/venv/lib/python3.12/site-packages/allauth/socialaccount/adapter.py", line 193, in list_providers
    apps = self.list_apps(request)
           ^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/recipes/venv/lib/python3.12/site-packages/allauth/socialaccount/adapter.py", line 251, in list_apps
    for app in db_apps:
    ^^^^^^^^^^^^^^^^^^^^
  File "/opt/recipes/venv/lib/python3.12/site-packages/django/db/models/query.py", line 398, in __iter__
    self._fetch_all()
    ^^^^^^^^^^^^^^^^^
  File "/opt/recipes/venv/lib/python3.12/site-packages/django/db/models/query.py", line 1881, in _fetch_all
    self._result_cache = list(self._iterable_class(self))
                         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/recipes/venv/lib/python3.12/site-packages/django/db/models/query.py", line 91, in __iter__
    results = compiler.execute_sql(
              
  File "/opt/recipes/venv/lib/python3.12/site-packages/django/db/models/sql/compiler.py", line 1562, in execute_sql
    cursor.execute(sql, params)
    ^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/recipes/venv/lib/python3.12/site-packages/django/db/backends/utils.py", line 102, in execute
    return super().execute(sql, params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/recipes/venv/lib/python3.12/site-packages/django/db/backends/utils.py", line 67, in execute
    return self._execute_with_wrappers(
           
  File "/opt/recipes/venv/lib/python3.12/site-packages/django/db/backends/utils.py", line 80, in _execute_with_wrappers
    return executor(sql, params, many, context)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/recipes/venv/lib/python3.12/site-packages/django/db/backends/utils.py", line 84, in _execute
    with self.db.wrap_database_errors:
    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/recipes/venv/lib/python3.12/site-packages/django/db/utils.py", line 91, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/recipes/venv/lib/python3.12/site-packages/django/db/backends/utils.py", line 89, in _execute
    return self.cursor.execute(sql, params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Exception Type: ProgrammingError at /accounts/login/
Exception Value: column socialaccount_socialapp.provider_id does not exist
LINE 1: ...lapp"."id", "socialaccount_socialapp"."provider", "socialacc...
                                                             ^
HINT:  Perhaps you meant to reference the column "socialaccount_socialapp.provider".

Tandoor Version

Can't see the version, docker hash as follows: sha256:177a634dc07d0b94013dd94c98174836fa00e8513829fd47467e725964857126

OS Version

Synology DSM 7.1.1-42962

Setup

Docker / Docker-Compose

Reverse Proxy

Nginx Proxy Manager (NPM)

Other

Portainer-managed docker

Environment file

DEBUG=0
SQL_DEBUG=0
TANDOOR_PORT=8080
ALLOWED_HOSTS=recipes.domain.tld, *.domain.tld
SECRET_KEY=REDACTED
TIMEZONE=America/Denver
DB_ENGINE=django.db.backends.postgresql
POSTGRES_HOST=tandoor-db
POSTGRES_PORT=5432
POSTGRES_USER=djangouser
POSTGRES_PASSWORD="REDACTED"
POSTGRES_DB=djangodb
FRACTION_PREF_DEFAULT=1
COMMENT_PREF_DEFAULT=0
SHOPPING_MIN_AUTOSYNC_INTERVAL=5
STICKY_NAV_PREF_DEFAULT=1
GUNICORN_MEDIA=0
REVERSE_PROXY_AUTH=0
ENABLE_SIGNUP=0
ENABLE_PDF_EXPORT=1
EXPORT_FILE_CACHE_DURATION=600
DOCKER_PATH_TANDOOR=tandoor
DOCKER_PATH_POSTGRES=postgres/postgres-16
FS_DOCKER_BASE=/volume1/docker
UID_TANDOOR_APP=1002
GID_TANDOOR_APP=65537
UID_TANDOOR_DB=1026
GID_TANDOOR_DB=65537

Docker-Compose file

version: "3"
services:
  tandoor-app:
    image: vabene1111/recipes:latest
    container_name: tandoor-app
    environment:
      - SECRET_KEY=$SECRET_KEY
      - DB_ENGINE=$DB_ENGINE
      - POSTGRES_HOST=$POSTGRES_HOST
      - POSTGRES_PORT=$POSTGRES_PORT
      - POSTGRES_USER=$POSTGRES_USER
      - POSTGRES_PASSWORD=$POSTGRES_PASSWORD
      - POSTGRES_DB=$POSTGRES_DB
    ports:
      - 40281:8080
    volumes:
      - $FS_DOCKER_BASE/$DOCKER_PATH_TANDOOR/static:/opt/recipes/staticfiles
      - $FS_DOCKER_BASE/$DOCKER_PATH_TANDOOR/media:/opt/recipes/mediafiles
    restart: unless-stopped
    links:
      - "tandoor-db:tandoor-db"
    depends_on:

      - tandoor-db
  tandoor-db:
    image: postgres:16-alpine
    container_name: tandoor-db
    volumes:
      - $FS_DOCKER_BASE/$DOCKER_PATH_POSTGRES/tandoor-db:/var/lib/postgresql/data
    env_file:
      - stack.env
    restart: always

Relevant logs

No response

[smilerz]

please enable debug and share the tandoor logs from first boot

[duggym122]

@smilerz I get a slightly different error about a duplicate RecipeBook this time

Checking configuration...
Waiting for database to be ready...
Database is ready
Migrating database
Operations to perform:
  Apply all migrations: account, admin, auth, authtoken, contenttypes, cookbook, oauth2_provider, sessions, sites, socialaccount
Running migrations:
Traceback (most recent call last):
  File "/opt/recipes/manage.py", line 15, in <module>
    execute_from_command_line(sys.argv)
  File "/opt/recipes/venv/lib/python3.12/site-packages/django/core/management/__init__.py", line 442, in execute_from_command_line
    utility.execute()
  File "/opt/recipes/venv/lib/python3.12/site-packages/django/core/management/__init__.py", line 436, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/opt/recipes/venv/lib/python3.12/site-packages/django/core/management/base.py", line 412, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/opt/recipes/venv/lib/python3.12/site-packages/django/core/management/base.py", line 458, in execute
    output = self.handle(*args, **options)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/recipes/venv/lib/python3.12/site-packages/django/core/management/base.py", line 106, in wrapper
    res = handle_func(*args, **kwargs)
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/recipes/venv/lib/python3.12/site-packages/django/core/management/commands/migrate.py", line 356, in handle
    post_migrate_state = executor.migrate(
                         ^^^^^^^^^^^^^^^^^
  File "/opt/recipes/venv/lib/python3.12/site-packages/django/db/migrations/executor.py", line 135, in migrate
    state = self._migrate_all_forwards(
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/recipes/venv/lib/python3.12/site-packages/django/db/migrations/executor.py", line 167, in _migrate_all_forwards
    state = self.apply_migration(
            ^^^^^^^^^^^^^^^^^^^^^
  File "/opt/recipes/venv/lib/python3.12/site-packages/django/db/migrations/executor.py", line 252, in apply_migration
    state = migration.apply(state, schema_editor)
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/recipes/venv/lib/python3.12/site-packages/django/db/migrations/migration.py", line 132, in apply
    operation.database_forwards(
  File "/opt/recipes/venv/lib/python3.12/site-packages/django/db/migrations/operations/special.py", line 193, in database_forwards
    self.code(from_state.apps, schema_editor)
  File "/opt/recipes/cookbook/migrations/0200_alter_propertytype_options_remove_keyword_icon_and_more.py", line 33, in migrate_icons
    raise RuntimeError(f'Duplicate RecipeBook found, please remove/rename them and run migrations again/restart the container. {duplicate_meal_types}')
RuntimeError: Duplicate RecipeBook found, please remove/rename them and run migrations again/restart the container. <QuerySet [{'space_id': 3, 'name': 'Cocktails', 'name_count': 2}]>
  Applying cookbook.0200_alter_propertytype_options_remove_keyword_icon_and_more...Generating static files
js-reverse file written to /opt/recipes/cookbook/static/django_js_reverse
1 static file copied to '/opt/recipes/staticfiles', 574 unmodified, 1185 post-processed.
Done
[2024-07-02 15:21:30 +0000] [1] [INFO] Starting gunicorn 21.2.0
[2024-07-02 15:21:30 +0000] [1] [INFO] Listening at: http://[::]:8080 (1)
[2024-07-02 15:21:30 +0000] [1] [INFO] Using worker: gthread
[2024-07-02 15:21:30 +0000] [17] [INFO] Booting worker with pid: 17
[2024-07-02 15:21:31 +0000] [18] [INFO] Booting worker with pid: 18
[2024-07-02 15:21:31 +0000] [19] [INFO] Booting worker with pid: 19

[smilerz]

Duplicate RecipeBook found

[duggym122]

@smilerz this really isn't advice, just quoting my own message back to me - how do I fix it if it's not true? And/or what debugging tool/ui do I use to figure it out?

[smilerz]

@smilerz this really isn't advice, just quoting my own message back to me - how do I fix it if it's not true? And/or what debugging tool/ui do I use to figure it out?

you need to go into the database, find the duplicate name and delete it.

Alternatively, return the state to a prior working version and find the duplicate recipe book and delete it from the Tandoor UI

[duggym122]

you need to go into the database, find the duplicate name and delete it.

Can do, but I'm hoping I can try your other suggestion first, just to avoid a total clustertruck in the event I botch it. Luckily I work with a few former and current DBAs who I might be able to call in for a favor.

Alternatively, return the state to a prior working version and find the duplicate recipe book and delete it from the Tandoor UI

I tried to find documentation of version compatibility, but got lost - any idea what Tandoor version last supported postgres 11 by any chance? I figured this would work, but I couldn't seem to go back far enough in the tags.

[smilerz]

Unfortunately, it would have to be the exact version that you had installed previously.

[vabene1111]

sorry for the inconvenice on this, this was a very old inconsistency error that came up trough a migration a few month ago. You need to remove the duplicates (there are maybe more than one), after that just run the migrations again. The changelog says on which version Postgres 11 support was dropped, i would migrate first to that version and then upgrade to pg 16 and then do the last few migrations which should run smoothly.