Migrate to PostgreSQL

BizNode ships with SQLite because it works out of the box with zero configuration. For most single-operator deployments it is the right choice. Switch to PostgreSQL when you hit these conditions:

Complete every item before starting the migration.

pip install psycopg2-binary

docker compose stop bot dashboard

Connect to your PostgreSQL server and create the database and user. Open a terminal and run:

Using psql (command line)

# Connect as the postgres superuser
psql -U postgres

-- Inside psql:
CREATE DATABASE biznode;
CREATE USER biznode_user WITH PASSWORD 'choose_a_strong_password';
GRANT ALL PRIVILEGES ON DATABASE biznode TO biznode_user;

-- PostgreSQL 15+ also requires this:
\c biznode
GRANT ALL ON SCHEMA public TO biznode_user;

\q

Using pgAdmin (GUI)

1. Open pgAdmin → right-click Databases → Create → Database → name it biznode
2. Right-click Login Roles → Create → Login/Group Role → name it biznode_user, set a password
3. Right-click the biznode database → Properties → Security → add biznode_user with ALL privileges

Using a managed service (Supabase / Neon / Railway)

Create a new project. Copy the connection details — host, port, database name, user, password — you will use them in Step 5.

Always take a full backup before any migration. Two options:

Option A — Dashboard Backup (recommended)

1. Open http://localhost:7777
2. Go to Backup section in the left navigation
3. Click Run Backup Now on your Local or cloud provider
4. Wait for the green confirmation — backup is encrypted and stored in backups/

Option B — Manual file copy

# Windows PowerShell — from your BizNode folder
Copy-Item memory\biznode.db memory\biznode.db.pre-migration-backup

# Or copy to an external location
Copy-Item memory\biznode.db "C:\Backups\biznode-$(Get-Date -f yyyyMMdd-HHmmss).db"

BizNode includes scripts/migrate_to_postgresql.py — it reads every table from SQLite and inserts the data into PostgreSQL. It is safe to run multiple times (uses ON CONFLICT DO NOTHING).

Step 5a — Dry run first (preview only)

cd "C:\path\to\your\BizNode"
python scripts/migrate_to_postgresql.py --dry-run

Expected output — shows what will be migrated without writing anything:

Step 5b — Run the actual migration

python scripts/migrate_to_postgresql.py ^
  --pg-host localhost ^
  --pg-port 5432 ^
  --pg-db   biznode ^
  --pg-user biznode_user ^
  --pg-password your_password

Expected output:

set DB_TYPE=postgresql
set DB_HOST=localhost
set DB_NAME=biznode
set DB_USER=biznode_user
set DB_PASSWORD=your_password
python scripts/migrate_to_postgresql.py

Tell BizNode to connect to PostgreSQL instead of SQLite through the Setup section.

DB_TYPE     = postgresql
DB_HOST     = localhost          (or your VPS IP / managed service hostname)
DB_PORT     = 5432
DB_NAME     = biznode
DB_USER     = biznode_user
DB_PASSWORD = your_password

Restart all containers

cd "C:\path\to\your\BizNode"
docker compose up -d --force-recreate

The --force-recreate flag ensures containers re-read the updated .env values.

Check container logs for errors

docker compose logs --tail=40 dashboard
docker compose logs --tail=40 bot

Look for a clean startup. If you see psycopg2.OperationalError the credentials in .env don't match PostgreSQL. Double-check host, port, user, and password.

Verify from command line

python -c "
import os
os.environ['DB_TYPE']     = 'postgresql'
os.environ['DB_HOST']     = 'localhost'
os.environ['DB_NAME']     = 'biznode'
os.environ['DB_USER']     = 'biznode_user'
os.environ['DB_PASSWORD'] = 'your_password'
from memory.db_adapter import get_connection
conn = get_connection()
cur = conn.cursor()
cur.execute('SELECT COUNT(*) as n FROM agent_identity')
print('agent_identity rows:', cur.fetchone())
conn.close()
print('PostgreSQL connection: OK')
"

Send a test message to the bot

Open Telegram and send /start to your bot. A response confirms the bot is connected to PostgreSQL and running correctly.

Check row counts match

python scripts/migrate_to_postgresql.py --dry-run

Run the dry-run again — it now reads from PostgreSQL (since DB_TYPE=postgresql is set) and will show current row counts for confirmation.

If you want PostgreSQL to run as a Docker container alongside BizNode instead of a separate installation, add this service to your docker-compose.yml:

  postgres:
    image: postgres:16-alpine
    environment:
      POSTGRES_DB: biznode
      POSTGRES_USER: biznode_user
      POSTGRES_PASSWORD: your_password
    volumes:
      - ./memory/postgres:/var/lib/postgresql/data
    ports:
      - "5432:5432"
    restart: unless-stopped

Then add postgres to the depends_on list of the bot and dashboard services:

  dashboard:
    depends_on:
      - qdrant
      - ollama
      - postgres     # ← add this

  bot:
    depends_on:
      - qdrant
      - ollama
      - postgres     # ← add this

DB_HOST=postgres

Then start everything:

docker compose up -d

PostgreSQL data is persisted in memory/postgres/ which is mounted as a volume — it survives container restarts and upgrades.

If anything goes wrong, reverting to SQLite takes under a minute.

docker compose up -d --force-recreate