PostgreSQL Setup

ScanPick requires PostgreSQL 16. The API uses EF Core 10 with the Npgsql provider and applies migrations automatically on startup.

Quick Start (Docker)

The Docker Compose setup includes PostgreSQL 16:

services:
  postgres:
    image: postgres:16
    environment:
      POSTGRES_DB: scanpick
      POSTGRES_USER: scanpick
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
    volumes:
      - pgdata:/var/lib/postgresql/data
    ports:
      - "5432:5432"

Standalone PostgreSQL

Installation

# macOS
brew install postgresql@16
brew services start postgresql@16

# Ubuntu/Debian
sudo apt install postgresql-16
sudo systemctl start postgresql

# Windows
# Download from https://www.postgresql.org/download/windows/

Create Database and User

sudo -u postgres psql

CREATE USER scanpick WITH PASSWORD 'yourpassword';
CREATE DATABASE scanpick OWNER scanpick;
GRANT ALL PRIVILEGES ON DATABASE scanpick TO scanpick;

Connection String

# Local (no SSL)
DATABASE_CONNECTION_STRING="Host=localhost;Database=scanpick;Username=scanpick;Password=yourpassword"

# Remote (SSL)
DATABASE_CONNECTION_STRING="Host=db.example.com;Port=5432;Database=scanpick;Username=scanpick;Password=...;SSL Mode=Require"

Backups

pg_dump

pg_dump -U scanpick -h localhost scanpick > scanpick-backup-$(date +%Y%m%d).sql

Restore

psql -U scanpick -h localhost scanpick < scanpick-backup-20260619.sql

Performance

For warehouses with more than 100,000 pick tasks per day:

• Ensure work_mem is set to at least 32MB in postgresql.conf
• Set shared_buffers to 25% of available RAM
• Enable autovacuum (default: on)
• Consider placing the database on an SSD