How to Perform Database Migration from SQLite to PostgreSQL for AI-Trader

You can migrate AI-Trader's data from the default SQLite file to PostgreSQL using the built-in script service/server/scripts/migrate_sqlite_to_postgres.py, which handles schema creation, bulk data transfer, and sequence reset in a single idempotent operation.

AI-Trader (HKUDS/AI-Trader) stores all persistent data in service/server/data/clawtrader.db by default. For production deployments requiring concurrent access and robustness, migrating this database to PostgreSQL is essential. The repository provides a purpose-built migration utility that executes safely and can be rerun without risking duplicate data.

Prerequisites and Dependencies

Before executing the database migration from SQLite to PostgreSQL for AI-Trader, verify that your environment meets the following requirements:

• PostgreSQL driver: Install psycopg (the modern PostgreSQL driver) via the service requirements or directly:

pip install -r service/requirements.txt

# Or individually:

pip install psycopg[binary]

• Target database: Ensure the PostgreSQL database exists and is accessible. The migration script will create tables but requires an existing database connection.

• Environment configuration: Create a .env file in the project root containing your PostgreSQL connection string.

# .env

DATABASE_URL=postgresql://user:password@db-host:5432/ai_trader

Understanding the Migration Architecture

The migration relies on three core modules that coordinate the transfer:

• service/server/scripts/migrate_sqlite_to_postgres.py: The main executable that orchestrates the entire process, including connection handling, table truncation, data streaming, and sequence reset.
• service/server/database.py: Contains the init_database() function that defines the PostgreSQL schema structure, ensuring the target database mirrors the SQLite source exactly.
• service/server/config.py: Loads the DATABASE_URL environment variable from your .env file, providing the connection credentials to the migration script.

Step-by-Step Migration Process

The script implements an eight-stage pipeline designed to handle foreign keys, timestamps, and bulk data efficiently:

1. Configuration Loading

The script reads DATABASE_URL from service/server/config.py , which parses the project root .env file. Alternatively, pass the URL via the --target CLI flag to override the environment variable.

2. Schema Initialization

Before data transfer begins, the script imports init_database from service/server/database.py and executes it against the PostgreSQL connection. This creates all tables (such as agents, agent_messages, and others) with proper constraints and indexes, ensuring structural parity with the SQLite source.

3. Ordered Table Processing

A hard-coded TABLE_ORDER list within the migration script defines the precise sequence for data copying. This order respects foreign-key dependencies—for example, copying the agents table before agent_messages to prevent constraint violations during insertion.

4. Target Table Truncation

To guarantee an idempotent migration, the script executes TRUNCATE ... RESTART IDENTITY CASCADE on every target table. This removes existing data and resets auto-increment counters, providing a clean slate for the incoming data.

5. Timestamp Normalization

The script automatically detects timestamp columns via an internal TIMESTAMP_COLUMNS mapping. Each timestamp value passes through a normalize_timestamp function that converts local or ambiguous formats into ISO-8601 UTC strings, ensuring temporal consistency between the two database engines.

6. Bulk Data Streaming

For each table, the migration selects all rows from the SQLite source, then streams them into PostgreSQL using the COPY ... FROM STDIN protocol via the copy_table function. This approach avoids loading entire tables into memory and leverages PostgreSQL's optimized bulk-loading pathway for high-performance transfer.

7. Sequence Reset

After all rows are inserted, the reset_sequences function queries the maximum id value for each table and calls setval on the associated PostgreSQL serial sequences. This synchronizes the sequence counters so that subsequent application inserts continue from the correct primary key value without collision.

8. Connection Cleanup

Finally, the script closes both the SQLite and PostgreSQL connections and prints a completion message. The entire operation is wrapped in transaction logic where possible, ensuring data integrity.

Configuration Options

You can customize the migration behavior through environment variables or command-line arguments.

Using .env file (recommended):

DATABASE_URL=postgresql://user:password@db-host:5432/ai_trader

Using CLI flags:

python service/server/scripts/migrate_sqlite_to_postgres.py \
    --source /path/to/custom/clawtrader.db \
    --target postgresql://user:pwd@host:5432/ai_trader

If both are provided, CLI flags take precedence over the environment variable.

Running the Migration

Execute the migration from the repository root after configuring your environment:

python service/server/scripts/migrate_sqlite_to_postgres.py

For custom source paths or direct connection strings without a .env file:

python service/server/scripts/migrate_sqlite_to_postgres.py \
    --source service/server/data/clawtrader.db \
    --target postgresql://trader:securepass@localhost:5432/ai_trader

Post-Migration Verification

Confirm the migration succeeded by querying the PostgreSQL database using AI-Trader's connection utilities:

from service.server.database import get_db_connection

with get_db_connection() as conn:
    cur = conn.cursor()
    cur.execute("SELECT COUNT(*) AS cnt FROM agents")
    result = cur.fetchone()
    print(f"Agents migrated to PostgreSQL: {result['cnt']}")

You should see counts matching your original SQLite database. Check additional tables like agent_messages and any custom tables defined in your schema to ensure complete data integrity.

Handling Schema Changes

If you modify the SQLite schema (adding columns or tables), update the init_database() function in service/server/database.py accordingly before rerunning the migration. The TRUNCATE ... CASCADE operation will automatically clear old data, while the updated schema initialization will create the new structure. Because the migration is idempotent, you can safely re-run the script after fixing schema definitions without creating duplicate rows.

Summary

• Use the built-in script: service/server/scripts/migrate_sqlite_to_postgres.py handles the complete database migration from SQLite to PostgreSQL for AI-Trader.
• Install prerequisites: Ensure psycopg is installed and DATABASE_URL is configured in your .env file or passed via --target.
• Leverage bulk operations: The script uses COPY ... FROM STDIN for efficient data transfer and automatically normalizes timestamps and resets sequences.
• Verify before deploying: Use get_db_connection() from service/server/database.py to validate row counts and data integrity after migration.
• Re-run safely: The idempotent design with table truncation allows repeated execution during development or schema iteration.

Frequently Asked Questions

What happens if the migration is interrupted halfway through?

You can safely rerun the script. The migration uses TRUNCATE ... RESTART IDENTITY CASCADE at the start to empty target tables, ensuring no duplicate primary keys exist. Since the PostgreSQL schema is recreated via init_database() and sequences are reset after data insertion, an interrupted migration can be restarted without manual cleanup.

Does the migration script preserve foreign key relationships?

Yes. The TABLE_ORDER list in service/server/scripts/migrate_sqlite_to_postgres.py explicitly sequences table copying to respect foreign key dependencies. Parent tables like agents are processed before child tables like agent_messages, preventing constraint violations during the bulk copy phase.

Can I migrate only specific tables instead of the entire database?

The current script is designed for full database migration. It iterates through the complete TABLE_ORDER list and truncates all tables before copying. To migrate specific tables, you would need to modify the script's TABLE_ORDER definition or temporarily rename unwanted tables in your source SQLite file before running the migration.

Why are my timestamps showing incorrect values after migration?

The script's normalize_timestamp function attempts to convert timestamps to ISO-8601 UTC, but timezone handling depends on how values were stored in SQLite. If your SQLite database stored timestamps in local time without timezone offsets, verify the TIMESTAMP_COLUMNS mapping in the migration script includes all relevant columns, and consider standardizing your SQLite data to UTC before migration.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}