Skip to content

Database Configuration

Jump to bottom
homelabforge edited this page Nov 2, 2026 · 8 revisions

Database Configuration

Configure MyGarage to use SQLite (default) or PostgreSQL for data storage. Learn about migration, backup, and performance optimization.

Database Options

MyGarage supports two database backends:

Database Use Case Performance Setup Complexity Multi-User Support
SQLite Single-user, homelab, <1000 records Good ✅ Zero config ⚠️ Limited
PostgreSQL Several users writing at once, or you already run PostgreSQL Excellent ⚠️ Requires setup ✅ Full

SQLite (Default)

Overview

  • File-based database - No separate database server required
  • Zero configuration - Works out of the box
  • Lightweight - Single .db file
  • Best for: Single-user deployments, small garages (<5 vehicles), low traffic

Default Configuration

MyGarage automatically creates SQLite database on first run:

Database Location:

/data/mygarage.db

Docker Volume Mapping:

volumes:
  - ./data:/data  # mygarage.db stored here

SQLite Advantages

✅ Zero setup - No configuration needed ✅ Simple backup - Copy single file ✅ Low resource usage - <50MB RAM ✅ Portable - Move .db file between systems

SQLite Limitations

⚠️ Concurrent writes - Only one write operation at a time (readers not blocked) ⚠️ Network access - Cannot be accessed remotely (file-based) ⚠️ Large datasets - Performance degrades >10,000 service records ⚠️ Multi-user - Limited concurrent user support

When to Use SQLite

✅ Recommended:

  • Single user or family (2-4 users)
  • Small vehicle garage (1-10 vehicles)
  • Low traffic (< 100 requests/day)
  • Homelab/personal use

❌ Not Recommended:

  • 10+ concurrent users
  • Large garage (50+ vehicles)
  • High traffic (thousands of requests/day)

PostgreSQL

Overview

  • Client-server database - Separate database container/server
  • Best for: several people writing at once, large garages, or a PostgreSQL server you already run and back up

PostgreSQL Advantages

✅ Concurrent access - Many writers at once without lock waits ✅ Reliability - ACID compliance, data integrity ✅ Tooling - Your existing pg_dump, replication and monitoring

PostgreSQL Setup

Docker Compose (Recommended)

Full docker-compose.yml with PostgreSQL:

services:
  postgres:
    image: postgres:16-alpine
    container_name: mygarage-postgres
    environment:
      POSTGRES_DB: mygarage
      POSTGRES_USER: mygarage
      POSTGRES_PASSWORD: CHANGEME_SECURE_PASSWORD
    volumes:
      - postgres_data:/var/lib/postgresql/data
    restart: unless-stopped
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U mygarage"]
      interval: 10s
      timeout: 5s
      retries: 5

  mygarage:
    image: ghcr.io/homelabforge/mygarage:latest
    container_name: mygarage
    depends_on:
      postgres:
        condition: service_healthy
    ports:
      - "8686:8686"
    volumes:
      - ./data:/data
    environment:
      - MYGARAGE_DATABASE_URL=postgresql+asyncpg://mygarage:CHANGEME_SECURE_PASSWORD@postgres:5432/mygarage
    restart: unless-stopped

volumes:
  postgres_data:

Start Services:

docker-compose up -d

External PostgreSQL Server

If you have existing PostgreSQL server:

MyGarage Configuration:

services:
  mygarage:
    image: ghcr.io/homelabforge/mygarage:latest
    environment:
      - MYGARAGE_DATABASE_URL=postgresql+asyncpg://username:password@postgres-server:5432/mygarage

Connection String Format:

postgresql+asyncpg://username:password@hostname:port/database

Components:

  • postgresql+asyncpg - Driver (required for async support)
  • username - PostgreSQL user
  • password - User's password
  • hostname - PostgreSQL server address (or container name)
  • port - PostgreSQL port (default: 5432)
  • database - Database name

PostgreSQL Database Creation

If using existing PostgreSQL server, create database first:

CREATE DATABASE mygarage;
CREATE USER mygarage WITH PASSWORD 'secure-password-here';
GRANT ALL PRIVILEGES ON DATABASE mygarage TO mygarage;

Or using psql:

docker exec -it postgres-container psql -U postgres
CREATE DATABASE mygarage;
CREATE USER mygarage WITH ENCRYPTED PASSWORD 'secure-password';
GRANT ALL PRIVILEGES ON DATABASE mygarage TO mygarage;
\q

Server Timezone

From 3.10.0, MyGarage pins every PostgreSQL session to UTC, so the server's own timezone (a TZ on its container, or timezone in postgresql.conf) no longer matters. Times show in your household's timezone in the app.

Before 3.10.0, a server that wasn't on UTC stored the times the database stamps itself, like most created and updated times, in its own zone. On the first start of 3.10.0, migration 134 converts those stored times to UTC once. Seven columns the app also writes itself (such as users' and vehicles' updated times) are left as they are, since old rows there can't be told apart. A server already on UTC has nothing to convert.

  • Back up first. Migrations 134 and 135 are FATAL: if either fails, MyGarage refuses to start. On PostgreSQL, any migration that fails before 134 has run also stops startup.
  • PGTZ or PGOPTIONS in MyGarage's environment: if the session zone came from MyGarage's side, 134 converts nothing, logs a warning naming the zone, and doesn't run again. Times from before the upgrade stay in that zone. To have them converted, restore the backup, take the zone setting out of MyGarage's environment, and start 3.10.0 again.

Migrating from SQLite to PostgreSQL

Why Migrate?

  • Growing number of users (5+)
  • Large dataset (1,000+ service records)
  • Performance issues (slow queries)
  • Need concurrent write access

Migration Steps

MyGarage has no built-in way to move your data from SQLite to PostgreSQL:

  • The Full Data Backup in Settings → Backup & Restore restores only into the same kind of database. On PostgreSQL the restore is done with pg_restore, not from the app.
  • A vehicle's JSON Export and Import (on the vehicle page) carry only some of its records, and no photos, documents or settings.

To start on PostgreSQL, point MYGARAGE_DATABASE_URL at an empty database and restart. MyGarage creates its tables on first startup.

Database Migration Tool

Using pgloader (advanced, not tested with MyGarage):

# Install pgloader
docker run --rm -it dimitri/pgloader:latest pgloader \
  sqlite:///data/mygarage.db \
  postgresql://mygarage:password@postgres:5432/mygarage

Note: May require schema adjustments.

Migrating from PostgreSQL to SQLite

There is no built-in way to move data from PostgreSQL back to SQLite.

Database Backups

SQLite Backup

SQLite runs in WAL mode, so copying mygarage.db while the container is running is a torn copy (it misses committed data in the -wal sidecar). Stop the container first, or use Settings → Backup & Restore (admins only), which snapshots via SQLite's Online Backup API.

Method 1: File Copy (Simple):

# Stop MyGarage first to ensure clean backup
docker-compose stop mygarage

# Copy database file
cp data/mygarage.db data/mygarage-backup-2024-12-02.db

# Restart MyGarage
docker-compose start mygarage

Automated Backup Script:

#!/bin/bash
# backup-mygarage.sh

DATE=$(date +%Y-%m-%d)
BACKUP_DIR="/backups/mygarage"
mkdir -p "$BACKUP_DIR"

# WAL-safe online backup (no container stop needed). The image has no sqlite3
# command, so this uses Python's sqlite3 module.
docker exec mygarage python3 -c "import sqlite3; sqlite3.connect('/data/mygarage.db').backup(sqlite3.connect('/data/backup-$DATE.db'))"
docker cp "mygarage:/data/backup-$DATE.db" "$BACKUP_DIR/mygarage-$DATE.db"
docker exec mygarage rm "/data/backup-$DATE.db"

# Keep last 30 days of backups
find "$BACKUP_DIR" -name "mygarage-*.db" -mtime +30 -delete

Cron Job (daily backups at 2 AM):

0 2 * * * /path/to/backup-mygarage.sh

PostgreSQL Backup

Method 1: pg_dump:

# Backup to SQL file
docker exec mygarage-postgres pg_dump -U mygarage mygarage > mygarage-backup.sql

# Backup to compressed file
docker exec mygarage-postgres pg_dump -U mygarage mygarage | gzip > mygarage-backup.sql.gz

Method 2: pg_dumpall (includes users, roles):

docker exec mygarage-postgres pg_dumpall -U postgres > mygarage-full-backup.sql

Automated PostgreSQL Backup:

#!/bin/bash
# backup-postgres.sh

DATE=$(date +%Y-%m-%d)
BACKUP_DIR="/backups/mygarage"
mkdir -p "$BACKUP_DIR"

# Dump database
docker exec mygarage-postgres pg_dump -U mygarage mygarage | gzip > "$BACKUP_DIR/mygarage-$DATE.sql.gz"

# Keep last 30 days
find "$BACKUP_DIR" -name "mygarage-*.sql.gz" -mtime +30 -delete

Cron Job:

0 2 * * * /path/to/backup-postgres.sh

Backups and secret settings

Secret settings (SSO client secret, notification tokens, API keys and the like) are stored encrypted, and every backup carries them encrypted: the settings backup (version 2.1), the full backup, and the mygarage.db copies and pg_dumps above. None of them holds the key, settings.key, or the login key, secret.key. Back those up separately; see Authentication - Settings Encryption Key.

  • Restoring on a new host: copy settings.key over first. Without it, a full restore brings every secret back unreadable, and Settings names them so you can re-enter them. Expect one sign-in, since secret.key doesn't travel either.
  • Settings restore: a secret that no configured key opens is skipped. It keeps its current value, and the restore's warning names it. A secret under an older key still in the keyring is re-encrypted under the current one. An older settings backup (version 2.0) holds secrets in plaintext; the restore encrypts them.
  • Refused backups: while a secret is still stored unencrypted and no key can encrypt it, creating or restoring a backup answers 503 and writes nothing. The Backup tab shows the fix: set MYGARAGE_SETTINGS_KEY, or make the data directory writable.

Old plaintext inside the database files. Copies of the old values can survive the upgrade: in SQLite's free space until a VACUUM (see SQLite Maintenance; run it once if the file may leave the host), and in PostgreSQL's files and WAL until they're reused (pg_dump doesn't carry them). Delete database copies taken before the upgrade, or keep them as safely as the live data.

Restoring Backups

Restore SQLite

From Settings → Backup & Restore (admins only):

Pick a full backup and choose Restore. MyGarage checks the backup, stages it, and keeps running on the current data. Restart MyGarage to finish the restore. Anything changed before the restart is replaced by the backup, and an older backup is migrated on that start.

  • A backup whose database is damaged, or that has no database, is refused before anything is staged.
  • Until the restart, the Backup tab shows the staged restore with a Cancel restore button. Without the tab, deleting data/.restore-pending/manifest.json cancels it too, and the next start throws the rest of the staging away.
  • The restore request saves mygarage-full-safety-<time>.tar.gz in data/backups/, and the restart saves mygarage-full-safety-prerestore-<time>.tar.gz, a full backup of the data it replaces. Restore either one like any other backup to go back.
  • Until the restart, a second copy of the restored data sits in /data, so check the disk has room for it.
  • The photos, documents and attachments folders must be on the same filesystem as /data itself. If one is mounted separately, the restore is refused; restore that backup by file copy instead.
  • A photo, document or attachment that was a link to a folder or to a file outside the backup is left out, and the log names it.
  • The first start after restoring a large photo library can be slow: it backs up and swaps every file before it serves. The container reads unhealthy until it finishes. restart: unless-stopped acts on the exit code, not health, so just wait.
  • If a start can't get past a restore (for example, the disk filled up halfway), MyGarage refuses to start, and its log says how to finish the restore, cancel it, or go back to the pre-restore safety backup.

From File Copy:

# Stop MyGarage
docker-compose stop mygarage

# Replace database file and clear stale WAL/SHM sidecars
cp mygarage-backup-2024-12-02.db data/mygarage.db
rm -f data/mygarage.db-wal data/mygarage.db-shm

# Restart MyGarage
docker-compose start mygarage

Restore PostgreSQL

From pg_dump:

# Drop existing database (WARNING: deletes all data!)
docker exec mygarage-postgres psql -U postgres -c "DROP DATABASE mygarage;"

# Recreate database
docker exec mygarage-postgres psql -U postgres -c "CREATE DATABASE mygarage OWNER mygarage;"

# Restore from backup
docker exec -i mygarage-postgres psql -U mygarage mygarage < mygarage-backup.sql

# Or from gzip
gunzip < mygarage-backup.sql.gz | docker exec -i mygarage-postgres psql -U mygarage mygarage

Database Maintenance

SQLite Maintenance

The image has no sqlite3 command, so these use Python's sqlite3 module.

Vacuum (Optimize):

Reclaim unused space and defragment:

docker exec mygarage python3 -c "import sqlite3; sqlite3.connect('/data/mygarage.db').execute('VACUUM')"

Analyze (Update Statistics):

docker exec mygarage python3 -c "import sqlite3; sqlite3.connect('/data/mygarage.db').execute('ANALYZE')"

Check Integrity:

docker exec mygarage python3 -c "import sqlite3; print(sqlite3.connect('/data/mygarage.db').execute('PRAGMA integrity_check').fetchone()[0])"

PostgreSQL Maintenance

Vacuum (Reclaim Space):

docker exec mygarage-postgres psql -U mygarage -c "VACUUM FULL;"

Analyze (Update Statistics):

docker exec mygarage-postgres psql -U mygarage -c "ANALYZE;"

Reindex (Rebuild Indexes):

docker exec mygarage-postgres psql -U mygarage -d mygarage -c "REINDEX DATABASE mygarage;"

Check Database Size:

docker exec mygarage-postgres psql -U mygarage -c "SELECT pg_size_pretty(pg_database_size('mygarage'));"

Performance Optimization

SQLite Optimization

MyGarage enables WAL mode, a 30-second busy_timeout, and foreign_keys=ON automatically on every connection (since v2.31). No manual PRAGMA tuning is required.

PostgreSQL Optimization

Connection Pooling:

MyGarage uses SQLAlchemy connection pooling by default.

The PostgreSQL connection pool is fixed in code at 5 base connections plus 10 overflow, with pool_pre_ping enabled. It is not configurable via environment variables.

PostgreSQL Configuration (postgresql.conf):

# Memory
shared_buffers = 256MB
effective_cache_size = 1GB
work_mem = 16MB
maintenance_work_mem = 64MB

# Checkpoints
checkpoint_completion_target = 0.9
wal_buffers = 16MB

# Query Planning
random_page_cost = 1.1  # SSD

Indexes: MyGarage creates its own indexes when it creates its tables. No manual indexes are needed.

Database Monitoring

SQLite Monitoring

Check Database Size:

ls -lh data/mygarage.db

Row Counts:

docker exec mygarage python3 -c "import sqlite3; c=sqlite3.connect('/data/mygarage.db'); [print(t, c.execute(f'SELECT COUNT(*) FROM {t}').fetchone()[0]) for t in ('vehicles', 'service_visits', 'fuel_records')]"

PostgreSQL Monitoring

Connection Count:

docker exec mygarage-postgres psql -U mygarage -c "SELECT count(*) FROM pg_stat_activity WHERE datname='mygarage';"

Table Sizes:

docker exec mygarage-postgres psql -U mygarage -d mygarage -c "
SELECT
  relname AS table_name,
  pg_size_pretty(pg_total_relation_size(relid)) AS size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;
"

Slow Queries (requires pg_stat_statements extension):

SELECT
  query,
  calls,
  total_exec_time,
  mean_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

Database Security

SQLite Security

  • ✅ File permissions - Ensure only MyGarage container can access .db file
  • ✅ Backup encryption - Encrypt backup files before storing remotely
  • ⚠️ No built-in encryption - SQLite stores data in plain text

PostgreSQL Security

Strong Passwords:

environment:
  POSTGRES_PASSWORD: "Use-Long-Random-Password-Here-32-Chars-Min"

SSL/TLS Connections (for remote PostgreSQL):

Leave SSL options out of the URL and set PGSSLMODE instead. MyGarage runs its migrations through a second driver, which refuses ?ssl=require in the URL, so the migrations would not run.

environment:
  - MYGARAGE_DATABASE_URL=postgresql+asyncpg://user:pass@host:5432/mygarage
  - PGSSLMODE=require

Network Isolation (Docker):

networks:
  mygarage-internal:
    internal: true  # No external access

services:
  postgres:
    networks:
      - mygarage-internal  # Only accessible by MyGarage

Restrict PostgreSQL Access (pg_hba.conf):

# Only allow MyGarage container
host    mygarage    mygarage    172.20.0.0/16    md5

Troubleshooting

"Database is locked" (SQLite)

WAL mode and a 30-second busy_timeout are enabled automatically (v2.30), so this should not occur under normal use. If it does, the data volume is likely on a network filesystem (NFS/SMB) that doesn't support SQLite locking, or the disk is full. Use local storage and check df -h.

"Connection refused" (PostgreSQL)

Cause: PostgreSQL container not running or wrong hostname

Fix:

# Check PostgreSQL container status
docker ps | grep postgres

# Check connection
docker exec mygarage-postgres psql -U mygarage -c "SELECT 1;"

# Verify hostname in DATABASE_URL matches container name

"Authentication failed" (PostgreSQL)

Cause: Wrong username/password in connection string

Fix:

  1. Verify POSTGRES_USER and POSTGRES_PASSWORD in docker-compose.yml
  2. Ensure MYGARAGE_DATABASE_URL uses same credentials
  3. Reset PostgreSQL password: 
    docker exec mygarage-postgres psql -U postgres -c "ALTER USER mygarage WITH PASSWORD 'new-password';"

Slow Queries

SQLite:

  • Run VACUUM and ANALYZE
  • Consider migrating to PostgreSQL

PostgreSQL:

  • Check indexes exist
  • Run VACUUM ANALYZE
  • Increase shared_buffers in postgresql.conf
  • Review slow query log

Need Help?

Next: Configure Reverse Proxy or read FAQ.

Clone this wiki locally