Challenges with restore

[Petrox]

I'm trying to work out a process where I can reliably automatically reload a backup from a production system onto a staging/dev environment.

The journey was more than simple so let me share the steps I took, with the key takeaways at the end.

• the staging instance MUST HAVE the same pg username as the one used by the system taking the backup (not documented)
• backups filenames lack inventree version, which might affect the recovery process (they contain the docker container id which is not enough information)
• backups don't contain the .env and config.yml settings (technically it shouldn't be zipped straight, but exported as a safe subset of all settings without passwords, keys) this makes restore less than 100%
• backups don't contain the plugins.txt so one can not be sure which backup was done with which plugin was installed (at which version!)
• backups don't contain which plugin were enabled and which were not. (technically they contain them in the pgsqldump but we can't load those without enabling the plugins first manually so chicken-egg problem)
• backups don't contain any custom changes from static folder (like the images, css, etc)
• restore fails when launching a clean instance (deleted plugins, media, psql folders and anything besides plugins.txt, secret_key and backups folder) and trying to restore a backup which contains plugin modified database (when the new instance does not include the plugin (yet))

pg_restore: error: could not execute query: ERROR:  relation "public.inventree_bulk_plugin_bulkcreationtemplate" does not exist
Command was: ALTER TABLE ONLY public.inventree_bulk_plugin_bulkcreationtemplate DROP CONSTRAINT inventree_bulk_plugin_bulkcreationtemplate_pkey;

Since plugins are not enabled by default even though I put the correct plugins.txt to the right place:

• I have to start the empty instance,
• Log in through a browser
• Enter admin
• Enable the plugins one-by-one (maybe not all of them were enabled on the original server, but we won't know because it's not stored, so it's just a memory test)
• stop
• migrate
• start
• stop
• migrate

(it does not look like it's doing anything)

[+] Building 0.0s (0/0)
[+] Creating 1/0
 ✔ Container invtreepg-inventree-db-1  Running                                                                     0.0s
[+] Building 0.0s (0/0)
Loading config file : /home/inventree/data/config.yaml
Running InvenTree database migrations...
========================================
WARNING: Running pip as the 'root' user can result in broken permissions and conflicting behaviour with the system package manager. It is recommended to use a virtual environment instead: https://pip.pypa.io/warnings/venv

[notice] A new release of pip is available: 23.2.1 -> 23.3
[notice] To update, run: pip install --upgrade pip
No changes detected
WARNING: Running pip as the 'root' user can result in broken permissions and conflicting behaviour with the system package manager. It is recommended to use a virtual environment instead: https://pip.pypa.io/warnings/venv

[notice] A new release of pip is available: 23.2.1 -> 23.3
[notice] To update, run: pip install --upgrade pip
Operations to perform:
  Apply all migrations: InvenTree, account, admin, auth, authtoken, build, common, company, contenttypes, django_q, error_report, exchange, flags, label, order, otp_static, otp_totp, part, plugin, report, sites, socialaccount, stock, taggit, user_sessions, users
Running migrations:
  No migrations to apply.
WARNING: Running pip as the 'root' user can result in broken permissions and conflicting behaviour with the system package manager. It is recommended to use a virtual environment instead: https://pip.pypa.io/warnings/venv

[notice] A new release of pip is available: 23.2.1 -> 23.3
[notice] To update, run: pip install --upgrade pip
Operations to perform:
  Synchronize unmigrated apps: allauth, allauth_2fa, corsheaders, crispy_forms, dbbackup, dj_rest_auth, django_cleanup, django_filters, django_ical, django_otp, djmoney, drf_spectacular, formtools, generic, import_export, maintenance_mode, markdownify, messages, mptt, registration, rest_framework, staticfiles
  Apply all migrations: InvenTree, account, admin, auth, authtoken, build, common, company, contenttypes, django_q, error_report, exchange, flags, label, order, otp_static, otp_totp, part, plugin, report, sites, socialaccount, stock, taggit, user_sessions, users
Synchronizing apps without migrations:
  Creating tables...
    Running deferred SQL...
Running migrations:
  No migrations to apply.
System check identified no issues (1 silenced).
========================================
InvenTree database migrations completed!
Rebuilding Part objects
Rebuilding PartCategory objects
Rebuilding StockItem objects
Rebuilding StockLocation objects
Rebuilding Build objects

Still got error:

pg_restore: error: could not execute query: ERROR:  relation "public.inventree_bulk_plugin_bulkcreationtemplate" does not exist
Command was: ALTER TABLE ONLY public.inventree_bulk_plugin_bulkcreationtemplate DROP CONSTRAINT inventree_bulk_plugin_bulkcreationtemplate_pkey;

• source containing the tables are here: https://github.com/wolflu05/inventree-bulk-plugin/blob/main/inventree_bulk_plugin/migrations/0001_initial.py
• I just don't know how to trigger this code to run
• And why it didn't run when it was installed, enabled or migrate was specifically launched ( https://docs.inventree.org/en/latest/extend/plugins/install/#container-environments does not mention this)
• Even if I update the inventree to a newer version and invoke migrate, still those tables are not generated
• Then I recalled that there are some permissions to allow... app integration, url integration (which settings are not stored alongside the backup, but probably within the psql dump which we can't load yet)
• So manual api and url integration enabled manually
• stop server
  Applying inventree_bulk_plugin.0001_initial... OK

Running migrate:

[+] Building 0.0s (0/0)
[+] Creating 1/0
 ✔ Container invtreepg-inventree-db-1  Running                                                                     0.0s
[+] Building 0.0s (0/0)
Loading config file : /home/inventree/data/config.yaml
Restoring InvenTree database...
Finding latest backup
Restoring backup for database 'default' and server 'None'
Restoring: default-5ded7c8ccc7d-2023-10-10-034833.psql.bin.gz
Restore tempfile created: 693.3 KiB
Restoring InvenTree media files...
Restoring backup for media files
Finding latest backup
Restoring: 5ded7c8ccc7d-2023-10-10-034841.tar.gz

success.gif

TLDR:

• There are missing information from the backup process (plugins, enabled plugins, permissions enabled)
• There are unrecoverable information stored in the backup which is necessary for the restore to work
• There are manual steps that is officially done through the webpage (maybe enabling these permissions and plugins can be done via the invoke method, just not documented)
• DB Migration generally works well, but since the backup inventree version and the current inventree version might not match this could cause serious trouble.
• Restore procedure needs more detailed documentation than the current "invoke restore"

[Petrox]

Just to recap what is necessary for if someone wants a secondary playground inventree server to restore the backup from the production (docker based)

WARNING: this procedure causes loss of data stored in the playground system

# make sure the .env and config.yml and plugins.txt are nearly the same on the prod and playground systems (except the playground MUST HAVE a different login background and a header message visible)  the postgres username and database name MUST be the same. The http port MUST be different if running on the same server.
# volumes/playground is the docker data volume used by the secondary system
# volumes/prod is the docker volume used by the production system
sudo mkdir -p volumes/playground/backups/rolling #this will store the backups made by the playground system. 
sudo mkdir -p volumes/playground/backups/to_restore

cd $PRODUCTIONfolder
docker compose run inventree-server invoke backup
cd $PLAYGROUNDfolder
docker compose run inventree-server invoke backup
sudo mv volumes/playground/backups/*.gz volumes/playground/backups/rolling
## manually copy volumes/prod/backups/ latest two gz files to volumes/playground/to_restore  (TODO needs cli solution)

docker compose down
sudo rm -rf volumes/playground/pgdb
sudo rm -rf volumes/playground/media
sudo rm -rf volumes/playground/plugins
docker compose run inventree-server invoke update
docker compose up -d
## double start since some settings require restart already at first start
docker compose down
docker compose up -d

### NOW log into the clean fresh playground as admin, go to Settings / plugins / enable any permissions you need (TODO needs cli solution)
docker compose down
docker compose up -d
## double start since some settings require restart already at first start
docker compose down
docker compose up -d
### NOW log into the playground (still empty db), go to settings and enable all plugins that are enabled in the production system (TODO needs cli solution)
docker compose down
docker compose run inventree-server invoke update
docker compose up -d
docker compose down
## since the invoke update creates backups on the playground system (of the empty database) and restore always restores the latest one, we need to move those to rolling
sudo mv volumes/playground/backups/*.gz volumes/playground/backups/rolling
# we copy the real production backup to the backup folder of the playground system
sudo cp volumes/playground/backups/to_restore/*.gz volumes/playground/backups/
docker compose run inventree-server invoke restore
docker compose run inventree-server invoke update
docker compose down
docker compose up -d
## double start since some settings require restart already at first start
docker compose down
docker compose up -d

## the playground should now serve the same data and media restored from the production backup