home / selfhost / manage / database

Database Management¶

Manage your Tux database including backups, migrations, and administration tools.

Database Backups¶

Protect your data with regular backups.

Backup Strategies¶

Manual Backup¶

# Docker Compose
docker compose exec tux-postgres pg_dump -U tuxuser tuxdb > backup_$(date +%Y%m%d).sql

# With compression
docker compose exec tux-postgres pg_dump -U tuxuser tuxdb | gzip > backup_$(date +%Y%m%d).sql.gz

# Local PostgreSQL
pg_dump -h localhost -U tuxuser tuxdb > backup.sql

Automated Backups¶

Create backup.sh:

#!/bin/bash
BACKUP_DIR="/backups"
DATE=$(date +%Y%m%d_%H%M%S)

# Create backup
docker compose exec tux-postgres pg_dump -U tuxuser tuxdb | gzip > "$BACKUP_DIR/tux_$DATE.sql.gz"

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

# Optional: Upload to cloud storage
# rclone copy "$BACKUP_DIR/tux_$DATE.sql.gz" remote:backups/

Add to cron:

# Daily at 2 AM
0 2 * * * /path/to/backup.sh

Restore¶

# From SQL file
docker compose exec -T tux-postgres psql -U tuxuser tuxdb < backup.sql

# From gzip
gunzip < backup.sql.gz | docker compose exec -T tux-postgres psql -U tuxuser tuxdb

# Or
docker compose exec tux-postgres psql -U tuxuser tuxdb < backup.sql

Best Practices¶

• Backup daily (minimum)
• Test restore procedures regularly
• Store backups off-site
• Encrypt sensitive backups
• Keep multiple backup generations
• Document restore process

Database Migrations¶

Manage database schema changes with Alembic migrations.

What Are Migrations?¶

Migrations are version-controlled database schema changes:

• Track schema history
• Apply changes incrementally
• Rollback if needed
• Share schema changes with team

Tux uses Alembic for migrations.

CLI Commands¶

Apply Migrations¶

# Apply all pending migrations
uv run db push

# Check status
uv run db status

# View history
uv run db history

After Updates¶

When updating Tux:

git pull
uv sync
uv run db push                      # Apply new migrations
docker compose restart tux          # Restart bot

Migration Files¶

Located in: src/tux/database/migrations/versions/

Don't manually edit migration files unless you know what you're doing.

Docker Migrations¶

Migrations can run automatically on container startup:

# In .env
FORCE_MIGRATE=true                  # Auto-run migrations on start
USE_LOCAL_MIGRATIONS=true           # Use mounted migration files

Troubleshooting¶

Migration Fails¶

# Check what's wrong
uv run db status

# View specific migration
uv run db show head

# Check for conflicts
uv run db check

Database Out of Sync¶

# Reset safely (via migrations)
uv run db reset

# Nuclear option (destroys data!)
uv run db nuke --force
uv run db push

Adminer Web UI¶

Web-based database administration interface.

Accessing Adminer¶

Docker Compose users:

Adminer is included and runs on port 8080:

http://localhost:8080

Auto-login is enabled by default for development.

Features¶

• Browse tables and data
• Run SQL queries
• Export/import data
• View table structure
• Edit records directly
• User-friendly interface
• Dracula theme

Manual Login¶

If auto-login is disabled:

• System: PostgreSQL
• Server: tux-postgres
• Username: tuxuser
• Password: (from your .env)
• Database: tuxdb

Common Tasks¶

Browse Data¶

1. Click database name (tuxdb)
2. Click table name
3. View/edit data

Run SQL Query¶

1. Click "SQL command"
2. Enter your query
3. Click "Execute"

Export Database¶

1. Click "Export"
2. Choose format (SQL, CSV)
3. Click "Export"

Security¶

In .env:

ADMINER_AUTO_LOGIN=false

SSH Tunnel¶

For remote access:

ssh -L 8080:localhost:8080 user@your-server

Then access http://localhost:8080 on your local machine.

Configuration¶

Change Port¶

In .env:

ADMINER_PORT=9090

Then access at http://localhost:9090

Disable Adminer¶

Comment out in compose.yaml or:

docker compose stop tux-adminer

Related¶

• Database Setup
• Database CLI Reference
• Developer Migration Guide