Sign inSign up

rdeangel/instrada-ogm-postgres

By rdeangel

ā€¢Updated 2 months ago

InstradaOGM for postgres db

Image
Networking
Security
0

2.2K

rdeangel/instrada-ogm-postgres repository overview

InstradaOGM Logo

ā InstradaOGM - Docker Deployment Guide (PostgreSQL)

Quick Start Guide for deploying InstradaOGM with PostgreSQL database using Docker

šŸ™ GitHubā  | šŸ˜ View SQLite Guideā 

ā šŸ“¦ Docker Hub Images

ā šŸŽÆ Deployment Options

Choose the deployment method that fits your environment:

ā Option 1: HTTP Only (Development/Testing)

Simple setup without SSL certificates - ideal for local networks or testing.

ā Option 2: HTTPS with Traefik (Production)

Production-ready setup with automatic SSL certificates via Let's Encrypt.

ā šŸ“‹ Prerequisites

  • Docker Engine 20.10+
  • Docker Compose v2.0+
  • OPNsense firewall with API access (v23.1+, preferred v25.1+)
  • Domain name (for HTTPS deployment)
  • Minimum 2GB RAM (4GB+ recommended for production)

ā šŸš€ Option 1: HTTP Deployment

ā Step 1: Create Project Directory
mkdir instrada-ogm && cd instrada-ogm
ā Step 2: Download Docker Compose File
curl -o docker-compose.yml https://raw.githubusercontent.com/rdeangel/InstradaOGM/main/docker-compose.yml
ā Step 3: Create Environment Configuration

Create a .env file with the following configuration:

# =============================================================================
# InstradaOGM - PostgreSQL HTTP Deployment Configuration
# =============================================================================

# --- Application Version ---
NEXT_PUBLIC_APP_VERSION=latest

# --- OPNsense Configuration (REQUIRED) ---
OPNSENSE_URL=https://your-opnsense-firewall.local
OPNSENSE_API_KEY=your_api_key_here
OPNSENSE_API_SECRET=your_api_secret_here

# For self-signed certificates (development only)
SKIP_SSL_VERIFICATION=true

# --- HTTP Configuration ---
ALLOW_HTTP=true
NEXTAUTH_URL=http://localhost:3000
PORT=3000

# --- Database Configuration (PostgreSQL) ---
POSTGRES_USER=instrada-ogm-user
POSTGRES_PASSWORD=CHANGE_THIS_TO_SECURE_PASSWORD
POSTGRES_HOST=db
POSTGRES_PORT=5432
POSTGRES_DB=instrada-ogm

# PostgreSQL connection string (auto-generated from above values)
DATABASE_URL=postgresql://${POSTGRES_USER}:${POSTGRES_PASSWORD}@${POSTGRES_HOST}:${POSTGRES_PORT}/${POSTGRES_DB}?schema=public

# --- Security Configuration (REQUIRED) ---
# Generate with: openssl rand -base64 32
NEXTAUTH_SECRET=CHANGE_THIS_TO_A_SECURE_RANDOM_STRING

# Generate with: openssl rand -hex 32
BACKUP_ENCRYPTION_SECRET_KEY=CHANGE_THIS_TO_A_64_CHARACTER_HEX_STRING

# --- Authentication ---
AUTH_ALLOW_LOCAL_LOGIN=true
AUTH_ALLOW_LOCAL_2FA=true
AUTH_PASSWORD_MIN_LENGTH=8

# --- Logging ---
APP_DEBUG_LEVEL=ERROR
NODE_ENV=production
ā Step 4: Create Data Directory
mkdir -p data/backups

Option A: Using the automated setup script (recommended)

# Download the permission setup script
curl -o setup-data-permissions.sh https://raw.githubusercontent.com/rdeangel/InstradaOGM/main/setup-data-permissions.sh
chmod +x setup-data-permissions.sh

# Run the script to set correct permissions
sudo ./setup-data-permissions.sh ./data

Option B: Manual permission setup

# Set correct ownership for the application user (UID 65532)
sudo chown -R 65532:65532 data/
ā Step 5: Start the Application
docker compose --profile postgres up -d
ā Step 6: Wait for Database Initialization
# Watch the logs until database is ready
docker compose --profile postgres logs -f db

# You should see: "database system is ready to accept connections"
ā Step 7: Access the Application

Open your browser and navigate to:

  • Application: http://192.168.1.100:3000 (replace with your server's IP address)
ā Step 8: Initial Setup

Default Login Credentials:

  • Username: admin
  • Password: admin

āš ļø Important: You will be required to change the admin password on first login for security.

  1. Open your browser and navigate to http://192.168.1.100:3000 (replace with your server's IP)
  2. Log in with the default credentials above
  3. You will be prompted to change the password immediately

ā šŸ”’ Option 2: HTTPS Deployment with Traefik

ā Step 1: Create Project Directory
mkdir instrada-ogm && cd instrada-ogm
ā Step 2: Download Configuration Files
# Download Traefik compose file
curl -o docker-compose-traefik.yml https://raw.githubusercontent.com/rdeangel/InstradaOGM/main/docker-compose-traefik.yml

# Download Traefik configuration scripts
mkdir -p traefik
cd traefik
curl -o generate-config.sh https://raw.githubusercontent.com/rdeangel/InstradaOGM/main/traefik/generate-config.sh
curl -o validate.sh https://raw.githubusercontent.com/rdeangel/InstradaOGM/main/traefik/validate.sh
chmod +x generate-config.sh validate.sh
cd ..
ā Step 3: Create Main Environment Configuration

Create a .env file in the root directory:

# =============================================================================
# InstradaOGM - PostgreSQL HTTPS Deployment Configuration
# =============================================================================

# --- Domain Configuration (REQUIRED for Traefik) ---
DOMAIN=your-instrada-ogm.com

# --- Application Version ---
NEXT_PUBLIC_APP_VERSION=latest

# --- OPNsense Configuration (REQUIRED) ---
OPNSENSE_URL=https://your-opnsense-firewall.local
OPNSENSE_API_KEY=your_api_key_here
OPNSENSE_API_SECRET=your_api_secret_here

# For self-signed certificates
SKIP_SSL_VERIFICATION=true

# --- HTTPS Configuration ---
ALLOW_HTTP=false
NEXTAUTH_URL=https://your-instrada-ogm.com
PORT=3000

# --- Database Configuration (PostgreSQL) ---
POSTGRES_USER=instrada-ogm-user # this can be any valid username
POSTGRES_PASSWORD=CHANGE_THIS_TO_SECURE_PASSWORD # this can be any valid password
POSTGRES_HOST=db
POSTGRES_PORT=5432
POSTGRES_DB=instrada-ogm

# PostgreSQL connection string (auto-generated from above values)
DATABASE_URL=postgresql://${POSTGRES_USER}:${POSTGRES_PASSWORD}@${POSTGRES_HOST}:${POSTGRES_PORT}/${POSTGRES_DB}?schema=public

# --- Security Configuration (REQUIRED) ---
# Generate with: openssl rand -base64 32
NEXTAUTH_SECRET=CHANGE_THIS_TO_A_SECURE_RANDOM_STRING

# Generate with: openssl rand -hex 32
BACKUP_ENCRYPTION_SECRET_KEY=CHANGE_THIS_TO_A_64_CHARACTER_HEX_STRING

# --- Authentication ---
AUTH_ALLOW_LOCAL_LOGIN=true
AUTH_ALLOW_LOCAL_2FA=true
AUTH_PASSWORD_MIN_LENGTH=8

# --- Logging ---
APP_DEBUG_LEVEL=ERROR
NODE_ENV=production
ā Step 4: Configure Traefik
cd traefik
./generate-config.sh

Edit traefik/runtime/.env.traefik:

# Must match DOMAIN in main .env file
DOMAIN=your-instrada-ogm.com

# DNS Provider (cloudflare, route53, digitalocean, etc.)
DNS_PROVIDER=cloudflare

# DNS Provider Credentials (example for Cloudflare)
CLOUDFLARE_DNS_API_TOKEN=your_cloudflare_api_token_here

# Let's Encrypt Configuration
[email protected]

# Use staging for testing, production for live certificates
ACME_SERVER=staging

Run the configuration generator again to apply settings:

./generate-config.sh
./validate.sh
cd ..
ā Step 5: Create Data Directory
mkdir -p data/backups

Option A: Using the automated setup script (recommended)

# Download the permission setup script
curl -o setup-data-permissions.sh https://raw.githubusercontent.com/rdeangel/InstradaOGM/main/setup-data-permissions.sh
chmod +x setup-data-permissions.sh

# Run the script to set correct permissions
sudo ./setup-data-permissions.sh ./data

Option B: Manual permission setup

# Set correct ownership for the application user (UID 65532)
sudo chown -R 65532:65532 data/
ā Step 6: Start the Application
docker compose -f docker-compose-traefik.yml --profile postgres up -d
ā Step 7: Wait for Database Initialization
# Watch the logs until database is ready
docker compose -f docker-compose-traefik.yml --profile postgres logs -f db

# You should see: "database system is ready to accept connections"
ā Step 8: Access the Application
  • Application: https://your-instrada-ogm.com
  • Traefik Dashboard: http://192.168.1.100:8080/dashboard/ (access from server only, disable in production)
ā Step 9: Initial Setup

Default Login Credentials:

  • Username: admin
  • Password: admin

āš ļø Important: You will be required to change the admin password on first login for security.

  1. Open your browser and navigate to https://your-instrada-ogm.com
  2. Log in with the default credentials above
  3. You will be prompted to change the password immediately
ā Step 10: Switch to Production SSL Certificates

Once you verify everything works with staging certificates:

  1. Edit traefik/runtime/.env.traefik and change:

    ACME_SERVER=production

  2. Regenerate configuration and restart:

    cd traefik && ./generate-config.sh && cd ..
docker compose -f docker-compose-traefik.yml --profile postgres down
rm traefik/runtime/acme.json
docker compose -f docker-compose-traefik.yml --profile postgres up -d

ā šŸ”§ Common Commands

ā View Logs
# HTTP deployment - all services
docker compose --profile postgres logs -f

# HTTP deployment - application only
docker compose --profile postgres logs -f instrada-ogm-postgres

# HTTP deployment - database only
docker compose --profile postgres logs -f db

# HTTPS deployment
docker compose -f docker-compose-traefik.yml --profile postgres logs -f
ā Stop Services
# HTTP deployment
docker compose --profile postgres down

# HTTPS deployment
docker compose -f docker-compose-traefik.yml --profile postgres down
ā Update to Latest Version
# HTTP deployment
docker compose --profile postgres pull
docker compose --profile postgres up -d

# HTTPS deployment
docker compose -f docker-compose-traefik.yml --profile postgres pull
docker compose -f docker-compose-traefik.yml --profile postgres up -d
ā Database Backup
# Create PostgreSQL backup
docker compose --profile postgres exec db pg_dump -U instrada-ogm-user instrada-ogm > backup-$(date +%Y%m%d).sql

# For HTTPS deployment
docker compose -f docker-compose-traefik.yml --profile postgres exec db pg_dump -U instrada-ogm-user instrada-ogm > backup-$(date +%Y%m%d).sql
ā Database Restore
# Restore PostgreSQL backup
docker compose --profile postgres exec -T db psql -U instrada-ogm-user instrada-ogm < backup-20240101.sql

# For HTTPS deployment
docker compose -f docker-compose-traefik.yml --profile postgres exec -T db psql -U instrada-ogm-user instrada-ogm < backup-20240101.sql
ā Access PostgreSQL CLI
# HTTP deployment
docker compose --profile postgres exec db psql -U instrada-ogm-user -d instrada-ogm

# HTTPS deployment
docker compose -f docker-compose-traefik.yml --profile postgres exec db psql -U instrada-ogm-user -d instrada-ogm

ā šŸ“ Directory Structure

instrada-ogm/
ā”œā”€ā”€ docker-compose.yml              # HTTP deployment
ā”œā”€ā”€ docker-compose-traefik.yml      # HTTPS deployment
ā”œā”€ā”€ .env                            # Main configuration
ā”œā”€ā”€ data/
ā”‚   ā””ā”€ā”€ backups/                    # Application backups
ā””ā”€ā”€ traefik/                        # Traefik configuration (HTTPS only)
    ā”œā”€ā”€ generate-config.sh
    ā”œā”€ā”€ validate.sh
    ā””ā”€ā”€ runtime/
        ā”œā”€ā”€ .env.traefik
        ā”œā”€ā”€ traefik.yml
        ā”œā”€ā”€ config.yml
        ā””ā”€ā”€ acme.json               # SSL certificates

Note: PostgreSQL data is stored in a Docker volume named postgres_data and managed automatically by Docker.

ā āš™ļø Configuration Reference

ā Required Variables
VariableDescriptionExample
OPNSENSE_URLOPNsense firewall URLhttps://firewall.local
OPNSENSE_API_KEYOPNsense API keyGenerated in OPNsense UI
OPNSENSE_API_SECRETOPNsense API secretGenerated in OPNsense UI
POSTGRES_USERPostgreSQL usernameinstrada-ogm-user
POSTGRES_PASSWORDPostgreSQL passwordStrong password
POSTGRES_DBPostgreSQL database nameinstrada-ogm
NEXTAUTH_SECRETJWT signing secretopenssl rand -base64 32
BACKUP_ENCRYPTION_SECRET_KEYBackup encryption keyopenssl rand -hex 32
ā Optional Variables
VariableDefaultDescription
ALLOW_HTTPfalseAllow HTTP connections
POSTGRES_HOSTdbPostgreSQL hostname
POSTGRES_PORT5432PostgreSQL port
APP_DEBUG_LEVELSILENTLogging level (SILENT/ERROR/WARN/INFO/DEBUG)
SKIP_SSL_VERIFICATIONfalseSkip OPNsense SSL verification (dev only)

ā šŸ†˜ Troubleshooting

ā Container won't start
# Check logs
docker compose --profile postgres logs instrada-ogm-postgres
docker compose --profile postgres logs db

# Verify .env file is present
ls -la .env

# Check environment variables are loaded
docker compose --profile postgres config
ā Database connection errors
# Check if database is ready
docker compose --profile postgres exec db pg_isready -U instrada-ogm-user

# Verify connection string
docker compose --profile postgres exec instrada-ogm-postgres env | grep DATABASE_URL

# Check if database exists
docker compose --profile postgres exec db psql -U instrada-ogm-user -l
ā Backup/data permission issues
# Option A: Use the automated setup script (recommended)
curl -o setup-data-permissions.sh https://raw.githubusercontent.com/rdeangel/InstradaOGM/main/setup-data-permissions.sh
chmod +x setup-data-permissions.sh
sudo ./setup-data-permissions.sh ./data

# Option B: Manual fix
sudo chown -R 65532:65532 data/
ā Cannot access application
# Verify containers are running
docker compose --profile postgres ps

# Check port binding
netstat -tuln | grep 3000

# Test local connection (or use your server IP)
curl http://localhost:3000
ā Database performance issues
# Check PostgreSQL stats
docker compose --profile postgres exec db psql -U instrada-ogm-user -d instrada-ogm -c "SELECT * FROM pg_stat_activity;"

# View active connections
docker compose --profile postgres exec db psql -U instrada-ogm-user -d instrada-ogm -c "SELECT count(*) FROM pg_stat_activity;"
ā Traefik SSL certificate issues
# Check Traefik logs
docker compose -f docker-compose-traefik.yml --profile postgres logs traefik

# Verify DNS is resolving
nslookup your-instrada-ogm.com

# Check acme.json permissions
ls -la traefik/runtime/acme.json
# Should be: -rw------- (600)
ā Reset Database (āš ļø WARNING: Deletes all data)
# Stop services
docker compose --profile postgres down

# Remove PostgreSQL volume
docker volume rm instrada-ogm_postgres_data

# Start services (fresh database)
docker compose --profile postgres up -d

ā šŸ” Production Security Checklist

  • Change default POSTGRES_PASSWORD to a strong password
  • Generate secure NEXTAUTH_SECRET using openssl rand -base64 32
  • Generate secure BACKUP_ENCRYPTION_SECRET_KEY using openssl rand -hex 32
  • Set ALLOW_HTTP=false for HTTPS enforcement
  • Set SKIP_SSL_VERIFICATION=false (use valid SSL for OPNsense)
  • Set APP_DEBUG_LEVEL=ERROR or SILENT
  • Set NODE_ENV=production
  • Configure regular database backups
  • Disable Traefik dashboard or secure it with authentication (port 8080)
  • Configure firewall rules to restrict database port (5432) access
  • Use production SSL certificates (not staging)

ā šŸ“Š Performance Optimization

ā PostgreSQL Tuning

For production deployments, consider tuning PostgreSQL settings:

# Edit docker-compose.yml and add to db service:
command:
  - "postgres"
  - "-c"
  - "max_connections=200"
  - "-c"
  - "shared_buffers=256MB"
  - "-c"
  - "effective_cache_size=1GB"
  - "-c"
  - "work_mem=4MB"
ā Resource Limits

Consider adding resource limits in docker-compose.yml:

services:
  instrada-ogm-postgres:
    deploy:
      resources:
        limits:
          cpus: '2'
          memory: 2G
  
  db:
    deploy:
      resources:
        limits:
          cpus: '2'
          memory: 2G

Category: Deployment | Database: PostgreSQL | Status: Production Ready

ā šŸ“ Notes

  • Default Login: Username admin, Password admin (must be changed on first login)
  • PostgreSQL is recommended for production and high-traffic deployments
  • Database data is stored in Docker volume postgres_data
  • For smaller deployments (< 10 users), consider SQLite deployment
  • Regular backups are essential - use pg_dump or the built-in backup feature
  • PostgreSQL 16 Alpine image provides optimal performance and minimal footprint

Tag summary

Content type

Image

Digest

sha256:4fad157d3ā€¦

Size

189.7 MB

Last updated

2 months ago

docker pull rdeangel/instrada-ogm-postgres