> ## Documentation Index
> Fetch the complete documentation index at: https://docs.deploystack.io/llms.txt
> Use this file to discover all available pages before exploring further.

# Database Setup for Self-Hosting

> Step-by-step guide to configure PostgreSQL for your self-hosted DeployStack instance.

## Overview

DeployStack uses PostgreSQL as its database backend, providing enterprise-grade reliability with ACID compliance, connection pooling, and advanced features for production deployments.

**Important**: PostgreSQL must be running and accessible before starting your DeployStack instance.

## What You'll Need

* PostgreSQL 13+ installed and running (or included in Docker Compose)
* Database connection details (host, port, username, password)
* About 5-10 minutes to complete the configuration

## Deployment Options

### Option 1: Docker Compose (Recommended)

If you're using our Docker Compose setup, PostgreSQL is included and automatically configured. No manual database setup required!

```bash theme={null}
# PostgreSQL is automatically included
docker-compose up -d
```

The Docker Compose setup includes:

* PostgreSQL 18 Alpine
* Automatic health checks
* Persistent data volume
* Pre-configured connection details

### Option 2: External PostgreSQL Server

For production deployments with existing PostgreSQL infrastructure:

## Step 1: Prepare PostgreSQL Database

### Create Database and User

Connect to your PostgreSQL server and create a dedicated database and user:

```sql theme={null}
-- Connect to PostgreSQL as admin
psql -U postgres

-- Create database
CREATE DATABASE deploystack;

-- Create user with password
CREATE USER deploystack_user WITH ENCRYPTED PASSWORD 'your_secure_password_here';

-- Grant privileges
GRANT ALL PRIVILEGES ON DATABASE deploystack TO deploystack_user;

-- Grant schema privileges (PostgreSQL 15+)
\c deploystack
GRANT ALL ON SCHEMA public TO deploystack_user;
GRANT ALL ON ALL TABLES IN SCHEMA public TO deploystack_user;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO deploystack_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO deploystack_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO deploystack_user;

-- Exit
\q
```

### Verify Connection

Test the database connection:

```bash theme={null}
# Test connection
psql -h localhost -U deploystack_user -d deploystack -c "SELECT version();"

# You should see PostgreSQL version information
```

## Step 2: Configure Environment Variables

Set PostgreSQL connection details in your environment:

### For Docker Deployments

Add to your `.env` file:

```bash theme={null}
# PostgreSQL Configuration
POSTGRES_HOST=your-postgres-host     # e.g., localhost or postgres.example.com
POSTGRES_PORT=5432                   # Default PostgreSQL port
POSTGRES_DATABASE=deploystack        # Database name
POSTGRES_USER=deploystack_user       # Database user
POSTGRES_PASSWORD=your_secure_password_here
POSTGRES_SSL=false                   # Set to 'true' for SSL connections
```

### For Local Development

Edit `services/backend/.env`:

```bash theme={null}
# PostgreSQL Configuration
POSTGRES_HOST=localhost
POSTGRES_PORT=5432
POSTGRES_DATABASE=deploystack
POSTGRES_USER=deploystack
POSTGRES_PASSWORD=deploystack
POSTGRES_SSL=false
```

## Step 3: Start DeployStack

Once PostgreSQL is configured, start your DeployStack instance:

### Docker Compose

```bash theme={null}
docker-compose up -d
```

### Individual Containers

```bash theme={null}
# Start backend with PostgreSQL configuration
docker run -d \
  --name deploystack-backend \
  -p 3000:3000 \
  -e POSTGRES_HOST=your-postgres-host \
  -e POSTGRES_PORT=5432 \
  -e POSTGRES_DATABASE=deploystack \
  -e POSTGRES_USER=deploystack_user \
  -e POSTGRES_PASSWORD=your_secure_password_here \
  -e POSTGRES_SSL=false \
  -e DEPLOYSTACK_ENCRYPTION_SECRET=your-secret-here \
  -v deploystack_backend_persistent:/app/persistent_data \
  deploystack/backend:latest
```

## Step 4: Complete Setup Wizard

1. **Access DeployStack**: Navigate to your frontend URL (e.g., `http://localhost:8080`)
2. **Automatic Redirect**: You'll be redirected to `/setup`
3. **Database Initialization**: The wizard will:
   * Test PostgreSQL connection
   * Apply database migrations
   * Create necessary tables
   * Initialize system data
4. **Create Admin Account**: Set up your administrator account
5. **Configuration**: Complete basic platform settings

## SSL/TLS Connection

For secure connections to PostgreSQL:

### Enable SSL in PostgreSQL

1. **Configure PostgreSQL** (`postgresql.conf`):
   ```conf theme={null}
   ssl = on
   ssl_cert_file = '/path/to/server.crt'
   ssl_key_file = '/path/to/server.key'
   ssl_ca_file = '/path/to/root.crt'
   ```

2. **Set Environment Variable**:
   ```bash theme={null}
   POSTGRES_SSL=true
   ```

3. **Restart PostgreSQL** and DeployStack backend

## Production Considerations

### Connection Pooling

DeployStack uses `node-postgres` with connection pooling:

* Default max connections: 20
* Idle timeout: 30 seconds
* Connection timeout: 2 seconds

### Database Maintenance

```bash theme={null}
# Vacuum database (reclaim storage)
psql -U deploystack_user -d deploystack -c "VACUUM ANALYZE;"

# Check database size
psql -U deploystack_user -d deploystack -c "SELECT pg_size_pretty(pg_database_size('deploystack'));"

# View active connections
psql -U deploystack_user -d deploystack -c "SELECT count(*) FROM pg_stat_activity WHERE datname = 'deploystack';"
```

### Backup Strategy

```bash theme={null}
# Create backup
pg_dump -h localhost -U deploystack_user deploystack > backup.sql

# Compressed backup
pg_dump -h localhost -U deploystack_user deploystack | gzip > backup.sql.gz

# Custom format (supports parallel restore)
pg_dump -h localhost -U deploystack_user -Fc deploystack > backup.dump

# Restore from backup
psql -h localhost -U deploystack_user deploystack < backup.sql

# Restore from custom format
pg_restore -h localhost -U deploystack_user -d deploystack backup.dump
```

### Performance Tuning

Edit PostgreSQL configuration (`postgresql.conf`):

```conf theme={null}
# Memory settings
shared_buffers = 256MB              # 25% of RAM
effective_cache_size = 1GB          # 50-75% of RAM
maintenance_work_mem = 64MB
work_mem = 16MB

# Connections
max_connections = 100

# Write-ahead log
wal_buffers = 16MB
checkpoint_completion_target = 0.9

# Query planner
random_page_cost = 1.1              # For SSD storage
effective_io_concurrency = 200      # For SSD storage
```

## Troubleshooting

### "Connection refused" or "Cannot connect"

**Solutions**:

1. **Check PostgreSQL is running**:
   ```bash theme={null}
   # For system service
   sudo systemctl status postgresql

   # For Docker
   docker ps | grep postgres
   ```

2. **Check PostgreSQL is listening**:
   ```bash theme={null}
   netstat -an | grep 5432
   ```

3. **Check PostgreSQL configuration** (`postgresql.conf`):
   ```conf theme={null}
   listen_addresses = '*'              # Or specific IP
   ```

4. **Check firewall rules**:
   ```bash theme={null}
   # Allow PostgreSQL port
   sudo ufw allow 5432
   ```

### "Authentication failed"

**Solutions**:

1. **Verify credentials**: Double-check username and password
2. **Check pg\_hba.conf**:
   ```conf theme={null}
   # Allow password authentication
   host    all             all             0.0.0.0/0               md5
   ```
3. **Reload PostgreSQL** after config changes:
   ```bash theme={null}
   sudo systemctl reload postgresql
   ```

### "Database does not exist"

**Solutions**:

1. **Create database** as shown in Step 1
2. **Check database name** matches environment variable
3. **Verify user has access**:
   ```sql theme={null}
   \l    -- List all databases
   ```

### "Permission denied"

**Solutions**:

1. **Grant proper privileges** as shown in Step 1
2. **Check user permissions**:
   ```sql theme={null}
   \du   -- List user permissions
   ```

### Migration Errors

**Solutions**:

1. **Check PostgreSQL version**: DeployStack requires PostgreSQL 13+
2. **Verify user privileges**: User needs CREATE, ALTER, DROP permissions
3. **Check logs**: Review backend logs for detailed error messages
4. **Manual migration reset** (development only):
   ```sql theme={null}
   -- Connect to database
   psql -U deploystack_user -d deploystack

   -- Drop all tables
   DROP SCHEMA public CASCADE;
   CREATE SCHEMA public;
   GRANT ALL ON SCHEMA public TO deploystack_user;

   -- Restart backend to re-apply migrations
   ```

## Monitoring

### Check Database Health

```sql theme={null}
-- Check active connections
SELECT count(*) FROM pg_stat_activity WHERE datname = 'deploystack';

-- Check table sizes
SELECT schemaname, tablename, pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename))
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

-- Check index usage
SELECT schemaname, tablename, indexname, idx_scan as scans
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

-- Check slow queries
SELECT pid, now() - query_start as duration, query
FROM pg_stat_activity
WHERE state = 'active' AND now() - query_start > interval '1 second'
ORDER BY duration DESC;
```

## Security Notes

* **Use strong passwords** for database users
* **Enable SSL/TLS** for production deployments
* **Restrict network access** using pg\_hba.conf
* **Regular backups** are essential for data protection
* **Rotate passwords** periodically
* **Monitor access logs** for suspicious activity

## Next Steps

After successful database setup:

1. **Complete Setup Wizard** - Create your admin account
2. **Configure Global Settings** - Set up email, authentication, etc.
3. **Deploy Satellites** - Set up MCP server management infrastructure
4. **Create Teams** - Invite team members and set up workspaces

Your DeployStack instance is now ready to use!