TimeTagger now supports PostgreSQL as a database backend, offering improved performance, reliability, and concurrency for multi-user environments.
While SQLite is excellent for single-user setups or smaller deployments, PostgreSQL offers several advantages for larger or production environments:
- Concurrency: Multiple users can write to the database simultaneously without locking
- Reliability: Built-in features for data integrity and crash recovery
- Scalability: Better performance as your data and user base grows
- Advanced Features: Full SQL support, robust indexing, and query optimization
The easiest way to set up TimeTagger with PostgreSQL is using Docker Compose:
- Create or modify your
.envfile with PostgreSQL configuration:
# PostgreSQL Configuration
POSTGRES_USER=timetagger
POSTGRES_PASSWORD=your_secure_password
POSTGRES_DB=timetagger
POSTGRES_HOST=postgres
POSTGRES_PORT=5432
# TimeTagger Database URL
TIMETAGGER_DB_URL=postgresql://timetagger:your_secure_password@postgres:5432/timetagger
- Update your
docker-compose.ymlto include PostgreSQL:
version: '3'
services:
postgres:
image: postgres:15
environment:
- POSTGRES_USER=${POSTGRES_USER}
- POSTGRES_PASSWORD=${POSTGRES_PASSWORD}
- POSTGRES_DB=${POSTGRES_DB}
volumes:
- postgres_data:/var/lib/postgresql/data
restart: unless-stopped
timetagger:
image: timetagger/timetagger:latest
# Or build from local source
# build: .
depends_on:
- postgres
environment:
- TIMETAGGER_DB_URL=${TIMETAGGER_DB_URL}
- TIMETAGGER_BIND=0.0.0.0:8000
- TIMETAGGER_CREDENTIALS=${TIMETAGGER_CREDENTIALS}
ports:
- "8000:8000"
volumes:
- timetagger_data:/app/data
restart: unless-stopped
volumes:
postgres_data:
timetagger_data:- Launch the services:
docker-compose up -dIf you prefer to manage PostgreSQL separately:
-
Install PostgreSQL on your system or use a hosted PostgreSQL service
-
Create a database and user for TimeTagger:
CREATE DATABASE timetagger;
CREATE USER timetagger WITH PASSWORD 'your_secure_password';
GRANT ALL PRIVILEGES ON DATABASE timetagger TO timetagger;- Configure TimeTagger to use PostgreSQL by setting the environment variable:
export TIMETAGGER_DB_URL=postgresql://timetagger:your_secure_password@localhost:5432/timetaggerOr add it to your .env file if using Docker.
If you're transitioning from SQLite to PostgreSQL, TimeTagger provides a migration script:
-
Make sure PostgreSQL is running and configured
-
Run the migration script:
python scripts/migrate_to_postgres.py --all --login-db --validateThis script will:
- Initialize the PostgreSQL database
- Migrate all user data from SQLite to PostgreSQL
- Migrate the login tracking database
- Validate that the migration was successful
The script supports the following options:
--username USERNAME: Migrate a specific user only--all: Migrate all users (recommended)--login-db: Migrate the central login database--validate: Verify migration success by comparing record counts
If TimeTagger cannot connect to PostgreSQL:
- Verify PostgreSQL is running and accessible
- Check the database URL format:
postgresql://username:password@host:port/database - Ensure network connectivity between TimeTagger and PostgreSQL
- Check PostgreSQL logs for connection errors
If you encounter issues during migration:
- Check the migration log file (
migration.log) - Ensure the PostgreSQL user has sufficient permissions
- Try migrating a single user first with
--usernameto identify specific issues - Verify SQLite databases exist and are not corrupted
Always backup your data before and after migration:
pg_dump -U timetagger -d timetagger -F c -f timetagger_backup.dumppg_restore -U timetagger -d timetagger -c timetagger_backup.dump# Copy the entire users directory
cp -r /path/to/timetagger/data/users /backup/locationFor larger deployments, consider these PostgreSQL optimizations:
# Add to postgresql.conf
shared_buffers = 1GB
work_mem = 32MB
maintenance_work_mem = 256MB
effective_cache_size = 2GB
Adjust values based on your server resources and usage patterns.