ANTE User Manual

Appearance

Database Setup & Management ​

Complete guide to managing databases in your self-hosted ANTE ERP installation.

Database Architecture ​

ANTE ERP uses a multi-database architecture:

┌────────────────────┐
│   ANTE Backend     │
└────────┬───────────┘
         │
    ┌────┴─────────────────────────┐
    │                              │
┌───▼─────────┐  ┌────▼────────┐  ┌▼──────────┐
│ PostgreSQL  │  │    Redis    │  │  MongoDB  │
│             │  │             │  │           │
│ Relational  │  │   Cache &   │  │ Documents │
│    Data     │  │   Sessions  │  │ & Logs    │
└─────────────┘  └─────────────┘  └───────────┘

PostgreSQL - Primary Database ​

  • Stores all business data
  • User accounts and permissions
  • Transactions and records
  • Relational integrity

Redis - Cache & Sessions ​

  • User sessions
  • Application cache
  • Real-time data
  • Queue management

MongoDB - Document Storage ​

  • File metadata
  • Application logs
  • Analytics data
  • Flexible schemas

PostgreSQL Configuration ​

Connection Details ​

Default connection (Docker network):

Host: postgres (or localhost from host machine)
Port: 5432 (internal) / 5433 (exposed)
Database: ante_db
Username: ante
Password: [from .env DB_PASSWORD]

Managing PostgreSQL ​

Access Database Shell ​

bash
# Using docker compose
docker compose exec postgres psql -U ante -d ante_db

# From host machine
psql -h localhost -p 5433 -U ante -d ante_db

Common PostgreSQL Commands ​

sql
-- List all databases
\l

-- Connect to database
\c ante_db

-- List all tables
\dt

-- Describe table structure
\d users

-- Check database size
SELECT pg_size_pretty(pg_database_size('ante_db'));

-- List active connections
SELECT count(*) FROM pg_stat_activity;

-- View running queries
SELECT pid, age(clock_timestamp(), query_start), usename, query 
FROM pg_stat_activity 
WHERE query != '<IDLE>' 
ORDER BY query_start;

Database Backups ​

Manual Backup ​

bash
# Full database backup
docker compose exec -T postgres pg_dump -U ante ante_db > backup_$(date +%Y%m%d_%H%M%S).sql

# Compressed backup (recommended)
docker compose exec -T postgres pg_dump -U ante ante_db | gzip > backup_$(date +%Y%m%d_%H%M%S).sql.gz

# Backup specific tables
docker compose exec -T postgres pg_dump -U ante -t users -t projects ante_db > users_projects_backup.sql

Automated Backup Script ​

Create ~/ante-erp/scripts/backup-postgres.sh:

bash
#!/bin/bash

# Configuration
BACKUP_DIR=~/ante-erp/backups
COMPOSE_FILE=~/ante-erp/docker-compose.yml
DATE=$(date +%Y%m%d_%H%M%S)
RETENTION_DAYS=30

# Create backup directory
mkdir -p $BACKUP_DIR

# Perform backup
echo "Starting PostgreSQL backup: $DATE"
docker compose -f $COMPOSE_FILE exec -T postgres \
  pg_dump -U ante ante_db | gzip > $BACKUP_DIR/postgres_$DATE.sql.gz

# Check if backup successful
if [ $? -eq 0 ]; then
    echo "Backup completed successfully: postgres_$DATE.sql.gz"
    
    # Remove old backups
    find $BACKUP_DIR -name "postgres_*.sql.gz" -mtime +$RETENTION_DAYS -delete
    echo "Old backups cleaned up (retention: $RETENTION_DAYS days)"
else
    echo "Backup failed!" >&2
    exit 1
fi

# Show backup size
ls -lh $BACKUP_DIR/postgres_$DATE.sql.gz

Make it executable:

bash
chmod +x ~/ante-erp/scripts/backup-postgres.sh

Schedule with cron (daily at 2 AM):

bash
crontab -e
# Add this line:
0 2 * * * ~/ante-erp/scripts/backup-postgres.sh >> ~/ante-erp/logs/backup.log 2>&1

Restore from Backup ​

bash
# Stop the backend to prevent conflicts
docker compose stop backend

# Restore from uncompressed backup
docker compose exec -T postgres psql -U ante -d ante_db < backup_20251027_120000.sql

# Restore from compressed backup
gunzip -c backup_20251027_120000.sql.gz | docker compose exec -T postgres psql -U ante -d ante_db

# Restart backend
docker compose start backend

Database Migrations ​

ANTE ERP uses Prisma for database migrations.

Run Migrations ​

bash
# Migrations run automatically on first startup

# Manually run pending migrations
docker compose exec backend npx prisma migrate deploy

# View migration status
docker compose exec backend npx prisma migrate status

Reset Database (Development Only!) ​

bash
# ⚠️ WARNING: This deletes all data!
docker compose exec backend npx prisma migrate reset

Performance Tuning ​

PostgreSQL Configuration ​

Edit PostgreSQL settings in docker-compose.yml:

yaml
services:
  postgres:
    command:
      - postgres
      - -c
      - max_connections=100
      - -c
      - shared_buffers=256MB
      - -c
      - effective_cache_size=1GB
      - -c
      - maintenance_work_mem=64MB
      - -c
      - checkpoint_completion_target=0.9
      - -c
      - wal_buffers=16MB
      - -c
      - default_statistics_target=100
      - -c
      - random_page_cost=1.1
      - -c
      - work_mem=4MB

Optimize for Different Workloads ​

For Small Deployments (4GB RAM):

shared_buffers = 1GB
effective_cache_size = 3GB
maintenance_work_mem = 256MB
work_mem = 16MB

For Medium Deployments (8GB RAM):

shared_buffers = 2GB
effective_cache_size = 6GB
maintenance_work_mem = 512MB
work_mem = 32MB

For Large Deployments (16GB+ RAM):

shared_buffers = 4GB
effective_cache_size = 12GB
maintenance_work_mem = 1GB
work_mem = 64MB

Redis Configuration ​

Connection Details ​

Host: redis (or localhost from host)
Port: 6379 (internal) / 6380 (exposed)
Password: [from .env REDIS_PASSWORD]
Database: 0

Managing Redis ​

Access Redis CLI ​

bash
# Using docker compose
docker compose exec redis redis-cli -a "$REDIS_PASSWORD"

# From host machine
redis-cli -h localhost -p 6380 -a "$REDIS_PASSWORD"

Common Redis Commands ​

bash
# Test connection
redis-cli> PING
# Returns: PONG

# Get all keys
redis-cli> KEYS *

# Get specific key
redis-cli> GET session:abc123

# Check memory usage
redis-cli> INFO memory

# Get database stats
redis-cli> INFO stats

# Monitor commands in real-time
redis-cli> MONITOR

# Clear all cache (careful!)
redis-cli> FLUSHALL

Redis Backups ​

Redis automatically saves to disk based on configuration.

Manual Backup ​

bash
# Trigger immediate save
docker compose exec redis redis-cli -a "$REDIS_PASSWORD" SAVE

# Create backup copy
docker compose exec redis redis-cli -a "$REDIS_PASSWORD" BGSAVE

# Copy RDB file
docker cp ante-redis:/data/dump.rdb ./backups/redis_$(date +%Y%m%d).rdb

Configure Persistence ​

In docker-compose.yml:

yaml
services:
  redis:
    command: >
      redis-server
      --requirepass ${REDIS_PASSWORD}
      --save 900 1      # Save after 900 sec if 1 key changed
      --save 300 10     # Save after 300 sec if 10 keys changed
      --save 60 10000   # Save after 60 sec if 10000 keys changed
      --appendonly yes  # Enable AOF persistence

Redis Performance ​

Memory Management ​

yaml
services:
  redis:
    command: >
      redis-server
      --requirepass ${REDIS_PASSWORD}
      --maxmemory 512mb           # Set memory limit
      --maxmemory-policy allkeys-lru  # Eviction policy

Eviction Policies:

  • allkeys-lru - Evict least recently used keys
  • volatile-lru - Evict LRU keys with expiration
  • allkeys-random - Evict random keys
  • noeviction - Return errors when memory limit reached

MongoDB Configuration ​

Connection Details ​

Host: mongodb (or localhost from host)
Port: 27017 (internal) / 27018 (exposed)
Username: ante
Password: [from .env MONGO_PASSWORD]
Auth Database: admin
Database: ante

Managing MongoDB ​

Access MongoDB Shell ​

bash
# Using docker compose
docker compose exec mongodb mongosh --username ante --password "$MONGO_PASSWORD" --authenticationDatabase admin

# From host machine
mongosh "mongodb://ante:password@localhost:27018/ante?authSource=admin"

Common MongoDB Commands ​

javascript
// List databases
show dbs

// Use database
use ante

// List collections
show collections

// Count documents
db.logs.countDocuments()

// Find documents
db.files.find().limit(10)

// Database stats
db.stats()

// Collection stats
db.logs.stats()

// Drop collection (careful!)
db.old_collection.drop()

MongoDB Backups ​

Manual Backup ​

bash
# Full database backup
docker compose exec -T mongodb mongodump \
  --username=ante \
  --password="$MONGO_PASSWORD" \
  --authenticationDatabase=admin \
  --db=ante \
  --out=/backup

# Copy backup to host
docker cp ante-mongodb:/backup ./backups/mongodb_$(date +%Y%m%d)

# Compressed backup
docker compose exec -T mongodb mongodump \
  --username=ante \
  --password="$MONGO_PASSWORD" \
  --authenticationDatabase=admin \
  --db=ante \
  --archive | gzip > backups/mongodb_$(date +%Y%m%d).archive.gz

Restore from Backup ​

bash
# Restore from directory
docker compose exec -T mongodb mongorestore \
  --username=ante \
  --password="$MONGO_PASSWORD" \
  --authenticationDatabase=admin \
  --db=ante \
  /backup/ante

# Restore from archive
gunzip -c backups/mongodb_20251027.archive.gz | \
  docker compose exec -T mongodb mongorestore \
    --username=ante \
    --password="$MONGO_PASSWORD" \
    --authenticationDatabase=admin \
    --archive

Database Monitoring ​

Health Checks ​

bash
# Check all databases
docker compose ps postgres redis mongodb

# Check PostgreSQL health
docker compose exec postgres pg_isready -U ante

# Check Redis health
docker compose exec redis redis-cli -a "$REDIS_PASSWORD" ping

# Check MongoDB health
docker compose exec mongodb mongosh \
  --username ante \
  --password "$MONGO_PASSWORD" \
  --authenticationDatabase admin \
  --eval "db.adminCommand('ping')"

Performance Monitoring ​

PostgreSQL Monitoring ​

sql
-- Active connections
SELECT count(*) FROM pg_stat_activity;

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

-- Table sizes
SELECT schemaname, tablename,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

-- Index usage
SELECT schemaname, tablename, indexname,
  idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

Redis Monitoring ​

bash
# Real-time stats
docker compose exec redis redis-cli -a "$REDIS_PASSWORD" --stat

# Memory info
docker compose exec redis redis-cli -a "$REDIS_PASSWORD" INFO memory

# Slow log
docker compose exec redis redis-cli -a "$REDIS_PASSWORD" SLOWLOG GET 10

MongoDB Monitoring ​

javascript
// Server status
db.serverStatus()

// Current operations
db.currentOp()

// Collection stats
db.logs.stats()

// Profiler (slow queries)
db.setProfilingLevel(1, { slowms: 100 })
db.system.profile.find().limit(10).sort({ ts: -1 })

Database Maintenance ​

PostgreSQL Maintenance ​

sql
-- Analyze tables (update statistics)
ANALYZE;

-- Vacuum (reclaim space)
VACUUM;

-- Full vacuum (more thorough, locks tables)
VACUUM FULL;

-- Reindex
REINDEX DATABASE ante_db;

Regular Maintenance Script ​

Create ~/ante-erp/scripts/db-maintenance.sh:

bash
#!/bin/bash

echo "Starting database maintenance..."

# PostgreSQL maintenance
docker compose exec -T postgres psql -U ante -d ante_db <<EOF
ANALYZE;
VACUUM;
REINDEX DATABASE ante_db;
EOF

# Redis optimization
docker compose exec redis redis-cli -a "$REDIS_PASSWORD" BGREWRITEAOF

# MongoDB compact (optional, locks database)
# docker compose exec mongodb mongosh --username ante --password "$MONGO_PASSWORD" --authenticationDatabase admin --eval "db.runCommand({ compact: 'collection_name' })"

echo "Database maintenance completed"

Schedule weekly:

bash
0 3 * * 0 ~/ante-erp/scripts/db-maintenance.sh >> ~/ante-erp/logs/maintenance.log 2>&1

Migrating to External Databases ​

For larger deployments, consider managed database services:

Using External PostgreSQL ​

bash
# In .env file
DATABASE_URL=postgresql://user:pass@external-db.example.com:5432/ante_db?ssl=true
DIRECT_URL=postgresql://user:pass@external-db.example.com:5432/ante_db?ssl=true

# Remove postgres service from docker-compose.yml

Using External Redis ​

bash
# In .env file
REDIS_HOST=external-redis.example.com
REDIS_PORT=6379
REDIS_PASSWORD=your_password
REDIS_TLS=true

# Remove redis service from docker-compose.yml

Using MongoDB Atlas ​

bash
# In .env file
MONGODB_URI=mongodb+srv://user:pass@cluster.mongodb.net/ante?retryWrites=true&w=majority

# Remove mongodb service from docker-compose.yml

Troubleshooting ​

PostgreSQL Issues ​

Connection refused:

bash
# Check service is running
docker compose ps postgres

# Check logs
docker compose logs postgres

# Restart service
docker compose restart postgres

Out of connections:

sql
-- Check current connections
SELECT count(*) FROM pg_stat_activity;

-- Increase max_connections in docker-compose.yml

Redis Issues ​

Authentication failed:

bash
# Verify password in .env
cat .env | grep REDIS_PASSWORD

# Test connection
docker compose exec redis redis-cli -a "$REDIS_PASSWORD" PING

Out of memory:

bash
# Check memory usage
docker compose exec redis redis-cli -a "$REDIS_PASSWORD" INFO memory

# Increase maxmemory in docker-compose.yml

MongoDB Issues ​

Authentication failed:

bash
# Verify credentials
docker compose exec mongodb mongosh \
  --username ante \
  --password "$MONGO_PASSWORD" \
  --authenticationDatabase admin

Storage space:

bash
# Check database size
docker compose exec mongodb mongosh \
  --username ante \
  --password "$MONGO_PASSWORD" \
  --authenticationDatabase admin \
  --eval "db.stats()"

Next Steps ​

Last Updated: October 27, 2025

Released under the MIT License.

Copyright © 2024 GEER ANTE