PostgreSQL Upgrade (15 to 18.3)

This guide provides a step-by-step tutorial for upgrading your SparkyFitness PostgreSQL database from version 15-alpine to 18.3-alpine.

IMPORTANT

Warning: PostgreSQL does NOT automatically upgrade its data. Changing the image version alone will cause the database to fail to start. Follow these steps carefully.

Prerequisites

• SparkyFitness installed via Docker Compose.
• Basic knowledge of Docker commands.
• Things can go wrong and you don't blame me 😃
• You have a backup of your data before starting the upgrade.
• You have a backup of your .env file before starting the upgrade.
• You have a backup of your docker-compose.yml file before starting the upgrade.

Default Paths

This guide assumes you are using the default SparkyFitness directory structure. If you have custom paths, replace ./postgresql with your actual path.

Assumptions

• Base path: Your SparkyFitness installation directory (where docker-compose.yml is located).
• Default Database Path: ./postgresql (relative to the compose file).
• Database Username: sparky (Verify this in your .env file. This is your SPARKY_FITNESS_DB_USER).
• **Database Name: sparkyfitness_db (this is your SPARKY_FITNESS_DB_NAME. default is sparkyfitness_db)

---

Part 1: Backup and Shutdown

1. Perform a Full Data Backup

Run this on your host terminal to export all your data into a single SQL file. Replace [OLD_CONTAINER_NAME] with your actual container name (e.g., sparkyfitness-db or postgres15-alpine): run "docker ps " to find the old container name. in the below command sparky is your SPARKY_FITNESS_DB_USER

docker exec -i [OLD_CONTAINER_NAME] pg_dumpall -U sparky > ./full_backup.sql

2. Stop All Containers

Stop the services to prevent any new data from being written during the upgrade.

docker compose down

3. Backup and Move Old Volume (Host Side)

We'll rename the old volume as a safety measure and create a fresh one for the new version.

cd your_sparkyfitness_directory
# Move old v15 data to a backup folder
sudo mv ./postgresql ./postgresql_v15
# Create a fresh folder for the new version
sudo mkdir -p ./postgresql
# Set the correct permissions for the Postgres process (UID 70)
sudo chown -R 1000:1000 ./*

---

Part 2: Configuration Update

4. Update your docker-compose.yml

Modify the sparkyfitness-db service to use the new image and the corrected mount point structure required for version 18.3. We are updating the container name to sparkyfitness-db and image to postgres:18.3

version: "3.9"
services:
  sparkyfitness-db:
    image: postgres:18.3-alpine
    container_name: sparkyfitness-db

Update the volumes section of the sparkyfitness-db service to:

      - ${DB_PATH:-./postgresql}:/var/lib/postgresql

4. Update your docker-compose.yml

Update postgres, server, frontend and garmin(if using garmin) section in your compose file to include 1000 for PUID and GUID PUID: 1000 GUID: 1000

Part 3: Restore and Restart

5. Bring Up All Containers

Start the sparkyfitness db container

docker compose up -d sparkyfitness-db

IMPORTANT

Wait 30 seconds for all services to fully initialize before proceeding. If you restore before the app finishes initializing, the app will overwrite your restored data.

6. Restore the Data

Once the db container is running and initialized, run the restore.

cat ./full_backup.sql | docker exec -i sparkyfitness-db psql -U sparky

if a restore fails try this

cat ./full_backup.sql | docker exec -i sparkyfitness-db psql -U sparky -d sparkyfitness_db

7. Restart All Containers

Restart the services so the app picks up the restored data.

docker compose restart

---

Troubleshooting

• Permission Denied: If the container fails to start with a permission error, re-run: sudo chown -R 1000:1000 ./postgresql