# 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
```bash
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.
```bash
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.
```bash
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
```yaml
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:
```yaml
      - ${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
```bash
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.
```bash
cat ./full_backup.sql | docker exec -i sparkyfitness-db psql -U sparky
```
if a restore fails try this
```bash
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.
```bash
docker compose restart
```
---
## Troubleshooting
- **Permission Denied**: If the container fails to start with a permission error, re-run:
  `sudo chown -R 1000:1000 ./postgresql`