# PostgreSQL Migration Guide

## Overview

AtlasOS now supports both SQLite and PostgreSQL databases. You can switch between them by changing the database connection string.

## Quick Start

### Using PostgreSQL

Set the `ATLAS_DB_CONN` environment variable to a PostgreSQL connection string:

```bash
export ATLAS_DB_CONN="postgres://username:password@localhost:5432/atlas?sslmode=disable"
./atlas-api
```

### Using SQLite (Default)

Set the `ATLAS_DB_PATH` environment variable to a file path:

```bash
export ATLAS_DB_PATH="/var/lib/atlas/atlas.db"
./atlas-api
```

Or use the connection string format:

```bash
export ATLAS_DB_CONN="sqlite:///var/lib/atlas/atlas.db"
./atlas-api
```

## Connection String Formats

### PostgreSQL

```
postgres://[user[:password]@][netloc][:port][/dbname][?param1=value1&...]
```

Examples:
- `postgres://user:pass@localhost:5432/atlas`
- `postgres://user:pass@localhost:5432/atlas?sslmode=disable`
- `postgresql://user:pass@db.example.com:5432/atlas?sslmode=require`

### SQLite

- File path: `/var/lib/atlas/atlas.db`
- Connection string: `sqlite:///var/lib/atlas/atlas.db`

## Setup PostgreSQL Database

### 1. Install PostgreSQL

**Ubuntu/Debian:**
```bash
sudo apt-get update
sudo apt-get install postgresql postgresql-contrib
```

**CentOS/RHEL:**
```bash
sudo yum install postgresql-server postgresql-contrib
sudo postgresql-setup initdb
sudo systemctl start postgresql
sudo systemctl enable postgresql
```

### 2. Create Database and User

```bash
# Switch to postgres user
sudo -u postgres psql

# Create database
CREATE DATABASE atlas;

# Create user
CREATE USER atlas_user WITH PASSWORD 'your_secure_password';

# Grant privileges
GRANT ALL PRIVILEGES ON DATABASE atlas TO atlas_user;

# Exit
\q
```

### 3. Configure AtlasOS

Update your systemd service file (`/etc/systemd/system/atlas-api.service`):

```ini
[Service]
Environment="ATLAS_DB_CONN=postgres://atlas_user:your_secure_password@localhost:5432/atlas?sslmode=disable"
```

Or update `/etc/atlas/atlas.conf`:

```bash
# PostgreSQL connection string
ATLAS_DB_CONN=postgres://atlas_user:your_secure_password@localhost:5432/atlas?sslmode=disable
```

### 4. Restart Service

```bash
sudo systemctl daemon-reload
sudo systemctl restart atlas-api
```

## Migration from SQLite to PostgreSQL

### Option 1: Fresh Start (Recommended for new installations)

1. Set up PostgreSQL database (see above)
2. Update connection string
3. Restart service - tables will be created automatically

### Option 2: Data Migration

If you have existing SQLite data:

1. **Export from SQLite:**
   ```bash
   sqlite3 /var/lib/atlas/atlas.db .dump > atlas_backup.sql
   ```

2. **Convert SQL to PostgreSQL format:**
   - Replace `INTEGER` with `BOOLEAN` for boolean fields
   - Replace `TEXT` with `VARCHAR(255)` or `TEXT` as appropriate
   - Update timestamp formats

3. **Import to PostgreSQL:**
   ```bash
   psql -U atlas_user -d atlas < converted_backup.sql
   ```

## Rebuilding the Application

### 1. Install PostgreSQL Development Libraries

**Ubuntu/Debian:**
```bash
sudo apt-get install libpq-dev
```

**CentOS/RHEL:**
```bash
sudo yum install postgresql-devel
```

### 2. Update Dependencies

```bash
go mod tidy
```

### 3. Build

```bash
go build -o atlas-api ./cmd/atlas-api
go build -o atlas-tui ./cmd/atlas-tui
```

Or use the installer:

```bash
sudo ./installer/install.sh
```

## Environment Variables

| Variable | Description | Example |
|----------|-------------|---------|
| `ATLAS_DB_CONN` | Database connection string (takes precedence) | `postgres://user:pass@host:5432/db` |
| `ATLAS_DB_PATH` | SQLite database path (fallback if `ATLAS_DB_CONN` not set) | `/var/lib/atlas/atlas.db` |

## Troubleshooting

### Connection Refused

- Check PostgreSQL is running: `sudo systemctl status postgresql`
- Verify connection string format
- Check firewall rules for port 5432

### Authentication Failed

- Verify username and password
- Check `pg_hba.conf` for authentication settings
- Ensure user has proper permissions

### Database Not Found

- Verify database exists: `psql -l`
- Check database name in connection string

### SSL Mode Errors

- For local connections, use `?sslmode=disable`
- For production, configure SSL properly

## Performance Considerations

### PostgreSQL Advantages

- Better concurrency (multiple writers)
- Advanced query optimization
- Better for high-traffic scenarios
- Supports replication and clustering

### SQLite Advantages

- Zero configuration
- Single file deployment
- Lower resource usage
- Perfect for small deployments

## Schema Differences

The application automatically handles schema differences:

- **SQLite**: Uses `INTEGER` for booleans, `TEXT` for strings
- **PostgreSQL**: Uses `BOOLEAN` for booleans, `VARCHAR/TEXT` for strings

The migration system creates the appropriate schema based on the database type.