Skip to main content

Database Variables

PostgreSQL database configuration for Chatty AI.

Overview​

Chatty AI uses PostgreSQL as its primary database for storing:

  • User accounts and authentication
  • Chat conversations and history
  • Document metadata
  • RAG collections
  • Application settings

Connection Variables​

DB_USER​

  • Type: String
  • Required: No
  • Default: chattyAdmin
  • Example: chattyAdmin
  • Description: PostgreSQL username for database connections
  • Used in:
    • db service (POSTGRES_USER)
    • chattyai service (DATABASE_URL)

DB_NAME​

  • Type: String
  • Required: No
  • Default: chattydb
  • Example: chattydb
  • Description: PostgreSQL database name
  • Used in:
    • db service (POSTGRES_DB)
    • chattyai service (DATABASE_URL)

DB_PASSWORD​

  • Type: String (password)
  • Required: ✅ Yes
  • Default: None
  • Security: 🔴 HIGH - Use strong password
  • Example: MyS3cur3P@ssw0rd!2024
  • Description: PostgreSQL database password
  • Best Practice: Generate using openssl rand -base64 32
  • Used in:
    • db service (POSTGRES_PASSWORD)
    • chattyai service (DATABASE_URL)

DB_PORT​

  • Type: Integer
  • Required: No
  • Default: 5432
  • Example: 5432
  • Description: PostgreSQL port exposed on host (for external access)
  • Note:
    • Internal container always uses port 5432
    • Commented out in Portainer deployment (no external access)
    • Uncomment for Docker Compose if you need external database access
  • Used in: db service (port mapping)

Connection String​

DATABASE_URL​

  • Type: String (connection string)
  • Required: No (auto-generated)
  • Default: postgresql://${DB_USER}:${DB_PASSWORD}@db:5432/${DB_NAME}
  • Example: postgresql://chattyAdmin:password@db:5432/chattydb
  • Description: Full PostgreSQL connection string
  • Format: postgresql://[user]:[password]@[host]:[port]/[database]
  • Auto-generated: Yes (in docker-compose.yaml)
  • Used in: chattyai service

Note: You don't need to set this manually. Docker Compose automatically generates it from DB_USER, DB_PASSWORD, and DB_NAME.

Configuration Example​

Minimal Configuration​

# Only set password (others use defaults)
DB_PASSWORD=$(openssl rand -base64 32)

Custom Configuration​

# Custom database settings
DB_USER=myapp_user
DB_NAME=myapp_database
DB_PASSWORD=MySecurePassword123!
DB_PORT=5432  # Uncomment in docker-compose if needed

Connection String (Auto-Generated)​

# This is automatically set by docker-compose
DATABASE_URL=postgresql://myapp_user:MySecurePassword123!@db:5432/myapp_database

Security Best Practices​

1. Strong Password​

Generate a secure password:

# Generate 32-character password
openssl rand -base64 32

# Or use a password manager

Requirements:

  • Minimum 16 characters
  • Mix of uppercase, lowercase, numbers, symbols
  • No dictionary words
  • Unique (not reused)

2. Restrict External Access​

Portainer Deployment (Recommended):

  • DB_PORT is commented out
  • Database only accessible within Docker network
  • No external connections possible

Docker Compose Deployment:

  • Keep DB_PORT commented out unless needed
  • If exposing port, use firewall rules:
# Only allow localhost
sudo ufw deny 5432
sudo ufw allow from 127.0.0.1 to any port 5432

3. Never Commit Passwords​

# .env file should be in .gitignore
echo ".env" >> .gitignore

4. Rotate Passwords​

Change database password every 180 days:

# 1. Generate new password
NEW_PASSWORD=$(openssl rand -base64 32)

# 2. Update .env file
DB_PASSWORD=$NEW_PASSWORD

# 3. Restart services
docker compose down
docker compose up -d

Database Management​

Connect to Database​

From host (if DB_PORT exposed):

psql -h localhost -p 5432 -U chattyAdmin -d chattydb

From within Docker network:

docker compose exec db psql -U chattyAdmin -d chattydb

Backup Database​

# Backup to file
docker compose exec db pg_dump -U chattyAdmin chattydb > backup-$(date +%Y%m%d).sql

# Compressed backup
docker compose exec db pg_dump -U chattyAdmin chattydb | gzip > backup-$(date +%Y%m%d).sql.gz

Restore Database​

# Restore from file
docker compose exec -T db psql -U chattyAdmin -d chattydb < backup.sql

# Restore from compressed
gunzip -c backup.sql.gz | docker compose exec -T db psql -U chattyAdmin -d chattydb

Check Database Size​

docker compose exec db psql -U chattyAdmin -d chattydb -c "SELECT pg_size_pretty(pg_database_size('chattydb'));"

Troubleshooting​

Connection Refused​

Check database is running:

docker compose ps db
docker compose logs db

Authentication Failed​

Verify password in .env:

grep DB_PASSWORD .env

Check DATABASE_URL is correct:

docker compose exec chattyai env | grep DATABASE_URL

Database Not Initialized​

Check initialization logs:

docker compose logs db | grep "database system is ready"

If database failed to initialize, recreate it:

docker compose down
docker volume rm chatty-app_db_data
docker compose up -d

⚠️ Warning: This deletes all data!

Out of Disk Space​

Check volume size:

docker system df -v | grep chatty-app_db_data

Clean up old data or increase storage.

Performance Tuning​

For Production​

Consider these PostgreSQL settings (requires custom postgresql.conf):

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

# Connections
max_connections = 100

# WAL
wal_buffers = 16MB
checkpoint_completion_target = 0.9

Mount custom config:

# In docker-compose.yaml
db:
  volumes:
    - ./postgresql.conf:/etc/postgresql/postgresql.conf
  command: postgres -c config_file=/etc/postgresql/postgresql.conf