Postgres on a $6 VPS: The Setup That Runs Two Live Trading Bots
TL;DR / Key Takeaways
- Peer authentication is fine for local dev, but you need password auth for a bot running as a service user on a VPS.
- Two separate Postgres databases, one per bot, with explicit users and schemas keeps everything clean and auditable.
pg_dumpcron job takes 10 minutes to set up and has saved me twice.- The Predict & Profit Weather Bot and Econ Bot ship with SQLite for buyers, but the production versions run Postgres with the schemas documented below.
I run two live Kalshi trading bots on a $6 RackNerd VPS. 1 vCPU, 1.5 GB RAM, 30 GB SSD. Ubuntu 24. The bots make real trades with real money. Postgres is on the same machine.
This is not the setup a DBA would recommend. It works anyway.
Here is exactly how I set it up, why I made the choices I made, and the actual schemas the bots use.
Why Postgres and Not SQLite
The bots ship to customers with SQLite. SQLite is fine for single-process, local use. It requires zero setup, the whole database is one file, and it works on Windows without touching anything.
Production is different. The Weather Bot and the Econ Bot run as separate systemd services. Both write trade decisions. Both need concurrent access. Both need a query history I can actually analyze without fighting SQLite's limited data types and absent time functions.
Postgres handles concurrent writers cleanly. The timestamp with time zone type means I never have to guess whether a timestamp is UTC or local time. Window functions work. EXPLAIN ANALYZE works. When something goes wrong at 2am and I need to figure out why the bot skipped 14 trades in a row, I want real SQL.
SQLite is the right tool for a lot of things. A production trading bot is not one of them.
Installing Postgres on Ubuntu 24
Ubuntu 24 ships with Postgres 16 in the default repos. That is good enough.
sudo apt update
sudo apt install -y postgresql postgresql-contrib
sudo systemctl enable postgresql
sudo systemctl start postgresql
Verify it is running:
sudo systemctl status postgresql
You should see active (running). If not, check journalctl -xe and fix it before moving on.
Peer Auth vs Password Auth
This is the part that trips people up the first time.
Postgres uses pg_hba.conf to decide how connections are authenticated. On a fresh Ubuntu install, the default for local Unix socket connections is peer authentication. Peer auth means Postgres checks the Linux username of the process connecting. If you are the postgres Linux user, you can connect as the postgres Postgres role with no password.
That is fine for interactive admin work. It is useless for a systemd service running as a different user.
My bots run as stevefarmer (my main user). The bot connects to Postgres via TCP on localhost, not a Unix socket. For TCP connections, the default auth method is md5 (password), which is what we want.
Check your pg_hba.conf:
sudo cat /etc/postgresql/16/main/pg_hba.conf
The relevant lines should look like this:
# TYPE DATABASE USER ADDRESS METHOD
local all postgres peer
host all all 127.0.0.1/32 md5
host all all ::1/128 md5
The host lines cover TCP connections from localhost over IPv4 and IPv6. md5 means password required. That is correct for the bots.
If you changed something and need to reload:
sudo systemctl reload postgresql
Creating Users and Databases
Switch to the postgres Linux user first:
sudo -u postgres psql
Create the two databases and their dedicated users:
-- Weather bot
CREATE USER weather_bot WITH PASSWORD 'your_strong_password_here';
CREATE DATABASE kalshi_trading OWNER weather_bot;
-- Econ bot
CREATE USER econ_bot WITH PASSWORD 'your_other_strong_password_here';
CREATE DATABASE kalshi_econ_trading OWNER econ_bot;
-- Verify
\l
\q
Each bot gets its own Postgres user with access only to its own database. If something goes wrong with the Econ Bot, it cannot touch the Weather Bot's data. This is basic hygiene, not paranoia.
Test the connection from your normal user:
psql -h 127.0.0.1 -U weather_bot -d kalshi_trading
It should prompt for a password. Enter it. If you get in, the setup is correct.
The Weather Bot Schema
The kalshi_trading database has three main tables. Here is the schema:
CREATE TABLE trades (
id SERIAL PRIMARY KEY,
event_ticker TEXT NOT NULL,
market_ticker TEXT NOT NULL,
side TEXT NOT NULL CHECK (side IN ('yes', 'no')),
contracts INTEGER NOT NULL,
fill_price NUMERIC(6,2),
order_id TEXT,
status TEXT NOT NULL DEFAULT 'open'
CHECK (status IN ('open', 'closed', 'settled', 'cancelled')),
ensemble_edge NUMERIC(6,4),
ensemble_probability NUMERIC(6,4),
placed_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
closed_at TIMESTAMP WITH TIME ZONE,
settled_at TIMESTAMP WITH TIME ZONE,
pnl NUMERIC(8,2),
notes TEXT
);
CREATE TABLE trade_decisions (
id SERIAL PRIMARY KEY,
event_ticker TEXT NOT NULL,
market_ticker TEXT NOT NULL,
decision TEXT NOT NULL CHECK (decision IN ('placed', 'skipped', 'error')),
skip_reason TEXT,
ensemble_edge NUMERIC(6,4),
ensemble_probability NUMERIC(6,4),
gfs_probability NUMERIC(6,4),
aigefs_probability NUMERIC(6,4),
ecmwf_ifs_probability NUMERIC(6,4),
ecmwf_aifs_probability NUMERIC(6,4),
agreement_count INTEGER,
min_price_filter NUMERIC(6,2),
evaluated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
CREATE TABLE ensemble_runs (
id SERIAL PRIMARY KEY,
run_cycle TEXT,
source TEXT NOT NULL,
event_ticker TEXT NOT NULL,
members_used INTEGER,
mean_probability NUMERIC(6,4),
std_dev NUMERIC(6,4),
fetched_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
CREATE INDEX idx_trades_status ON trades(status);
CREATE INDEX idx_trades_event ON trades(event_ticker);
CREATE INDEX idx_decisions_evaluated ON trade_decisions(evaluated_at);
CREATE INDEX idx_decisions_market ON trade_decisions(market_ticker);
The trade_decisions table is the one I actually use for analysis. Every candidate gets logged whether the bot trades it or not. Skip reasons tell me which filter is firing most often. Per-source probabilities let me see where model disagreement killed a trade. I added 15 new insert call sites in v2.1 specifically because I was blind without this data.
The Econ Bot Schema
The kalshi_econ_trading database is similar but adds signal-tracking tables:
CREATE TABLE econ_trades (
id SERIAL PRIMARY KEY,
event_ticker TEXT NOT NULL,
market_ticker TEXT NOT NULL,
side TEXT NOT NULL CHECK (side IN ('yes', 'no')),
contracts INTEGER NOT NULL,
fill_price NUMERIC(6,2),
order_id TEXT,
status TEXT NOT NULL DEFAULT 'open'
CHECK (status IN ('open', 'closed', 'settled', 'cancelled')),
composite_probability NUMERIC(6,4),
market_price NUMERIC(6,2),
edge NUMERIC(6,4),
placed_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
closed_at TIMESTAMP WITH TIME ZONE,
settled_at TIMESTAMP WITH TIME ZONE,
pnl NUMERIC(8,2),
regime_close BOOLEAN DEFAULT FALSE,
notes TEXT
);
CREATE TABLE econ_decisions (
id SERIAL PRIMARY KEY,
event_ticker TEXT NOT NULL,
market_ticker TEXT NOT NULL,
decision TEXT NOT NULL CHECK (decision IN ('placed', 'skipped', 'error')),
skip_reason TEXT,
cleveland_fed_probability NUMERIC(6,4),
fred_nudge NUMERIC(6,4),
bls_nudge NUMERIC(6,4),
bea_nudge NUMERIC(6,4),
composite_probability NUMERIC(6,4),
market_price NUMERIC(6,2),
edge NUMERIC(6,4),
evaluated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
CREATE TABLE signal_snapshots (
id SERIAL PRIMARY KEY,
snapshot_time TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
cleveland_fed_cpi NUMERIC(6,4),
cleveland_fed_pce NUMERIC(6,4),
oil_price NUMERIC(8,2),
gas_price NUMERIC(6,3),
breakeven_10yr NUMERIC(6,4),
unemployment NUMERIC(5,2),
signals_active TEXT
);
CREATE TABLE regime_events (
id SERIAL PRIMARY KEY,
detected_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
prior_probability NUMERIC(6,4),
new_probability NUMERIC(6,4),
swing NUMERIC(6,4),
positions_closed INTEGER,
notes TEXT
);
CREATE INDEX idx_econ_trades_status ON econ_trades(status);
CREATE INDEX idx_econ_decisions_evaluated ON econ_decisions(evaluated_at);
CREATE INDEX idx_signal_snapshots_time ON signal_snapshots(snapshot_time);
The signal_snapshots table captures the raw signal values at each scan cycle. When I go back and look at a trade two weeks later, I can see exactly what gas prices and the breakeven rate were doing when the bot made that decision. That context matters.
The regime_events table logs every time the regime-change detector fires. I can see how often the Cleveland Fed nowcast moves enough to trigger a close, and whether those closes were good calls.
Connecting From Python
Both bots use psycopg2 with a simple connection pool. No ORM. Just SQL.
import psycopg2
from psycopg2 import pool
class DatabaseManager:
def __init__(self, dsn: str, min_conn: int = 1, max_conn: int = 5):
self._pool = pool.ThreadedConnectionPool(
min_conn,
max_conn,
dsn=dsn
)
def get_connection(self):
return self._pool.getconn()
def return_connection(self, conn):
self._pool.putconn(conn)
def close_all(self):
self._pool.closeall()
def execute(self, query: str, params: tuple = None):
conn = self.get_connection()
try:
with conn.cursor() as cur:
cur.execute(query, params)
conn.commit()
except Exception as e:
conn.rollback()
raise e
finally:
self.return_connection(conn)
def fetchall(self, query: str, params: tuple = None):
conn = self.get_connection()
try:
with conn.cursor() as cur:
cur.execute(query, params)
return cur.fetchall()
finally:
self.return_connection(conn)
The DSN string lives in the environment, not in the code:
import os
DB_DSN = os.environ.get("DB_DSN")
# e.g. "postgresql://weather_bot:password@127.0.0.1:5432/kalshi_trading"
db = DatabaseManager(dsn=DB_DSN)
ThreadedConnectionPool matters because the bot has multiple threads. The main trading loop, the position monitor, and the settlement checker all need DB access. Without pooling you get connection refused errors under load, or worse, silent connection leaks that eventually exhaust available connections.
Pool size of 1 to 5 is more than enough for these bots. On a $6 VPS with 1.5 GB RAM, you are not running 50 threads.
pg_dump Backups
This is 10 minutes of work that has saved me twice. Once when I fat-fingered a DROP, once when I ran an UPDATE without a WHERE clause during late-night debugging.
The backup script:
#!/bin/bash
# /home/stevefarmer/scripts/backup_postgres.sh
BACKUP_DIR="/home/stevefarmer/backups/postgres"
DATE=$(date +%Y%m%d_%H%M%S)
PGPASSWORD="your_password_here"
mkdir -p "$BACKUP_DIR"
# Weather bot
PGPASSWORD=$PGPASSWORD pg_dump \
-h 127.0.0.1 \
-U weather_bot \
-d kalshi_trading \
-F c \
-f "$BACKUP_DIR/kalshi_trading_$DATE.dump"
# Econ bot
PGPASSWORD=$PGPASSWORD pg_dump \
-h 127.0.0.1 \
-U econ_bot \
-d kalshi_econ_trading \
-F c \
-f "$BACKUP_DIR/kalshi_econ_trading_$DATE.dump"
# Keep 7 days of backups
find "$BACKUP_DIR" -name "*.dump" -mtime +7 -delete
echo "[$DATE] Backup complete."
-F c is custom format, which is compressed and supports selective restore. Better than plain SQL dumps for anything you actually care about.
Make it executable and add it to cron:
chmod +x /home/stevefarmer/scripts/backup_postgres.sh
crontab -e
Add this line:
0 3 * * * /home/stevefarmer/scripts/backup_postgres.sh >> /home/stevefarmer/logs/backup.log 2>&1
Runs at 3am every night. Keeps 7 days of dumps. If you need to restore:
pg_restore -h 127.0.0.1 -U weather_bot -d kalshi_trading -F c kalshi_trading_20260520_030001.dump
When Postgres Is Overkill
Not everything needs Postgres. The bot config, the API keys, the model weights, the threshold settings, all of that lives in environment variables and config files. I do not put it in a database.
SQLite is still what ships to buyers of the source code. Setting up Postgres is a real barrier for someone on Windows who just wants to run the bot and see if it works. SQLite gets them running in five minutes. They can migrate to Postgres later if they want.
If you are building a single-user script that runs once a day and writes 10 rows, SQLite is the right answer. If you are running two concurrent services that write continuously and need auditability, Postgres earns its place.
One More Thing
The VPS is $6 a month from RackNerd. The Postgres instance shares that machine with both bots, Nginx, and