227 lines
4.8 KiB
Markdown
227 lines
4.8 KiB
Markdown
# 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.
|