Keybard-Vagabond-Demo/.cursor/rules/postgresql-database-template.yaml

# PostgreSQL Database Templates
# CloudNativePG cluster configuration and application integration

# Main PostgreSQL Cluster (already deployed as postgres-shared)
---
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: postgres-shared
  namespace: postgresql-system
spec:
  instances: 3  # High availability with automatic failover
  
  postgresql:
    parameters:
      max_connections: "200"
      shared_buffers: "256MB"
      effective_cache_size: "1GB"
      
  bootstrap:
    initdb:
      database: postgres
      owner: postgres
      
  storage:
    storageClass: longhorn-retain
    size: 50Gi
    
  monitoring:
    enabled: true
    
# Application-specific database and user creation
---
apiVersion: postgresql.cnpg.io/v1
kind: Database
metadata:
  name: app-database
  namespace: postgresql-system
spec:
  name: app_db
  owner: app_user
  cluster:
    name: postgres-shared

---
# Application database user secret
apiVersion: v1
kind: Secret
metadata:
  name: app-postgresql-secret
  namespace: app-namespace
type: Opaque
data:
  # Base64 encoded credentials (encrypted by SOPS)
  # Replace with actual base64-encoded values before encryption
  username: <REPLACE_WITH_BASE64_ENCODED_USERNAME>
  password: <REPLACE_WITH_BASE64_ENCODED_PASSWORD>
  database: <REPLACE_WITH_BASE64_ENCODED_DATABASE_NAME>

---
# Connection examples for different frameworks

# Laravel/Pixelfed connection
apiVersion: v1
kind: ConfigMap
metadata:
  name: laravel-db-config
data:
  DB_CONNECTION: "pgsql"
  DB_HOST: "postgresql-shared-rw.postgresql-system.svc.cluster.local"
  DB_PORT: "5432"
  DB_DATABASE: "pixelfed"

# Flask/PieFed connection  
apiVersion: v1
kind: ConfigMap
metadata:
  name: flask-db-config
data:
  DATABASE_URL: "postgresql://piefed_user:<REPLACE_WITH_PASSWORD>@postgresql-shared-rw.postgresql-system.svc.cluster.local:5432/piefed"

# Django/BookWyrm connection
apiVersion: v1
kind: ConfigMap
metadata:
  name: django-db-config
data:
  POSTGRES_HOST: "postgresql-shared-rw.postgresql-system.svc.cluster.local"
  PGPORT: "5432"
  POSTGRES_DB: "bookwyrm"
  POSTGRES_USER: "bookwyrm_user"

# Ruby/Mastodon connection
apiVersion: v1
kind: ConfigMap
metadata:
  name: mastodon-db-config
data:
  DB_HOST: "postgresql-shared-rw.postgresql-system.svc.cluster.local"
  DB_PORT: "5432"
  DB_NAME: "mastodon"
  DB_USER: "mastodon_user"

---
# Database monitoring ServiceMonitor
apiVersion: monitoring.coreos.com/v1
kind: ServiceMonitor
metadata:
  name: postgresql-metrics
  namespace: postgresql-system
spec:
  selector:
    matchLabels:
      cnpg.io/cluster: postgres-shared
  endpoints:
  - port: metrics
    interval: 30s
    path: /metrics

# Connection Patterns:
# - Read/Write: postgresql-shared-rw.postgresql-system.svc.cluster.local:5432
# - Read Only: postgresql-shared-ro.postgresql-system.svc.cluster.local:5432  
# - Read Replica: postgresql-shared-r.postgresql-system.svc.cluster.local:5432
# - Monitoring: Port 9187 for comprehensive PostgreSQL metrics
# - Backup: Integrated with S3 backup system via Longhorn volume labels

# Read Replica Usage Examples:

# Mastodon - Read replicas for timeline queries and caching
apiVersion: v1
kind: ConfigMap
metadata:
  name: mastodon-db-replica-config
data:
  DB_HOST: "postgresql-shared-rw.postgresql-system.svc.cluster.local"  # Primary for writes
  DB_REPLICA_HOST: "postgresql-shared-ro.postgresql-system.svc.cluster.local"  # Read replica for queries
  DB_PORT: "5432"
  DB_NAME: "mastodon"
  # Mastodon automatically uses read replicas for timeline and cache queries

# PieFed - Flask app with read/write splitting
apiVersion: v1
kind: ConfigMap
metadata:
  name: piefed-db-replica-config
data:
  # Primary database for writes
  DATABASE_URL: "postgresql://piefed_user:<REPLACE_WITH_PASSWORD>@postgresql-shared-rw.postgresql-system.svc.cluster.local:5432/piefed"
  # Read replica for heavy queries (feeds, search, analytics)
  DATABASE_REPLICA_URL: "postgresql://piefed_user:<REPLACE_WITH_PASSWORD>@postgresql-shared-ro.postgresql-system.svc.cluster.local:5432/piefed"

# Authentik - Optimized performance with primary and replica load balancing  
apiVersion: v1
kind: ConfigMap
metadata:
  name: authentik-db-replica-config
data:
  AUTHENTIK_POSTGRESQL__HOST: "postgresql-shared-rw.postgresql-system.svc.cluster.local"
  AUTHENTIK_POSTGRESQL__PORT: "5432"
  AUTHENTIK_POSTGRESQL__NAME: "authentik"
  # Authentik can use read replicas for user lookups and session validation
  AUTHENTIK_POSTGRESQL_REPLICA__HOST: "postgresql-shared-ro.postgresql-system.svc.cluster.local"

# BookWyrm - Django with database routing for read replicas
apiVersion: v1
kind: ConfigMap
metadata:
  name: bookwyrm-db-replica-config
data:
  POSTGRES_HOST: "postgresql-shared-rw.postgresql-system.svc.cluster.local"  # Primary
  POSTGRES_REPLICA_HOST: "postgresql-shared-ro.postgresql-system.svc.cluster.local"  # Read replica
  PGPORT: "5432"
  POSTGRES_DB: "bookwyrm"
  # Django database routing can direct read queries to replica automatically

# Available Metrics:
# - Connection: cnpg_backends_total, cnpg_pg_settings_setting{name="max_connections"}
# - Performance: cnpg_pg_stat_database_xact_commit, cnpg_pg_stat_database_xact_rollback  
# - Storage: cnpg_pg_database_size_bytes, cnpg_pg_stat_database_blks_hit
# - Health: cnpg_collector_up, cnpg_collector_postgres_version

# CRITICAL PostgreSQL Pod Management Safety ⚠️
# Source: https://cloudnative-pg.io/documentation/1.20/failure_modes/

# ✅ SAFE: Proper pod deletion for failover testing
# kubectl delete pod [primary-pod] --grace-period=1

# ❌ DANGEROUS: Never use grace-period=0 
# kubectl delete pod [primary-pod] --grace-period=0  # NEVER DO THIS!
#
# Why grace-period=0 is dangerous:
# - Immediately removes pod from Kubernetes API without proper shutdown
# - Doesn't ensure PID 1 process (instance manager) is shut down
# - Operator triggers failover without guarantee primary was properly stopped
# - Can cause misleading results in failover simulation tests
# - Does not reflect real failure scenarios (power loss, network partition)

# Proper PostgreSQL Pod Operations:
# - Use --grace-period=1 for failover simulation tests
# - Allow CloudNativePG operator to handle automatic failover
# - Use cnpg.io/reconciliationLoop: "disabled" annotation only for emergency manual intervention
# - Always remove reconciliation disable annotation after emergency operations