Sal not reporting correctly in GUI, appears to be a DB issue, but its not

[Fallout476]

Background Docker container hosted on CentOS7 Postgres DB hosted on a different server in the DC Sal vers 4.1.6.2160 Sal-Scripts vers 4+

Describe the bug When restoring our DB back to Sal in preparation of upgrading to 4.1.9, the GUI does not report back the correct information. Devices are missing, reported checkin dates are wrong. all kinds of bad info

We have checked our DB tables and they are both (Prod and Dev) the same.

[grahamgilbert]

There isn't enough information here to do any troubleshooting. How was the export made? What is missing / wrong?

[Fallout476]

the DB was cloned via a postgres dump file, done by our DB admin. the tables on the DB are 1:1

As to what was missing, entire devices that have been online for months, historical info on devices that have been in Sal for over a year, info that is for one device showing up on another device in the GUI.

if you have a troubleshoot commands that can be ran to find the source of the issue, im game to use them

[grahamgilbert]

I literally have no idea what has happened. I've never seen this before and the information you are giving me is not enough to go on. You are being very vague. Screenshots, actual examples of data would be helpful.

[Fallout476]

• in this screenshot you can see that this is running on sal 4.1.9 from docker

• in this screenshot you can see this is on sal 4.1.6 from docker

• both of these docker containers are using the same external DB, that was from this afternoon. as you can see these do not match up. we did verify that the DB tables are 1:1 to each other

• here is a different example using the same devices, this should be the exact same for both devices

• this first screen shot is on sal 4.1.9

• and this one is from 4.1.6

• this appears this way on 4.1.6 or 4.1.9. is doesnt matter the version.

[vphan13]

I'm working with Fallout476 on this issue. I have a few more details to provide: Docker host: CentOS 7.7.1908 Docker version: docker-ce-19.03.12-3.el7.x86_64

The command to start the container is:

/bin/docker run --rm --name=sal --hostname=sal -p 443:443 -p 8000:8000 -v /etc/letsencrypt/live/<fqdn>/sal.conf:/etc/nginx/sites-enabled/sal.conf -v /etc/letsencrypt/live/<fqdn>:/opt -v /opt/docker/salprod/plugins:/home/docker/sal/plugins -e DB_HOST=<db_host> -e DB_PORT=<db_port> -e ADMIN_PASS=<password> -e DB_NAME=prod_sal -e DB_USER=<db_username> -e DB_PASS=<db_password> macadmins/sal:fix_operatingsystem

For our dev environment, we create a copy of the production database on the same database host (rounsley), and start a dev instance with nearly the same command

/bin/docker run --rm --name=saldev0 --hostname=saldev0 -p 443:443 -p 8000:8000 -v /etc/letsencrypt/live/<fqdn>/saldev0.conf:/etc/nginx/sites-enabled/saldev0.conf -v /etc/letsencrypt/live/<fqdn>:/opt -v /opt/docker/saldev0/plugins:/home/docker/sal/plugins -e DB_HOST=<db_host> -e DB_PORT=<db_port> -e ADMIN_PASS=<password> -e DB_NAME=dev_sal -e DB_USER=<db_username> -e DB_PASS=<db_password> macadmins/sal:fix_operatingsystem

The copying of the DB is done using the built in pg_dump and psql commands on the DB host. We've also done a database comparison of the tables/rows and schema which are identical between the production DB and any copies we make. I'm happy to provide more details, but I don't think this is a DB issue. I'm wondering if there is any unique data/configuration between the docker instances that are not stored in the DB. For example, we know the plugins folder is unique to each instance because we created a unique directory mapping to test plugins, are there any other directories that we should be aware of?

Lastly, we are unable to run the container with -e DOCKER_SAL_DEBUG=true. This causes nginx to crash immediately

[root@sal-d sal]# docker logs -f saldev0 /bin/bash: warning: setlocale: LC_ALL: cannot change locale (en_US.UTF-8) Operations to perform: Apply all migrations: admin, auth, catalog, contenttypes, inventory, licenses, profiles, search, server, sessions, sites Running migrations: No migrations to apply. 285 static files copied to '/home/docker/sal/static'. There are already 1 superusers Stopping nginx: nginx. RUNNING IN DEBUG MODE

[grahamgilbert]

If you edit any files, you will need to link them in. Migrating between servers using pgdump hasn't ever been tested. My suggestion is that you use the build in django dumpdata and loaddata (excluding auth table) https://coderwall.com/p/mvsoyg/django-dumpdata-and-loaddata

[vphan13]

We still need to poke around our dev instance, but it is indeed an issue with using dumpdata and loaddata vs pg_dump. I did not see this in the docs, if it is in there, I apologize ahead of time for not seeing it. For others who are interested, the basics are

1. create an empty db for sal
2. On a running (production) sal instance that has all the data you need, run the dumpdata command. We use the plugins path because it is mounted to a directory on our docker host /home/app/sal/manage.py dumpdata > /home/docker/plugins/salprod.json
3. start your dev sal container connected to your empty db
4. restore the json file in step 3 `root@saldev0:/home/app/sal# ./manage.py loaddata /home/docker/sal/plugins/salprod.json Installed 107198 object(s) from 1 fixture(s)

[grahamgilbert]

I don't see this in the Wiki, this would be a great addition.

[vphan13]

Yes I just looked through the wiki as well the "Docker ---> Upgrading Database" section recommends using psql for upgrading the DB server. This is probably what lead to my assumption that pg_dump can be used. There should probably be another section heading for Restoring DB from an existing sal instance

[Fallout476]

this problem has been resolved.

source of the problem: We where following the documentation on making a backup of the DB using pSQL tools, however we should be using the django tools that are already built into the docker image. Once we switched to the django tools, the backup was reading the same info as our production DB.

The steps are listed below for others that might run into this issue.

- create an empty db for sal
- On a running (production) sal instance that has all the data you need, run the dumpdata command. We use the plugins path because it is mounted to a directory on our docker host     /home/app/sal/manage.py dumpdata > home/docker/plugins/salprod.json
- start your dev sal container connected to your empty db
- restore the json file in step 3
- `root@saldev0:/home/app/sal# ./manage.py loaddata /home/docker/sal/plugins/salprod.json
Installed 107198 object(s) from 1 fixture(s)