Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Issue in restoring the backed data via invoke restore #8605

Closed
2 of 6 tasks
Jaishankar872 opened this issue Dec 1, 2024 · 15 comments
Closed
2 of 6 tasks

Issue in restoring the backed data via invoke restore #8605

Jaishankar872 opened this issue Dec 1, 2024 · 15 comments
Labels
question This is a question setup Relates to the InvenTree setup / installation process

Comments

@Jaishankar872
Copy link

Please verify that this bug has NOT been raised before.

  • I checked and didn't find a similar issue

Describe the bug*

I want to have a proper backup and restore plan for Inventree database and media. Using the invoke created the backup manually for database and media. But restore of this same data in not working at all. Tried all the combination.

Steps to Reproduce

Creating backup in the existing running system

  1. Data Backup via command invoke backup
sudo docker compose run --rm inventree-server invoke backup
  1. Note down database Username and password from .env file
INVENTREE_DB_USER=<username>
INVENTREE_DB_PASSWORD=<password>
  1. Finally Transfer or store into another system

Restore the data backup in other new system

  1. Create the basic Required file as per the document link
  2. Update database Username and password in .env file
INVENTREE_DB_USER=<username>
INVENTREE_DB_PASSWORD=<password>
  1. Copying backup data from $BACKUP_SOURCE_PATH to the backup folder
sudo cp $BACKUP_SOURCE_PATH/*.gz $INVENTREE_DATA_PATH/backup/
  1. Prepare the restore database and media file via invoke restore (Giving error)
sudo docker compose run --rm inventree-server invoke install
sudo docker compose run --rm inventree-server invoke restore

OR Tried Individually also (Giving error)

sudo docker compose run --rm inventree-server invoke install
sudo docker compose run --rm inventree-server invoke restore -g
sudo docker compose run --rm inventree-server invoke restore -i

Note: I tried without applying the invoke install or invoke update --skip-backupstep, still issue is there.

  1. Starting all Docker containers via compose.
sudo docker compose up --remove-orphans -d

Expected behaviour

Success database and media file restore. In database restore is failed. For details refer the log section.

Deployment Method

  • Docker
  • Package
  • Bare metal
  • Other - added info in Steps to Reproduce

Version Information

Versions:
Python 3.11.9
Django 4.2.15
InvenTree 0.16.9
API 232
Node N/A
Yarn N/A

Commit hash: e0756a7
Commit date: 2024-11-30

Please verify if you can reproduce this bug on the demo site.

  • I can reproduce this bug on the demo site.

Relevant log output

inventree$ sudo docker compose run --rm inventree-server ls data/backup
[+] Creating 2/0
 ✔ Container inventree-cache  Running                                                                                                                                                               0.0s 
 ✔ Container inventree-db     Running                                                                                                                                                               0.0s 
Loading config file : /home/inventree/data/config.yaml
24bd37a68652-2024-12-01-091519.tar.gz               default-24bd37a68652-2024-12-01-091516.psql.bin.gz

-----------------------------------------------------------------------------------------------------------------------------------------

inventree$ sudo docker compose run --rm inventree-server invoke restore
[+] Creating 2/0
 ✔ Container inventree-cache  Running                                                                                                                                                               0.0s 
 ✔ Container inventree-db     Running                                                                                                                                                               0.0s 
Loading config file : /home/inventree/data/config.yaml
Restoring InvenTree database
Python version 3.11.9 - /usr/local/bin/python3
/root/.local/lib/python3.11/site-packages/allauth/exceptions.py:9: UserWarning: allauth.exceptions is deprecated, use allauth.core.exceptions
  warnings.warn("allauth.exceptions is deprecated, use allauth.core.exceptions")
Failed to set maintenance mode state
Failed to reset pricing flags - database not ready
Traceback (most recent call last):
  File "/root/.local/lib/python3.11/site-packages/django/db/backends/utils.py", line 89, in _execute
    return self.cursor.execute(sql, params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/root/.local/lib/python3.11/site-packages/psycopg/cursor.py", line 732, in execute
    raise ex.with_traceback(None)
psycopg.errors.UndefinedTable: relation "part_partpricing" does not exist
LINE 1: SELECT COUNT(*) AS "__count" FROM "part_partpricing" WHERE "...
                                          ^

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

Traceback (most recent call last):
  File "/home/inventree/src/backend/InvenTree/part/apps.py", line 68, in reset_part_pricing_flags
    if items.count() > 0:
       ^^^^^^^^^^^^^
  File "/root/.local/lib/python3.11/site-packages/django/db/models/query.py", line 608, in count
    return self.query.get_count(using=self.db)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/root/.local/lib/python3.11/site-packages/django/db/models/sql/query.py", line 568, in get_count
    return obj.get_aggregation(using, {"__count": Count("*")})["__count"]
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/root/.local/lib/python3.11/site-packages/django/db/models/sql/query.py", line 554, in get_aggregation
    result = compiler.execute_sql(SINGLE)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/root/.local/lib/python3.11/site-packages/django/db/models/sql/compiler.py", line 1562, in execute_sql
    cursor.execute(sql, params)
  File "/root/.local/lib/python3.11/site-packages/django/db/backends/utils.py", line 67, in execute
    return self._execute_with_wrappers(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/root/.local/lib/python3.11/site-packages/django/db/backends/utils.py", line 80, in _execute_with_wrappers
    return executor(sql, params, many, context)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/root/.local/lib/python3.11/site-packages/django/db/backends/utils.py", line 84, in _execute
    with self.db.wrap_database_errors:
  File "/root/.local/lib/python3.11/site-packages/django/db/utils.py", line 91, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/root/.local/lib/python3.11/site-packages/django/db/backends/utils.py", line 89, in _execute
    return self.cursor.execute(sql, params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/root/.local/lib/python3.11/site-packages/psycopg/cursor.py", line 732, in execute
    raise ex.with_traceback(None)
django.db.utils.ProgrammingError: relation "part_partpricing" does not exist
LINE 1: SELECT COUNT(*) AS "__count" FROM "part_partpricing" WHERE "...
                                          ^
Database not ready for creating default report templates
Failed to set maintenance mode state
Database was not ready for initializing machines
Failed to remove obsolete tasks - database not ready
Traceback (most recent call last):
  File "/root/.local/lib/python3.11/site-packages/django/db/backends/utils.py", line 89, in _execute
    return self.cursor.execute(sql, params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/root/.local/lib/python3.11/site-packages/psycopg/cursor.py", line 732, in execute
    raise ex.with_traceback(None)
psycopg.errors.UndefinedTable: relation "django_q_schedule" does not exist
LINE 1: SELECT 1 AS "a" FROM "django_q_schedule" WHERE "django_q_sch...
                             ^

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

Traceback (most recent call last):
  File "/home/inventree/src/backend/InvenTree/InvenTree/apps.py", line 102, in remove_obsolete_tasks
    if obsolete_tasks.exists():
       ^^^^^^^^^^^^^^^^^^^^^^^
  File "/root/.local/lib/python3.11/site-packages/django/db/models/query.py", line 1241, in exists
    return self.query.has_results(using=self.db)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/root/.local/lib/python3.11/site-packages/django/db/models/sql/query.py", line 598, in has_results
    return compiler.has_results()
           ^^^^^^^^^^^^^^^^^^^^^^
  File "/root/.local/lib/python3.11/site-packages/django/db/models/sql/compiler.py", line 1530, in has_results
    return bool(self.execute_sql(SINGLE))
                ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/root/.local/lib/python3.11/site-packages/django/db/models/sql/compiler.py", line 1562, in execute_sql
    cursor.execute(sql, params)
  File "/root/.local/lib/python3.11/site-packages/django/db/backends/utils.py", line 67, in execute
    return self._execute_with_wrappers(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/root/.local/lib/python3.11/site-packages/django/db/backends/utils.py", line 80, in _execute_with_wrappers
    return executor(sql, params, many, context)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/root/.local/lib/python3.11/site-packages/django/db/backends/utils.py", line 84, in _execute
    with self.db.wrap_database_errors:
  File "/root/.local/lib/python3.11/site-packages/django/db/utils.py", line 91, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/root/.local/lib/python3.11/site-packages/django/db/backends/utils.py", line 89, in _execute
    return self.cursor.execute(sql, params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/root/.local/lib/python3.11/site-packages/psycopg/cursor.py", line 732, in execute
    raise ex.with_traceback(None)
django.db.utils.ProgrammingError: relation "django_q_schedule" does not exist
LINE 1: SELECT 1 AS "a" FROM "django_q_schedule" WHERE "django_q_sch...
                             ^
Traceback (most recent call last):
  File "/root/.local/lib/python3.11/site-packages/django/db/backends/utils.py", line 89, in _execute
    return self.cursor.execute(sql, params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/root/.local/lib/python3.11/site-packages/psycopg/cursor.py", line 732, in execute
    raise ex.with_traceback(None)
psycopg.errors.UndefinedTable: relation "django_q_schedule" does not exist
LINE 1: ...", "django_q_schedule"."intended_date_kwarg" FROM "django_q_...
                                                             ^

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

Traceback (most recent call last):
  File "/home/inventree/src/backend/InvenTree/manage.py", line 24, in <module>
    main()
  File "/home/inventree/src/backend/InvenTree/manage.py", line 20, in main
    execute_from_command_line(sys.argv)
  File "/root/.local/lib/python3.11/site-packages/django/core/management/__init__.py", line 442, in execute_from_command_line
    utility.execute()
  File "/root/.local/lib/python3.11/site-packages/django/core/management/__init__.py", line 416, in execute
    django.setup()
  File "/root/.local/lib/python3.11/site-packages/django/__init__.py", line 24, in setup
    apps.populate(settings.INSTALLED_APPS)
  File "/root/.local/lib/python3.11/site-packages/django/apps/registry.py", line 124, in populate
    app_config.ready()
  File "/home/inventree/src/backend/InvenTree/InvenTree/apps.py", line 61, in ready
    self.start_background_tasks()
  File "/home/inventree/src/backend/InvenTree/InvenTree/apps.py", line 120, in start_background_tasks
    for existing_task in Schedule.objects.all():
  File "/root/.local/lib/python3.11/site-packages/django/db/models/query.py", line 398, in __iter__
    self._fetch_all()
  File "/root/.local/lib/python3.11/site-packages/django/db/models/query.py", line 1881, in _fetch_all
    self._result_cache = list(self._iterable_class(self))
                         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/root/.local/lib/python3.11/site-packages/django/db/models/query.py", line 91, in __iter__
    results = compiler.execute_sql(
              ^^^^^^^^^^^^^^^^^^^^^
  File "/root/.local/lib/python3.11/site-packages/django/db/models/sql/compiler.py", line 1562, in execute_sql
    cursor.execute(sql, params)
  File "/root/.local/lib/python3.11/site-packages/django/db/backends/utils.py", line 67, in execute
    return self._execute_with_wrappers(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/root/.local/lib/python3.11/site-packages/django/db/backends/utils.py", line 80, in _execute_with_wrappers
    return executor(sql, params, many, context)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/root/.local/lib/python3.11/site-packages/django/db/backends/utils.py", line 84, in _execute
    with self.db.wrap_database_errors:
  File "/root/.local/lib/python3.11/site-packages/django/db/utils.py", line 91, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/root/.local/lib/python3.11/site-packages/django/db/backends/utils.py", line 89, in _execute
    return self.cursor.execute(sql, params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/root/.local/lib/python3.11/site-packages/psycopg/cursor.py", line 732, in execute
    raise ex.with_traceback(None)
django.db.utils.ProgrammingError: relation "django_q_schedule" does not exist
LINE 1: ...", "django_q_schedule"."intended_date_kwarg" FROM "django_q_...
@Jaishankar872 Jaishankar872 added bug Identifies a bug which needs to be addressed question This is a question triage:not-checked Item was not checked by the core team labels Dec 1, 2024
@Jaishankar872 Jaishankar872 changed the title Issue in restoring data which backed via invoke restore Issue in restoring the backed data via invoke restore Dec 1, 2024
@matmair matmair added setup Relates to the InvenTree setup / installation process and removed bug Identifies a bug which needs to be addressed triage:not-checked Item was not checked by the core team labels Dec 1, 2024
@matmair
Copy link
Member

matmair commented Dec 1, 2024

You will need a few more things:

  • the full .env
  • depending on your setup the secret key file

For a restore you will need to initialise the database before attempting the restore.

@SchrodingersGat
Copy link
Member

For a restore you will need to initialise the database before attempting the restore.

To be specific here, you need to run docker compose --rm inventree-server invoke update

The errors here are because the required database tables have not been initialized yet. Ref: https://docs.inventree.org/en/stable/start/migrate/#initialize-new-database

@Jaishankar872
Copy link
Author

Still issue is there with different error. Server is up But unable to login with old username and password

Different error while restoring the data(Refer below the Error logs).


Update the Restore process

  1. Create the basic Required file as per the document
  2. Replace the .env file with backup (Except the INVENTREE_SITE_URL and INVENTREE_EXT_VOLUME)
  3. Copying backup data from $BACKUP_SOURCE_PATH to the backup folder & Secret key file
  4. Prepare the restore database and media file via invoke(Giving error)
sudo docker compose run --rm inventree-server invoke update --skip-backup
sudo docker compose run --rm inventree-server invoke restore
  1. Starting all Docker containers via compose.
sudo docker compose up --remove-orphans -d

Error log

inventree$ sudo docker compose run --rm inventree-server ls data/backup
[+] Creating 2/0
 ✔ Container inventree-db     Running                                                   0.0s 
 ✔ Container inventree-cache  Running                                                   0.0s 
Loading config file : /home/inventree/data/config.yaml
24bd37a68652-2024-12-01-091519.tar.gz
default-24bd37a68652-2024-12-01-091516.psql.bin.gz

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
inventree$ sudo docker compose run --rm inventree-server invoke restore
[+] Creating 2/0
 ✔ Container inventree-cache  Running                                                                                                                     0.0s 
 ✔ Container inventree-db     Running                                                                                                                     0.0s 
Loading config file : /home/inventree/data/config.yaml
Restoring InvenTree database
Python version 3.11.9 - /usr/local/bin/python3
/root/.local/lib/python3.11/site-packages/allauth/exceptions.py:9: UserWarning: allauth.exceptions is deprecated, use allauth.core.exceptions
  warnings.warn("allauth.exceptions is deprecated, use allauth.core.exceptions")
Finding latest backup
Restoring backup for database 'default' and server 'None'
Restoring: default-24bd37a68652-2024-12-01-091516.psql.bin.gz
Restore tempfile created: 554.6 KiB
 pg_restore --dbname=postgresql://<REMOVED the DB USERNAME & PASSWORD>@inventree-db:5432/inventree --single-transaction --clean 
Traceback (most recent call last):
  File "/home/inventree/src/backend/InvenTree/manage.py", line 24, in <module>
    main()
  File "/home/inventree/src/backend/InvenTree/manage.py", line 20, in main
    execute_from_command_line(sys.argv)
  File "/root/.local/lib/python3.11/site-packages/django/core/management/__init__.py", line 442, in execute_from_command_line
    utility.execute()
  File "/root/.local/lib/python3.11/site-packages/django/core/management/__init__.py", line 436, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/root/.local/lib/python3.11/site-packages/django/core/management/base.py", line 412, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/root/.local/lib/python3.11/site-packages/django/core/management/base.py", line 458, in execute
    output = self.handle(*args, **options)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/root/.local/lib/python3.11/site-packages/dbbackup/management/commands/dbrestore.py", line 71, in handle
    self._restore_backup()
  File "/root/.local/lib/python3.11/site-packages/dbbackup/management/commands/dbrestore.py", line 120, in _restore_backup
    self.connector.restore_dump(input_file)
  File "/root/.local/lib/python3.11/site-packages/dbbackup/db/base.py", line 105, in restore_dump
    return self._restore_dump(dump)
           ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/root/.local/lib/python3.11/site-packages/dbbackup/db/postgresql.py", line 124, in _restore_dump
    stdout, stderr = self.run_command(cmd, stdin=dump, env=self.restore_env)
                     ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/root/.local/lib/python3.11/site-packages/dbbackup/db/base.py", line 171, in run_command
    raise exceptions.CommandConnectorError(
dbbackup.db.exceptions.CommandConnectorError: Error running:  pg_restore --dbname=postgresql://<REMOVED the DB USERNAME & PASSWORD>@inventree-db:5432/inventree --single-transaction --clean 
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 4419; 2606 19120 FK CONSTRAINT socialaccount_socialapp_sites socialaccount_social_socialapp_id_97fb6e7d_fk_socialacc bevlux
pg_restore: error: could not execute query: ERROR:  relation "public.socialaccount_socialapp_sites" does not exist
Command was: ALTER TABLE ONLY public.socialaccount_socialapp_sites DROP CONSTRAINT socialaccount_social_socialapp_id_97fb6e7d_fk_socialacc;

Running Database server log output

PostgreSQL Database directory appears to contain a database; Skipping initialization

2024-12-02 02:55:50.565 UTC [1] LOG:  starting PostgreSQL 13.18 (Debian 13.18-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit

2024-12-02 02:55:50.566 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432

2024-12-02 02:55:50.566 UTC [1] LOG:  listening on IPv6 address "::", port 5432

2024-12-02 02:55:50.567 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"

2024-12-02 02:55:50.569 UTC [27] LOG:  database system was shut down at 2024-12-02 02:52:57 UTC

2024-12-02 02:55:50.573 UTC [1] LOG:  database system is ready to accept connections

2024-12-02 03:00:11.626 UTC [254] ERROR:  relation "public.socialaccount_socialapp_sites" does not exist

2024-12-02 03:00:11.626 UTC [254] STATEMENT:  ALTER TABLE ONLY public.socialaccount_socialapp_sites DROP CONSTRAINT socialaccount_social_socialapp_id_97fb6e7d_fk_socialacc;

@SchrodingersGat
Copy link
Member

Are you migrating data between two setups with the exact same version and configuration?

The error message here is likely because multi site support is not enabled on your new setup - but it was (at some stage) on your old setup.

So, there are tables which exist in your old database which are not available in your new setup..

@Jaishankar872
Copy link
Author

Jaishankar872 commented Dec 2, 2024

Yes, Migrated between the setup. Both running the inventree version.

Still issue is there with different error while database restore. Server is up But unable to login with old username and password

Different error while restoring the data(Refer below the Error logs).


List of changes made here:

  1. Removed INVENTREE_SITE_URL in .env as per the previous system setup.
  2. Rest of the config remains the same.

Brief about existing working system

This configuration done 1.5 years before, application is running on portainer via stacks.
I forget the setup mostly on starting time, but saved many important in this application.

List of modified done in 'yaml' file.

  1. Modified the replaced the .env with stack.env.
  2. Replaced the INVENTREE_EXT_VOLUME in .yml file with actual file location.
  3. Here used the nginx as proxy server in place of Caddy.

My understanding

ERROR:  relation "public.socialaccount_socialapp_sites" does not exist

The error relation "public.socialaccount_socialapp_sites" does not exist confirms that the database restore is attempting to reference a table that does not exist in the current database

In document check here socialaccount. In existing system I haven't create any social account. So I tried the fix, but it is not working.


Log output

From invoke restore command

/inventree$ sudo docker compose run --rm inventree-server invoke restore
[+] Creating 1/0
 ✔ Container inventree-db  Running                                                                                                                                                                  0.0s 
Loading config file : /home/inventree/data/config.yaml
Restoring InvenTree database
Python version 3.11.9 - /usr/local/bin/python3
/root/.local/lib/python3.11/site-packages/allauth/exceptions.py:9: UserWarning: allauth.exceptions is deprecated, use allauth.core.exceptions
  warnings.warn("allauth.exceptions is deprecated, use allauth.core.exceptions")
Finding latest backup
Restoring backup for database 'default' and server 'None'
Restoring: default-24bd37a68652-2024-12-01-091516.psql.bin.gz
Restore tempfile created: 554.6 KiB
 pg_restore --dbname=postgresql://<REMOVED the DB USERNAME & PASSWORD>/inventree --single-transaction --clean 
Traceback (most recent call last):
  File "/home/inventree/src/backend/InvenTree/manage.py", line 24, in <module>
    main()
  File "/home/inventree/src/backend/InvenTree/manage.py", line 20, in main
    execute_from_command_line(sys.argv)
  File "/root/.local/lib/python3.11/site-packages/django/core/management/__init__.py", line 442, in execute_from_command_line
    utility.execute()
  File "/root/.local/lib/python3.11/site-packages/django/core/management/__init__.py", line 436, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/root/.local/lib/python3.11/site-packages/django/core/management/base.py", line 412, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/root/.local/lib/python3.11/site-packages/django/core/management/base.py", line 458, in execute
    output = self.handle(*args, **options)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/root/.local/lib/python3.11/site-packages/dbbackup/management/commands/dbrestore.py", line 71, in handle
    self._restore_backup()
  File "/root/.local/lib/python3.11/site-packages/dbbackup/management/commands/dbrestore.py", line 120, in _restore_backup
    self.connector.restore_dump(input_file)
  File "/root/.local/lib/python3.11/site-packages/dbbackup/db/base.py", line 105, in restore_dump
    return self._restore_dump(dump)
           ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/root/.local/lib/python3.11/site-packages/dbbackup/db/postgresql.py", line 124, in _restore_dump
    stdout, stderr = self.run_command(cmd, stdin=dump, env=self.restore_env)
                     ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/root/.local/lib/python3.11/site-packages/dbbackup/db/base.py", line 171, in run_command
    raise exceptions.CommandConnectorError(
dbbackup.db.exceptions.CommandConnectorError: Error running:  pg_restore --dbname=postgresql://<REMOVED the DB USERNAME & PASSWORD>/inventree --single-transaction --clean 
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 4419; 2606 19120 FK CONSTRAINT socialaccount_socialapp_sites socialaccount_social_socialapp_id_97fb6e7d_fk_socialacc bevlux
pg_restore: error: could not execute query: ERROR:  relation "public.socialaccount_socialapp_sites" does not exist
Command was: ALTER TABLE ONLY public.socialaccount_socialapp_sites DROP CONSTRAINT socialaccount_social_socialapp_id_97fb6e7d_fk_socialacc;

Running Database server log output

PostgreSQL Database directory appears to contain a database; Skipping initialization
2024-12-02 14:45:04.672 UTC [1] LOG:  starting PostgreSQL 13.18 (Debian 13.18-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
2024-12-02 14:45:04.673 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2024-12-02 14:45:04.673 UTC [1] LOG:  listening on IPv6 address "::", port 5432
2024-12-02 14:45:04.678 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2024-12-02 14:45:04.682 UTC [26] LOG:  database system was shut down at 2024-12-02 14:44:49 UTC
2024-12-02 14:45:04.688 UTC [1] LOG:  database system is ready to accept connections
2024-12-02 14:47:01.921 UTC [140] ERROR:  relation "public.socialaccount_socialapp_sites" does not exist
2024-12-02 14:47:01.921 UTC [140] STATEMENT:  ALTER TABLE ONLY public.socialaccount_socialapp_sites DROP CONSTRAINT socialaccount_social_socialapp_id_97fb6e7d_fk_socialacc;
	

@SchrodingersGat
Copy link
Member

So, what you probably need to do is:

  1. Crete a copy of your original database
  2. Manually drop the "public.socialaccount_socialapp_sites table
  3. Restore from the database which no longer has that table

@Jaishankar872
Copy link
Author

Jaishankar872 commented Dec 3, 2024

@SchrodingersGat

Actually, I don't know much about database table creation.

Two options coming in my mind

  1. Since the current application is running, On the front end (GUI) If I remove a social account, then create a backup and use it for restore purposes. How to remove a social account frontend (GUI)? (or) invoke clean-settings (or) anyother option?

Image

  1. Can I use the migration database (invoke import-records ) option? Initially I tried it but it is giving a different error so dropped that Idea.

Please let me know If the manual table creation is the only option to do?


Other question, How to ensure that current running inventree application on Portainer's Stacks doesn't any error in database side backend? As of system working while accessing from frontend GUI.


@Jaishankar872
Copy link
Author

Please provide your response here.

All My important data is stored here.

CC @SchrodingersGat @matmair

@SchrodingersGat
Copy link
Member

Please let me know If the manual table creation is the only option to do?

I am not suggesting creating any new tables. Rather, in the old data, you need to remove the offending table - as it is not supported in the new database schema

@Jaishankar872
Copy link
Author

Yes, I understand your point.

I haven't worked in the database. Still I started exploring.

As per online search and chatgpt, I tried the take dump from the backup database file and removed the problem line then tried restoring data. But that is giving many errors 😕.

This is the reason I am asking for a possible alternate approach.

Your help will be very much valuable here!!!

CC: @SchrodingersGat

@SchrodingersGat
Copy link
Member

Unless you can get the database schema to match between both systems, the the invoke restore operation will continue to fail.

Can I use the migration database (invoke import-records ) option? Initially I tried it but it is giving a different error so dropped that Idea.

Yes, this would be worth a try. The data import process is more forgiving around differences in database tables. I have successfully used this to migrate between different database backends.

What error message are you getting when you try this approach?

@Jaishankar872
Copy link
Author

While import first I got below error
1.

django.db.utils.IntegrityError: Problem installing fixture '/home/inventree/data/2b_data.json.data.json': Could not load common.InvenTreeSetting(pk=1): duplicate key value violates unique constraint "common_inventreesetting_key_key"
DETAIL:  Key (key)=(INVENTREE_DEFAULT_CURRENCY) already exists.

After see this error tried adding the below line in config as per the previous system( I configured default currency as INR).

# Base currency code (or use env var INVENTREE_BASE_CURRENCY)
base_currency: INR

# List of currencies supported by default. Add other currencies here to allow use in InvenTree
currencies:
  - AUD
  - CAD
  - CNY
  - EUR
  - GBP
  - JPY
  - NZD
  - INR
  - USD

and then run invoke update --skip-backup and invoke import-records, Still I getting the same error. and

I tried copy whole config.yaml as previous system Now I getting the different error*

django.db.utils.ProgrammingError: Problem installing fixture '/home/inventree/data/2b_data.json.auth.json': Could not load auth.Group(pk=1): relation "auth_group" does not exist
LINE 1: UPDATE "auth_group" SET "name" = 'Jai' WHERE "auth_group"."i...

I have attached both the logs

Not Sure what is mistake I am doing here.

Complete Error logs for two case

  1. Without copy the config.yaml and manually adding the default currency details
inventree$ sudo docker compose run --rm inventree-server invoke import-records -f data/2b_data.json
Importing database records from '/home/inventree/data/2b_data.json'
Importing user auth data...
Python version 3.11.9 - /usr/local/bin/python3
/root/.local/lib/python3.11/site-packages/allauth/exceptions.py:9: UserWarning: allauth.exceptions is deprecated, use allauth.core.exceptions
  warnings.warn("allauth.exceptions is deprecated, use allauth.core.exceptions")
Installed 4 object(s) from 1 fixture(s)
Importing database records...
Python version 3.11.9 - /usr/local/bin/python3
/root/.local/lib/python3.11/site-packages/allauth/exceptions.py:9: UserWarning: allauth.exceptions is deprecated, use allauth.core.exceptions
  warnings.warn("allauth.exceptions is deprecated, use allauth.core.exceptions")
Traceback (most recent call last):
  File "/root/.local/lib/python3.11/site-packages/django/db/backends/utils.py", line 89, in _execute
    return self.cursor.execute(sql, params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/root/.local/lib/python3.11/site-packages/psycopg/cursor.py", line 732, in execute
    raise ex.with_traceback(None)
psycopg.errors.UniqueViolation: duplicate key value violates unique constraint "common_inventreesetting_key_key"
DETAIL:  Key (key)=(INVENTREE_DEFAULT_CURRENCY) already exists.

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

Traceback (most recent call last):
  File "/home/inventree/src/backend/InvenTree/manage.py", line 24, in <module>
    main()
  File "/home/inventree/src/backend/InvenTree/manage.py", line 20, in main
    execute_from_command_line(sys.argv)
  File "/root/.local/lib/python3.11/site-packages/django/core/management/__init__.py", line 442, in execute_from_command_line
    utility.execute()
  File "/root/.local/lib/python3.11/site-packages/django/core/management/__init__.py", line 436, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/root/.local/lib/python3.11/site-packages/django/core/management/base.py", line 412, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/root/.local/lib/python3.11/site-packages/django/core/management/base.py", line 458, in execute
    output = self.handle(*args, **options)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/root/.local/lib/python3.11/site-packages/django/core/management/commands/loaddata.py", line 102, in handle
    self.loaddata(fixture_labels)
  File "/root/.local/lib/python3.11/site-packages/django/core/management/commands/loaddata.py", line 163, in loaddata
    self.load_label(fixture_label)
  File "/root/.local/lib/python3.11/site-packages/django/core/management/commands/loaddata.py", line 253, in load_label
    if self.save_obj(obj):
       ^^^^^^^^^^^^^^^^^^
  File "/root/.local/lib/python3.11/site-packages/django/core/management/commands/loaddata.py", line 209, in save_obj
    obj.save(using=self.using)
  File "/root/.local/lib/python3.11/site-packages/django/core/serializers/base.py", line 288, in save
    models.Model.save_base(self.object, using=using, raw=True, **kwargs)
  File "/root/.local/lib/python3.11/site-packages/django/db/models/base.py", line 877, in save_base
    updated = self._save_table(
              ^^^^^^^^^^^^^^^^^
  File "/root/.local/lib/python3.11/site-packages/django/db/models/base.py", line 990, in _save_table
    updated = self._do_update(
              ^^^^^^^^^^^^^^^^
  File "/root/.local/lib/python3.11/site-packages/django/db/models/base.py", line 1054, in _do_update
    return filtered._update(values) > 0
           ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/root/.local/lib/python3.11/site-packages/django/db/models/query.py", line 1231, in _update
    return query.get_compiler(self.db).execute_sql(CURSOR)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/root/.local/lib/python3.11/site-packages/django/db/models/sql/compiler.py", line 1984, in execute_sql
    cursor = super().execute_sql(result_type)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/root/.local/lib/python3.11/site-packages/django/db/models/sql/compiler.py", line 1562, in execute_sql
    cursor.execute(sql, params)
  File "/root/.local/lib/python3.11/site-packages/django/db/backends/utils.py", line 67, in execute
    return self._execute_with_wrappers(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/root/.local/lib/python3.11/site-packages/django/db/backends/utils.py", line 80, in _execute_with_wrappers
    return executor(sql, params, many, context)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/root/.local/lib/python3.11/site-packages/django/db/backends/utils.py", line 84, in _execute
    with self.db.wrap_database_errors:
  File "/root/.local/lib/python3.11/site-packages/django/db/utils.py", line 91, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/root/.local/lib/python3.11/site-packages/django/db/backends/utils.py", line 89, in _execute
    return self.cursor.execute(sql, params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/root/.local/lib/python3.11/site-packages/psycopg/cursor.py", line 732, in execute
    raise ex.with_traceback(None)
django.db.utils.IntegrityError: Problem installing fixture '/home/inventree/data/2b_data.json.data.json': Could not load common.InvenTreeSetting(pk=1): duplicate key value violates unique constraint "common_inventreesetting_key_key"
DETAIL:  Key (key)=(INVENTREE_DEFAULT_CURRENCY) already exists.
  1. Copied the config.yaml from whole file
/inventree$ sudo docker compose run --rm inventree-server invoke import-records -f data/2b_data.json
[+] Creating 1/0
 ✔ Container inventree-db  Running                                                                                                                                                                  0.0s 
Loading config file : /home/inventree/data/config.yaml
Importing database records from '/home/inventree/data/2b_data.json'
Importing user auth data...
Python version 3.11.9 - /usr/local/bin/python3
/root/.local/lib/python3.11/site-packages/allauth/exceptions.py:9: UserWarning: allauth.exceptions is deprecated, use allauth.core.exceptions
  warnings.warn("allauth.exceptions is deprecated, use allauth.core.exceptions")
Traceback (most recent call last):
  File "/root/.local/lib/python3.11/site-packages/django/db/backends/utils.py", line 89, in _execute
    return self.cursor.execute(sql, params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/root/.local/lib/python3.11/site-packages/psycopg/cursor.py", line 732, in execute
    raise ex.with_traceback(None)
psycopg.errors.UndefinedTable: relation "auth_group" does not exist
LINE 1: UPDATE "auth_group" SET "name" = 'Jai' WHERE "auth_group"."i...
               ^

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

Traceback (most recent call last):
  File "/home/inventree/src/backend/InvenTree/manage.py", line 24, in <module>
    main()
  File "/home/inventree/src/backend/InvenTree/manage.py", line 20, in main
    execute_from_command_line(sys.argv)
  File "/root/.local/lib/python3.11/site-packages/django/core/management/__init__.py", line 442, in execute_from_command_line
    utility.execute()
  File "/root/.local/lib/python3.11/site-packages/django/core/management/__init__.py", line 436, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/root/.local/lib/python3.11/site-packages/django/core/management/base.py", line 412, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/root/.local/lib/python3.11/site-packages/django/core/management/base.py", line 458, in execute
    output = self.handle(*args, **options)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/root/.local/lib/python3.11/site-packages/django/core/management/commands/loaddata.py", line 102, in handle
    self.loaddata(fixture_labels)
  File "/root/.local/lib/python3.11/site-packages/django/core/management/commands/loaddata.py", line 163, in loaddata
    self.load_label(fixture_label)
  File "/root/.local/lib/python3.11/site-packages/django/core/management/commands/loaddata.py", line 253, in load_label
    if self.save_obj(obj):
       ^^^^^^^^^^^^^^^^^^
  File "/root/.local/lib/python3.11/site-packages/django/core/management/commands/loaddata.py", line 209, in save_obj
    obj.save(using=self.using)
  File "/root/.local/lib/python3.11/site-packages/django/core/serializers/base.py", line 288, in save
    models.Model.save_base(self.object, using=using, raw=True, **kwargs)
  File "/root/.local/lib/python3.11/site-packages/django/db/models/base.py", line 877, in save_base
    updated = self._save_table(
              ^^^^^^^^^^^^^^^^^
  File "/root/.local/lib/python3.11/site-packages/django/db/models/base.py", line 990, in _save_table
    updated = self._do_update(
              ^^^^^^^^^^^^^^^^
  File "/root/.local/lib/python3.11/site-packages/django/db/models/base.py", line 1054, in _do_update
    return filtered._update(values) > 0
           ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/root/.local/lib/python3.11/site-packages/django/db/models/query.py", line 1231, in _update
    return query.get_compiler(self.db).execute_sql(CURSOR)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/root/.local/lib/python3.11/site-packages/django/db/models/sql/compiler.py", line 1984, in execute_sql
    cursor = super().execute_sql(result_type)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/root/.local/lib/python3.11/site-packages/django/db/models/sql/compiler.py", line 1562, in execute_sql
    cursor.execute(sql, params)
  File "/root/.local/lib/python3.11/site-packages/django/db/backends/utils.py", line 67, in execute
    return self._execute_with_wrappers(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/root/.local/lib/python3.11/site-packages/django/db/backends/utils.py", line 80, in _execute_with_wrappers
    return executor(sql, params, many, context)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/root/.local/lib/python3.11/site-packages/django/db/backends/utils.py", line 84, in _execute
    with self.db.wrap_database_errors:
  File "/root/.local/lib/python3.11/site-packages/django/db/utils.py", line 91, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/root/.local/lib/python3.11/site-packages/django/db/backends/utils.py", line 89, in _execute
    return self.cursor.execute(sql, params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/root/.local/lib/python3.11/site-packages/psycopg/cursor.py", line 732, in execute
    raise ex.with_traceback(None)
django.db.utils.ProgrammingError: Problem installing fixture '/home/inventree/data/2b_data.json.auth.json': Could not load auth.Group(pk=1): relation "auth_group" does not exist
LINE 1: UPDATE "auth_group" SET "name" = 'Jai' WHERE "auth_group"."i...

@Jaishankar872
Copy link
Author

Please let me know if I have to provide any other details

@SchrodingersGat

@Jaishankar872
Copy link
Author

Hi @SchrodingersGat

All the issues are resolved, by adding -c to clear old data before import.

import-records -c -f data/2b_data.json

Thanks for your support !!!!

@SchrodingersGat
Copy link
Member

@Jaishankar872 happy to hear that you got it worked out

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
question This is a question setup Relates to the InvenTree setup / installation process
Projects
None yet
Development

No branches or pull requests

3 participants