atlas/docs/POSTGRESQL_MIGRATION.md

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:

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:

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

Or use the connection string format:

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:

sudo apt-get update
sudo apt-get install postgresql postgresql-contrib

CentOS/RHEL:

sudo yum install postgresql-server postgresql-contrib
sudo postgresql-setup initdb
sudo systemctl start postgresql
sudo systemctl enable postgresql

2. Create Database and User

# 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):

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

Or update /etc/atlas/atlas.conf:

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

4. Restart Service

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:

   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:

   psql -U atlas_user -d atlas < converted_backup.sql
   

Rebuilding the Application

1. Install PostgreSQL Development Libraries

Ubuntu/Debian:

sudo apt-get install libpq-dev

CentOS/RHEL:

sudo yum install postgresql-devel

2. Update Dependencies

go mod tidy

3. Build

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

Or use the installer:

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.