7d702e9cbd
- Add db.py PostgreSQL abstraction layer (connection, upsert, table mgmt) - Replace sqlite3 with psycopg2 in: live_candle_fetcher, resampler, data_fetcher, fetch_history, import_csv, indicators, base_strategy - Sanitize table names (colons -> underscores) for PostgreSQL compat - Replace INSERT OR REPLACE with ON CONFLICT upserts - Replace pandas to_sql() with batch upsert_candles() - Add scripts: resampler_loop, gap_detector, backup_runner, cron_scheduler - Add migrate_sqlite_to_pg.py for one-time data migration - Add Dockerfile, docker-compose.yml, supervisord.conf - Add postgres/postgresql.conf tuned for 4GB RAM (Synology DS1513+) - Add .dockerignore, .env.docker.example, secrets template - Update requirements.txt (psycopg2-binary), .gitignore - Add MIGRATION_PLAN.md with full plan and todo list
127 lines
6.3 KiB
Markdown
127 lines
6.3 KiB
Markdown
# Migration Plan: SQLite → PostgreSQL + Docker on Synology DS1513+
|
|
|
|
## Architecture Decisions
|
|
|
|
| Decision | Choice | Rationale |
|
|
|----------|--------|-----------|
|
|
| Schema | Keep table-per-coin-timeframe (652 tables) | Minimal code changes, PostgreSQL handles it well |
|
|
| Table names | Sanitize `:` → `_` (e.g., `xyz_BRENTOIL_1m`) | PostgreSQL compatibility |
|
|
| Secrets | Docker env_file + bind-mount | Secure, rotate-friendly, Synology-compatible |
|
|
| Gap detection | New `gap_detector.py` | Fills data gaps when system is down |
|
|
| Backup | Daily `pg_dump` to shared folder | Accessible via File Station, Hyper Backup compatible |
|
|
| Host integration | Expose PostgreSQL port 5432 | Host scripts connect to `localhost:5432` |
|
|
| Migration | Two-phase (offline + cutover) | Minimizes downtime |
|
|
| Legacy tables | Skip `market_cap`, `candles`, `daily` | Not used by current code |
|
|
|
|
## Container Layout
|
|
|
|
```
|
|
┌─────────────────────────────────────────────────────┐
|
|
│ Docker Compose │
|
|
├─────────────────────────────────────────────────────┤
|
|
│ ┌──────────────┐ ┌──────────────────────────────┐ │
|
|
│ │ PostgreSQL │ │ Data Collector (supervisord)│ │
|
|
│ │ postgres:15- │ │ python:3.11-slim │ │
|
|
│ │ alpine │ │ │ │
|
|
│ │ │ │ • live_candle_fetcher (cont)│ │
|
|
│ │ shared_buff │ │ • resampler_loop (cont) │ │
|
|
│ │ =128MB │ │ • indicators_fetcher (cont) │ │
|
|
│ │ │ │ • cron_scheduler (cont) │ │
|
|
│ │ Vol:pg_data │ │ - data_fetcher (daily) │ │
|
|
│ │ Port:5432 │ │ - fetch_history (daily) │ │
|
|
│ │ exposed │ │ - gap_detector (hourly) │ │
|
|
│ └──────────────┘ │ - backup_runner (daily) │ │
|
|
│ └──────────────────────────────┘ │
|
|
└─────────────────────────────────────────────────────┘
|
|
|
|
Host Machine: indicators.py, base_strategy.py, main_app.py
|
|
→ connect to localhost:5432
|
|
```
|
|
|
|
## PostgreSQL Configuration (4GB RAM)
|
|
|
|
```ini
|
|
shared_buffers = 128MB
|
|
effective_cache_size = 512MB
|
|
work_mem = 8MB
|
|
maintenance_work_mem = 64MB
|
|
max_connections = 10
|
|
max_worker_processes = 2
|
|
checkpoint_completion_target = 0.9
|
|
wal_buffers = 4MB
|
|
```
|
|
|
|
## Data Migration (Two-Phase)
|
|
|
|
**Phase 1 (offline)**: Stop current system → run `migrate_sqlite_to_pg.py` → 2-3 hours for 1.8GB
|
|
|
|
**Phase 2 (cutover)**: Start Docker containers → update host scripts to connect to `localhost:5432`
|
|
|
|
## Files to Create/Modify
|
|
|
|
### New Files
|
|
1. `db.py` — PostgreSQL abstraction layer
|
|
2. `scripts/resampler_loop.py` — Runs resampler every minute in a loop
|
|
3. `scripts/gap_detector.py` — Detects and fills data gaps
|
|
4. `scripts/backup_runner.py` — Daily pg_dump with 7-day retention
|
|
5. `scripts/cron_scheduler.py` — Schedules data_fetcher, fetch_history, gap_detector, backup
|
|
6. `migrate_sqlite_to_pg.py` — One-time data migration
|
|
7. `Dockerfile` — Python 3.11-slim + supervisor + psycopg2-binary
|
|
8. `docker-compose.yml` — PostgreSQL + data-collector services
|
|
9. `supervisord.conf` — Process management
|
|
10. `postgres/postgresql.conf` — Tuned for 4GB RAM
|
|
11. `.dockerignore` — Docker build context exclusions
|
|
12. `.env.docker.example` — Docker env template
|
|
13. `secrets/pg_password.txt.example` — PG password template
|
|
|
|
### Files to Modify (7)
|
|
1. `live_candle_fetcher.py` — `sqlite3` → `db.py`
|
|
2. `resampler.py` — `sqlite3` → `db.py`
|
|
3. `data_fetcher.py` — `sqlite3` → `db.py`
|
|
4. `fetch_history.py` — `sqlite3` → `db.py`
|
|
5. `import_csv.py` — `sqlite3` → `db.py`
|
|
6. `indicators.py` — `sqlite3` → `psycopg2`
|
|
7. `base_strategy.py` — `sqlite3` → `psycopg2`
|
|
|
|
## TODO List
|
|
|
|
### Phase 1: DB Abstraction Layer
|
|
- [x] Create `db.py` with PostgreSQL connection, table sanitization, upsert logic
|
|
- [x] Add `psycopg2-binary` to `requirements.txt`
|
|
|
|
### Phase 2: Modify Data Collection Components
|
|
- [ ] Modify `live_candle_fetcher.py` — replace `sqlite3.connect()` with `db.get_connection()`, `INSERT OR REPLACE` with `db.upsert_candles()`, sanitize table names
|
|
- [ ] Modify `resampler.py` — replace `sqlite3` with `db.py`, `INSERT OR REPLACE` with `db.upsert_candles()`, `?` → `%s`
|
|
- [ ] Modify `data_fetcher.py` — replace `sqlite3` with `db.py`, `to_sql()` → `db.upsert_candles()`
|
|
- [ ] Modify `fetch_history.py` — replace `sqlite3` with `db.py`
|
|
- [ ] Modify `import_csv.py` — replace `sqlite3` with `db.py`, `to_sql()` → `db.upsert_candles()`
|
|
|
|
### Phase 3: New Components
|
|
- [ ] Create `scripts/resampler_loop.py` — wraps resampler in a while loop with 60s sleep
|
|
- [ ] Create `scripts/gap_detector.py` — detects gaps in 1m data, backfills via HTTP API
|
|
- [ ] Create `scripts/backup_runner.py` — daily pg_dump with 7-day retention
|
|
- [ ] Create `scripts/cron_scheduler.py` — schedules data_fetcher, fetch_history, gap_detector, backup
|
|
|
|
### Phase 4: Docker Setup
|
|
- [ ] Create `Dockerfile` (python:3.11-slim + supervisor + psycopg2-binary)
|
|
- [ ] Create `docker-compose.yml` (postgres + data-collector services)
|
|
- [ ] Create `supervisord.conf` (live_candle_fetcher, resampler_loop, indicators_fetcher, cron_scheduler)
|
|
- [ ] Create `postgres/postgresql.conf` (tuned for 4GB RAM)
|
|
- [ ] Create `.dockerignore`
|
|
- [ ] Create `.env.docker.example`
|
|
- [ ] Create `secrets/pg_password.txt.example`
|
|
- [ ] Update `.gitignore`
|
|
|
|
### Phase 5: Host-Side Updates
|
|
- [ ] Modify `indicators.py` on host — connect to `localhost:5432`
|
|
- [ ] Modify `base_strategy.py` on host — connect to `localhost:5432`
|
|
|
|
### Phase 6: Migration Tool
|
|
- [ ] Create `migrate_sqlite_to_pg.py` — reads from SQLite, writes to PostgreSQL
|
|
|
|
### Phase 7: Testing & Deployment
|
|
- [ ] Commit and push to remote
|
|
- [ ] User clones on NAS, copies `.env` and `_data/`
|
|
- [ ] User runs migration script
|
|
- [ ] User starts Docker containers
|