crypto_trader/docs/deployment/postgresql.md

# PostgreSQL Setup

This guide covers optional PostgreSQL database configuration.

## When to Use PostgreSQL

PostgreSQL is recommended for:

- Large datasets (millions of trades)
- Multiple users
- Advanced queries
- Production deployments
- High-performance requirements

## Installation

### Install PostgreSQL

**Fedora/Bluefin**:
```bash
sudo dnf install postgresql postgresql-server
sudo postgresql-setup --initdb
sudo systemctl enable postgresql
sudo systemctl start postgresql
```

**Ubuntu/Debian**:
```bash
sudo apt-get install postgresql postgresql-contrib
sudo systemctl enable postgresql
sudo systemctl start postgresql
```

## Database Setup

### Create Database

```bash
sudo -u postgres psql
```

```sql
CREATE DATABASE crypto_trader;
CREATE USER crypto_trader_user WITH PASSWORD 'your_password';
GRANT ALL PRIVILEGES ON DATABASE crypto_trader TO crypto_trader_user;
\q
```

### Configure Connection

Update `config.yaml`:

```yaml
database:
  type: postgresql
  host: localhost
  port: 5432
  database: crypto_trader
  user: crypto_trader_user
  password: ${DB_PASSWORD}  # Use environment variable
```

### Set Environment Variable

```bash
export DB_PASSWORD='your_password'
```

Or add to `~/.bashrc`:
```bash
echo 'export DB_PASSWORD="your_password"' >> ~/.bashrc
```

## Migration from SQLite

### Export from SQLite

```bash
sqlite3 trading.db .dump > dump.sql
```

### Import to PostgreSQL

```bash
psql -U crypto_trader_user -d crypto_trader -f dump.sql
```

## Performance Tuning

### PostgreSQL Configuration

Edit `/etc/postgresql/*/main/postgresql.conf`:

```ini
shared_buffers = 256MB
effective_cache_size = 1GB
maintenance_work_mem = 64MB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 4MB
min_wal_size = 1GB
max_wal_size = 4GB
```

### Indexes

Key indexes are created automatically. For custom queries, add indexes:

```sql
CREATE INDEX idx_trades_symbol_date ON trades(symbol, executed_at);
CREATE INDEX idx_market_data_symbol_timeframe ON market_data(symbol, timeframe, timestamp);
```

## Backup and Recovery

### Backup

```bash
pg_dump -U crypto_trader_user crypto_trader > backup.sql
```

### Restore

```bash
psql -U crypto_trader_user crypto_trader < backup.sql
```

### Automated Backups

Set up cron job:

```bash
0 2 * * * pg_dump -U crypto_trader_user crypto_trader > /backup/crypto_trader_$(date +\%Y\%m\%d).sql
```

## Security

### Connection Security

- Use strong passwords
- Restrict network access
- Use SSL connections for remote access
- Regular security updates

### User Permissions

- Use dedicated database user
- Grant only necessary permissions
- Don't use superuser for application

## Troubleshooting

**Connection refused?**
- Check PostgreSQL is running: `sudo systemctl status postgresql`
- Verify connection settings
- Check firewall rules

**Authentication failed?**
- Verify username and password
- Check `pg_hba.conf` configuration
- Review PostgreSQL logs

**Performance issues?**
- Check PostgreSQL configuration
- Review query performance
- Add appropriate indexes
- Monitor resource usage