Upgrade fails 3.1.x to 3.7.8

[inDane]

Deployment Type

Self-hosted

NetBox Version

v3.1.x

Python Version

3.10

Steps to Reproduce

Python Version is 3.8.

cd /opt/netbox
sudo git pull
sudo git checkout v3.7.8
sudo ./upgrade.sh

Expected Behavior

The upgrade script should succeed.

Observed Behavior

The upgrade-script fails at db migration. It cant find file "base/16384/24621" and fails.

Operations to perform:
  Apply all migrations: account, admin, auth, circuits, contenttypes, core, dcim, django_rq, extras, ipam, sessions, social_django, taggit, tenancy, users, virtualization, vpn, wireless
Running migrations:
  Applying dcim.0144_fix_cable_abs_length... OK
  Applying dcim.0145_site_remove_deprecated_fields... OK
  Applying users.0002_standardize_id_fields... OK
  Applying users.0003_token_allowed_ips_last_used... OK
  Applying users.0004_netboxgroup_netboxuser... OK
  Applying account.0001_initial... OK
  Applying wireless.0002_standardize_id_fields... OK
  Applying wireless.0003_created_datetimefield... OK
  Applying tenancy.0005_standardize_id_fields... OK
  Applying tenancy.0006_created_datetimefield... OK
  Applying tenancy.0007_contact_link... OK
  Applying wireless.0004_wireless_tenancy... OK
  Applying ipam.0054_vlangroup_min_max_vids... OK
  Applying extras.0068_configcontext_cluster_types...Traceback (most recent call last):
  File "/opt/netbox-git/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 87, in _execute
    return self.cursor.execute(sql)
  File "/opt/netbox-git/venv/lib/python3.8/site-packages/psycopg/cursor.py", line 732, in execute
    raise ex.with_traceback(None)
psycopg.errors.UndefinedFile: konnte Datei »base/16384/24621« nicht öffnen: Datei oder Verzeichnis nicht gefunden
CONTEXT:  SQL-Anweisung »SELECT fk."configcontext_id" FROM ONLY "public"."extras_configcontext_cluster_types" fk LEFT OUTER JOIN ONLY "public"."extras_configcontext" pk ON ( pk."id" OPERATOR(pg_catalog.=) fk."configcontext_id") WHERE pk."id" IS NULL AND (fk."configcontext_id" IS NOT NULL)«

[candlerb]

base/16384/24621 sounds like a chunk of the Postgres database itself (on Ubuntu these are /var/lib/postgresql/12/main/base/<NNN>/<MMM>)

The error therefore strongly suggests that your database is corrupt, or perhaps you ran out of disk space.

Either way, I'd suggest that you restore your postgres database from a backup before continuing.

[inDane]

pg_dump netbox
...
pg_dump: error: Dumping the contents of table "extras_configcontext" failed: PQgetResult() failed.
pg_dump: error: Error message from server: FEHLER:  konnte Datei »base/16384/24621« nicht öffnen: Datei oder Verzeichnis nicht gefunden
pg_dump: error: The command was: COPY public.extras_configcontext (id, name, weight, description, is_active, data, created, last_updated) TO stdout;

This line however, does not fail.
pg_dump -s -t extras_configcontext netbox > netbox_only_extra_configcontext.sql
Which I do not understand...

EDIT: ah, thats what you ment earlier "keeps the schema, just not the data."

Yes, I will learn from that and add a daily pg_dump. I thought a regular fs backup would suffice.

[inDane]

pg_dump -t extras_configcontext netbox > netbox_test.sql

pg_dump: error: Dumping the contents of table "extras_configcontext" failed: PQgetResult() failed.
pg_dump: error: Error message from server: FEHLER:  konnte Datei »base/16384/24621« nicht öffnen: Datei oder Verzeichnis nicht gefunden
pg_dump: error: The command was: COPY public.extras_configcontext (id, name, weight, description, is_active, data, created, last_updated) TO stdout;

[inDane]

i've created a new database netbox2, ran ./upgrade.sh and restored the dump from
pg_dump --format=custom -T extras_configcontext netbox > netbox_exclude_extras_configcontext.dump
via
pg_restore --clean -d netbox2 netbox_exclude_extras_configcontext.dump

The netbox webpage is loadin, but the result has no devices in it (FOREIGN_KEY errors while restoring).

Im afraid i have to scavenge the "working" instance with the corrupted database by hand...

[candlerb]

It's very hard to help if you don't show actual error messages.

The foreign key errors presumably are references to missing rows in extras_configcontext. However, "device" doesn't have a direct FK relationship to extras_configcontext as far as I can tell (it has local_context_data but that's just JSON data)

It must be something else, but I'm not prepared to play guessing games.

If you can identify which fields have foreign key relationships to extras_configcontext, then you can null them out by hand - if necessary in the raw dump itself, before restoring it. Good luck.

[inDane]

uhm, sorry, i didnt want to make you play guessing games.
Its merely that i do not know what you need and since pg_restore shows a very long output, i didnt think of posting it here.

i did pg_restore --clean -d netbox2 netbox_exclude_extras_configcontext.dump a few times, because i realized, that the error count got less with each time until it stayed at 82 errors.
pg_restore: warning: errors ignored on restore: 82
And actually after spamming it a few times, the devices are showing up! I have no explanation why this is the case.

I have added the file containing the output generated by that command:
pg_restore.output.txt

Sorry again for leaving you in the dark, that was not my intention.

EDIT:
What did I do:

# Create new database like in https://docs.netbox.dev/en/stable/installation/1-postgresql/
# alongside corrupt netbox database.

CREATE DATABASE netbox2;
ALTER DATABASE netbox2 OWNER TO netbox;

# change database in configuration.py
/opt/netbox/netbox/netbox/configuration.py

DATABASE = {
    'NAME': 'netbox2',         # Database name
    'USER': 'netbox',               # PostgreSQL username
    'PASSWORD': 'xyz',           # PostgreSQL password
    'HOST': 'localhost',      # Database server
    'PORT': '',               # Database port (leave blank for default)
    'CONN_MAX_AGE': 300,      # Max database connection age
}

# run ./upgrade.sh from /opt/netbox to initialize the fresh netbox2 db
./upgrade.sh

# Dump existing database excluding corrupt area.
pg_dump --format=custom -T extras_configcontext netbox > netbox_exclude_extras_configcontext.dump

# Restore into newly created database netbox2
pg_restore --clean -d netbox2 netbox_exclude_extras_configcontext.dump

# Spam this until the error count does not decrease