PostgreSQL DB schema/extension changes 15 to 17

[jcsp101]

I'm using unraid, and rereading the only supported way for upgrading PostgreSQL described here https://docs.teslamate.org/docs/maintenance/upgrading_postgres , I first made sure that the process I took aligns with the backup procedure, but on the restore I made some errors due to the new changes to the DB needed before restoring.

On the Backup and Restore page, it says to Create backup file
docker compose exec -T database pg_dump -U teslamate teslamate > ./teslamate.bck

I did this using the same pgdump command, specifying my own docker environment port specifics:
pg_dump -U DBUSERNAME -h IPADDRESS -p PORT -fc -f /var/lib/postgresql/data/teslamate.dump DBNAME
resulting in an 82.5MB dump file as expected

I then used the unraid GUI and Adminer to perform the steps of upgrading the postgressql DB version from 15 -> 17, deleting existing previous dbs, and creating an (empty) new one

The usual process to restore is usually something like the following, which i've used for other containers in the past:
pg_restore -U DBUSERNAME -h IPADDRESS -p PORT -d DBNAME /var/lib/postgresql/data/teslamate.dump

However, upon closer analysis on the restore procedure, i now see that additional changes are made to the database via the following before restoring:

# Stop the teslamate container to avoid write conflicts
docker compose stop teslamate

# Drop existing data and reinitialize (Don't forget to replace first teslamate if using different TM_DB_USER)
docker compose exec -T database psql -U teslamate teslamate << .
DROP SCHEMA public CASCADE;
CREATE SCHEMA public;
CREATE EXTENSION cube WITH SCHEMA public;
CREATE EXTENSION earthdistance WITH SCHEMA public;
.

# Restore
docker compose exec -T database psql -U teslamate -d teslamate < teslamate.bck

# Restart the teslamate container
docker compose start teslamate

My questions then:

• is it possible to make these db changes to an existing database to fix this? that has already been restored without these schemas/extensions added?
• if not, will starting over and restoring from a backup from 2w ago lose 2w worth of data, or will it be re-pulled once teslamate is back up?

[brianmay]

You can run the "CREATE EXTENSION" psql instructions at anytime, it won't effect any data already restored. They will error if the extension is already installed. That is fine.

I think it may not even matter if Teslamate is running at the time.

Do not run the DROP SCHEMA command, that will delete all your data. Similarly, do not run the CREATE SCHEMA command, it will just error because the schema already exists.

[jcsp101]

hmm, i see, thanks for the context on what these commands do to the database. sounds like i cannot fully remedy this after the fact then.

I can only assume they are all necessary for one reason or another, so if that's the case, then i likely need to start over and run these commands prior to restoring my database again, right?

[brianmay]

I think you might have misunderstood me. Yes, you can rectify this. Just don't run the command that will delete your database.

[jcsp101]

Ah okay, thanks for the additional clarification, I had assumed all were necessary for Teslamate and grafana to run correctly.

I will run just the create extension commands then.

[jcsp101]

I ran just the 2 CREATE EXTENSION commands just now, but unfortunately this did not resolve my issues with no data showing up in grafana.

[jcsp101]

I also just went ahead to further eliminate variables, and restored my database again from scratch from v15 to v17, this time making sure to even use a .bck file and avoid the -fc argument on backup, yielding a much larger backup file of 657MB, and using all the commands from the instructions, and restoring using pgsql command instead of pg_restore

however, i am still getting no data showing in grafana:

[jcsp101]

i figured out the issue, i ended up having to delete some directories associated with teslamate-grafana and letting them reinitialize, which fixed things.