How to upgrade DB version

[optimistic5]

Hi.
I quite old user of firefly (by the way how to find first transaction?).
I have quite old DB.
Should I update PG version, how? Just latest version or some other way?
Should I migrate to MySQL? If yes - what the best way?
Please suggest.
Thank you.

--- 
networks:
  default:
    external:
      name: nginx-proxy
      
services:

  firefly_iii_app: 
    image: fireflyiii/core:version-6.2.9 
    restart: unless-stopped
    depends_on:
      - firefly_iii_db
    expose: 
      - 8080
    env_file: .env
    volumes: 
      - ./firefly_iii_export:/var/www/firefly-iii/storage/export
      - ./firefly_iii_upload:/var/www/firefly-iii/storage/upload
        
  firefly_iii_db: 
    image: "postgres:10"
    restart: unless-stopped
    expose:
      - 5432
    environment:
      - POSTGRES_PASSWORD=xx
      - POSTGRES_DB=xx
      - POSTGRES_USER=xx
    volumes: 
      - ./firefly_iii_db:/var/lib/postgresql/data
  
  firefly_iii_cron:
    image: alpine
    command: sh -c "echo \"0 3 * * * wget https://xxx/cron/run/6ec85141f454a2204e3d6cfe9e392658\" | crontab - && crond -f -L /dev/stdout"
version: "3.2"

[JC5]

Hey, thanks for starting this discussion.

As a rule, it's not really necessary to upgrade the database. However, there are several guides that can help you.

For example: https://thomasbandt.com/postgres-docker-major-version-upgrade

Cheers,
James

[optimistic5]

Here the full log of upgrading from postgres:10 to postgres:17

docker exec firefly-iii-firefly_iii_db-1 pg_dump -U firefly -d homestead > firefly_backup_10.sql
tail firefly_backup_10.sql
# Verify it has "PostgreSQL database dump complete"
docker compose down
vim docker-compose.yml
  change image to postgres:12
  change volume to - ./firefly_iii_db_11:/var/lib/postgresql/data
docker compose up -d firefly_iii_db
docker exec -i firefly-iii-firefly_iii_db-1 psql -U firefly -d homestead < firefly_backup_10.sql
docker compose up -d && docker compose logs -f

docker exec firefly-iii-firefly_iii_db-1 pg_dump -U firefly -d homestead > firefly_backup_11.sql
tail firefly_backup_11.sql
# Verify it has "PostgreSQL database dump complete"
docker compose down
vim docker-compose.yml
  change image to postgres:12
  change volume to - ./firefly_iii_db_12:/var/lib/postgresql/data
docker compose up -d firefly_iii_db
docker exec -i firefly-iii-firefly_iii_db-1 psql -U firefly -d homestead < firefly_backup_11.sql
docker compose up -d && docker compose logs -f

docker exec firefly-iii-firefly_iii_db-1 pg_dump -U firefly -d homestead > firefly_backup_12.sql
tail firefly_backup_12.sql
# Verify it has "PostgreSQL database dump complete"
docker compose down
vim docker-compose.yml
  change image to postgres:13
  change volume to - ./firefly_iii_db_13:/var/lib/postgresql/data
docker compose up -d firefly_iii_db
docker exec -i firefly-iii-firefly_iii_db-1 psql -U firefly -d homestead < firefly_backup_12.sql
docker compose up -d && docker compose logs -f

docker exec firefly-iii-firefly_iii_db-1 pg_dump -U firefly -d homestead > firefly_backup_13.sql
tail firefly_backup_13.sql
# Verify it has "PostgreSQL database dump complete"
docker compose down
vim docker-compose.yml
  change image to postgres:14
  change volume to - ./firefly_iii_db_14:/var/lib/postgresql/data
docker compose up -d firefly_iii_db
docker exec -i firefly-iii-firefly_iii_db-1 psql -U firefly -d homestead < firefly_backup_13.sql
docker compose up -d && docker compose logs -f

docker exec firefly-iii-firefly_iii_db-1 pg_dump -U firefly -d homestead > firefly_backup_14.sql
tail firefly_backup_14.sql
# Verify it has "PostgreSQL database dump complete"
docker compose down
vim docker-compose.yml
  change image to postgres:15
  change volume to - ./firefly_iii_db_15:/var/lib/postgresql/data
docker compose up -d firefly_iii_db
docker exec -i firefly-iii-firefly_iii_db-1 psql -U firefly -d homestead < firefly_backup_14.sql
docker compose up -d && docker compose logs -f

docker exec firefly-iii-firefly_iii_db-1 pg_dump -U firefly -d homestead > firefly_backup_15.sql
tail firefly_backup_15.sql
# Verify it has "PostgreSQL database dump complete"
docker compose down
vim docker-compose.yml
  change image to postgres:16
  change volume to - ./firefly_iii_db_16:/var/lib/postgresql/data
docker compose up -d firefly_iii_db
docker exec -i firefly-iii-firefly_iii_db-1 psql -U firefly -d homestead < firefly_backup_15.sql
docker compose up -d && docker compose logs -f

docker exec firefly-iii-firefly_iii_db-1 pg_dump -U firefly -d homestead > firefly_backup_16.sql
tail firefly_backup_16.sql
# Verify it has "PostgreSQL database dump complete"
docker compose down
vim docker-compose.yml
  change image to postgres:17
  change volume to - ./firefly_iii_db_17:/var/lib/postgresql/data
docker compose up -d firefly_iii_db
docker exec -i firefly-iii-firefly_iii_db-1 psql -U firefly -d homestead < firefly_backup_16.sql
docker compose up -d && docker compose logs -f

@JC5 all was smooth, my bow to you!