How can I restore the database contents

I back up the database by below command:

docker exec -it kiwi_web /Kiwi/manage.py dumpdata --all --indent 2 > database.json

then, upgrade running Kiwi TCMS containers and restore the database by executing the following commands:

cd Kiwi/
git pull
docker-compose down
docker pull kiwitcms/kiwi 
docker pull centos/mariadb
docker volume rm kiwi_db_data
docker exec -it kiwi_web /Kiwi/manage.py migrate
docker-compose up -d

until now, it's fine. when I delete data from all tables

docker exec -it kiwi_web /bin/bash -c '/Kiwi/manage.py sqlflush | /Kiwi/manage.py dbshell'

it shows

CommandError: You appear not to have the 'mysql' program installed or on your path.

I don't know how to fix it. If I restore database command directly, it shows

django.db.utils.IntegrityError: Problem installing fixture '-': Could not load contenttypes.ContentType(pk=1): (1062, "Duplicate entry 'admin-logentry' for key 'django_content_type_app_label_model_76bd3d3b_uniq'")

So, what should I do to restore the database contents ?

Solution

@Cuijun - next time please format your question for better readability.

I back up the database by below command:
docker exec -it kiwi_web /Kiwi/manage.py dumpdata --all --indent 2 > database.json 

^^^ this is fine.

then, upgrade running Kiwi TCMS containers and restore the database by executing the following commands:

cd Kiwi/

git pull

docker-compose down

docker pull kiwitcms/kiwi

docker pull centos/mariadb

docker volume rm kiwi_db_data

docker exec -it kiwi_web /Kiwi/manage.py migrate

docker-compose up -d

until now, it's fine.

Nope, this isn't fine. Whenever you upgrade the container you have to apply any new migrations. This is documented both in the docs and also with every release notes:

https://kiwitcms.readthedocs.io/en/latest/installing_docker.html#upgrading

https://kiwitcms.org/blog/kiwi-tcms-team/2020/04/27/kiwi-tcms-83/

Your entire upgrade process is wrong and will lead to error and data loss!!!

You should never delete your data volume, it is separated from the rest of the application precisely for the reason to be able to survive reboots and version upgrades. However there's more to it, see below.

To explain: some changes in Kiwi TCMS require changes to the database structure. Sometimes these are adding or deleting columns, but other times these are renaming existing columns, changing their data type or updating the actual values. Internally this is controlled by what is called migrations - these are files which instruct the DB engine how to apply the necessary changes so the DB schema becomes what the application expects. This is what created the DB tables in the first place. That's why you have this manage.py migrate command in there.

The best thing you can do in the current situation is start the containers with the old version of Kiwi TCMS - that is the same version you had running when you made your backup.

Then upgrade between every single version one by one following the instructions very closely and backing up at every single step of the way.

Because you said you are using the centos/mariadb image that means you are using MariaDB 5.5. Kiwi TCMS 8.0 switches to MariaDB 10.x because the older version can't process some of the available migrations. Read the release notes for more info.

Now because you've been using an old version (and maybe you have removed the old versions of your docker images) you're stuck. The only option left for restoring an intermediate version is to build it from source for every single version that has been released between what you had and now.