Materialized View Refresh Service

A Go service that automatically refreshes the workspace_daily_usage_mv materialized view in PostgreSQL at a scheduled time every night.

Configuration

The service is configured via environment variables:

Variable	Description	Default
DB_HOST	PostgreSQL host	localhost
DB_PORT	PostgreSQL port	5432
DB_USER	Database user	postgres
DB_PASSWORD	Database password	(empty)
DB_NAME	Database name	postgres
DB_SSLMODE	SSL mode (disable, require, verify-full)	disable
REFRESH_HOUR	Hour of day to run refresh (0-23)	2
REFRESH_MIN	Minute of hour to run refresh (0-59)	0

Running Locally

# Set environment variables
export DB_HOST=localhost
export DB_PORT=5432
export DB_USER=myuser
export DB_PASSWORD=mypassword
export DB_NAME=mydb
export REFRESH_HOUR=2
export REFRESH_MIN=0

# Build and run
go build -o mv-refresh-service .
./mv-refresh-service

Running with Docker

# Build the image
docker build -t mv-refresh-service .

# Run the container
docker run -d \
  --name mv-refresh-service \
  -e DB_HOST=your-db-host \
  -e DB_PORT=5432 \
  -e DB_USER=your-user \
  -e DB_PASSWORD=your-password \
  -e DB_NAME=your-database \
  -e DB_SSLMODE=require \
  -e REFRESH_HOUR=2 \
  -e REFRESH_MIN=0 \
  mv-refresh-service

Running with Docker Compose

version: '3.8'
services:
  mv-refresh-service:
    build: .
    environment:
      - DB_HOST=postgres
      - DB_PORT=5432
      - DB_USER=myuser
      - DB_PASSWORD=mypassword
      - DB_NAME=mydb
      - DB_SSLMODE=disable
      - REFRESH_HOUR=2
      - REFRESH_MIN=0
    restart: unless-stopped

Kubernetes Deployment

See k8s-deployment.yaml for a sample Kubernetes deployment configuration.

Features

• Scheduled Execution: Runs at a configurable time every night
• Graceful Shutdown: Handles SIGINT and SIGTERM signals
• Connection Retry: Validates database connection on startup
• Timeout Protection: 30-minute timeout for the refresh operation
• Logging: Detailed logging with timestamps

Logs

The service logs:

• Startup and connection status
• Next scheduled refresh time
• Refresh start and completion with duration
• Any errors encountered