Database Setup

Journiv supports both PostgreSQL (default) and SQLite databases. PostgreSQL is recommended for database for Journiv. SQLite is supported for quickstart, development and testing.

Choosing a Database

SQLite Configuration

SQLite is the default database and requires no configuration. Journiv automatically creates the database file if it doesn't exist.

Default Location

Docker:

• Database file: /data/journiv.db
• Stored in the Docker volume

Manual Installation:

• Default: ./journiv.db (relative to application directory)
• Or specify custom path: sqlite:////absolute/path/to/journiv.db

Custom SQLite Path

To use a custom SQLite database location, set the DATABASE_URL environment variable:

# Relative path
DATABASE_URL=sqlite:///./custom/path/journiv.db

# Absolute path (note the 4 slashes)
DATABASE_URL=sqlite:////var/lib/journiv/journiv.db

SQLite Optimizations

Journiv automatically configures SQLite with optimal settings:

• WAL Mode: Better concurrency for read operations
• Foreign Keys: Enabled for data integrity
• Cache Size: Increased for better performance
• Synchronous: Set to NORMAL for balanced safety/performance

Backup SQLite

SQLite databases are simple files that can be backed up:

# Docker
docker compose exec journiv cp /data/journiv.db /data/backup-$(date +%Y%m%d).db

# Manual
cp journiv.db backup-$(date +%Y%m%d).db

PostgreSQL Configuration

PostgreSQL requires a separate database server. You can configure it using either a connection string or individual components.

Using Connection String (Recommended)

Set the DATABASE_URL environment variable:

DATABASE_URL=postgresql://user:password@host:5432/journiv

Example:

DATABASE_URL=postgresql://journiv:secure-password@postgres:5432/journiv

Using Individual Components

Alternatively, configure PostgreSQL using individual environment variables:

POSTGRES_HOST=postgres
POSTGRES_USER=journiv
POSTGRES_PASSWORD=secure-password
POSTGRES_DB=journiv
POSTGRES_PORT=5432

Docker Compose with PostgreSQL

Example docker-compose.yml with PostgreSQL:

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

  journiv:
    image: swalabtech/journiv-app:latest
    container_name: journiv
    ports:
      - "8000:8000"
    environment:
      - SECRET_KEY=your-secret-key-here
      - DOMAIN_NAME=192.168.1.1
      - DATABASE_URL=postgresql://journiv:your-secure-password@postgres:5432/journiv
    volumes:
      - journiv_data:/data
    depends_on:
      postgres:
        condition: service_healthy
    restart: unless-stopped

volumes:
  journiv_data:
  postgres_data:

PostgreSQL Connection Pooling

Journiv automatically configures PostgreSQL with connection pooling:

• Pool Size: 5 connections
• Max Overflow: 3 additional connections
• Pool Recycle: Connections recycled every hour
• Pool Pre-ping: Enabled for connection health checks

PostgreSQL Security Best Practices

1. Use Strong Passwords: Generate secure passwords for database users
2. Network Security: Restrict database access to only the application server
3. SSL/TLS: Enable SSL connections in production:

   DATABASE_URL=postgresql://user:password@host:5432/journiv?sslmode=require

4. Separate Users: Use dedicated database users with minimal privileges
5. Regular Updates: Keep PostgreSQL updated to the latest stable version

Backup PostgreSQL

Using pg_dump:

# Docker Compose
docker compose exec postgres pg_dump -U journiv journiv > backup-$(date +%Y%m%d).sql

# Manual
pg_dump -U journiv -h localhost journiv > backup-$(date +%Y%m%d).sql

Restore:

# Docker Compose
docker compose exec -T postgres psql -U journiv journiv < backup-YYYYMMDD.sql

# Manual
psql -U journiv -h localhost journiv < backup-YYYYMMDD.sql

Migrating Between Databases

From SQLite to PostgreSQL

1. Export data from SQLite:

   • Use Journiv's export feature to export all your data
   • Or use SQLite's .dump command

2. Set up PostgreSQL:

   • Install and configure PostgreSQL
   • Create the database and user

3. Update configuration:

   • Set DATABASE_URL to your PostgreSQL connection string
   • Restart Journiv

4. Import data:

   • Use Journiv's import feature to import your exported data
   • Or restore from a backup

From PostgreSQL to SQLite

1. Export data:

   • Use Journiv's export feature
   • Or create a PostgreSQL dump

2. Update configuration:

   • Remove or comment out DATABASE_URL (to use SQLite default)
   • Or set DATABASE_URL=sqlite:////data/journiv.db

3. Restart and import:

   • Restart Journiv (will create new SQLite database)
   • Import your exported data

Troubleshooting

SQLite Issues

Permission errors:

# Ensure the directory is writable
chmod 755 /data
chown -R $(id -u):$(id -g) /data

Database locked:

• Usually occurs with multiple write operations
• Consider migrating to PostgreSQL for better concurrency
• Ensure only one instance is accessing the database

PostgreSQL Issues

Connection refused:

• Verify PostgreSQL is running: docker compose ps or systemctl status postgresql
• Check DATABASE_URL is correct
• Verify network connectivity between containers

Authentication failed:

• Verify username and password match
• Check PostgreSQL user exists and has proper permissions
• Review PostgreSQL logs: docker compose logs postgres

Database does not exist:

• Create the database: CREATE DATABASE journiv;
• Or let Journiv create it automatically (if user has CREATE DATABASE permission)

Connection pool exhausted:

• Increase pool_size in database configuration (requires code changes)
• Check for connection leaks in application logs
• Monitor active connections: SELECT count(*) FROM pg_stat_activity;

Environment Variables Reference

See Environment Variables for complete reference.

Next Steps

• Configure your database using the methods above
• Set up regular backups for your database
• Review Security Guide for database security best practices
• Monitor database performance and adjust as needed