freeleaps-ops/docs/PostgreSQL_Gitea_Integration_Guide.md

# 🐘 PostgreSQL & Gitea Integration Guide

> **Complete Guide to PostgreSQL Database Support for Gitea in Your FreeLeaps Kubernetes Environment**  
> *From configuration to data access to troubleshooting*

---

## 📋 **Table of Contents**

1. [🎯 **Overview**](#-overview)
2. [🏗️ **Your Current Setup**](#️-your-current-setup)
3. [🔧 **Database Configuration**](#-database-configuration)
4. [📊 **Data Access Methods**](#-data-access-methods)
5. [🔍 **Database Monitoring**](#-database-monitoring)
6. [🚨 **Troubleshooting**](#-troubleshooting)
7. [💾 **Backup & Recovery**](#-backup--recovery)
8. [🔒 **Security Best Practices**](#-security-best-practices)
9. [📈 **Performance Optimization**](#-performance-optimization)
10. [🛠️ **Maintenance Procedures**](#️-maintenance-procedures)

---

## 🎯 **Overview**

### **What is PostgreSQL in Your Gitea Setup?**

PostgreSQL serves as the **primary database backend** for your Gitea installation, storing all critical data including:

- **User accounts** and authentication information
- **Repository metadata** and settings
- **Issues, pull requests, and comments**
- **Wiki pages** and documentation
- **Activity logs** and audit trails
- **Organization and team data**
- **Webhook configurations** and integrations

### **Why PostgreSQL Instead of SQLite?**

Your setup uses PostgreSQL for several important reasons:

1. **Production Reliability** - Better for concurrent access and high availability
2. **Data Integrity** - ACID compliance and transaction support
3. **Scalability** - Can handle larger datasets and more users
4. **Backup & Recovery** - Better tools for data protection
5. **Performance** - Optimized for complex queries and indexing

---

## 🏗️ **Your Current Setup**

### **🌐 Environment-Specific Configurations**

#### **Alpha Environment** (`freeleaps-alpha`)
```yaml
# Location: freeleaps-ops/freeleaps/helm-pkg/3rd/gitea/values.alpha.yaml
postgresql-ha:
  enabled: false  # ❌ High Availability disabled

postgresql:
  enabled: true   # ✅ Standard PostgreSQL enabled
  global:
    postgresql:
      auth:
        password: r8sA8CPHD9!bt6d
        database: gitea
        username: gitea
      service:
        ports:
          postgresql: 5432
  primary:
    persistence:
      size: 5Gi   # 5GB storage for alpha
  volumePermissions:
    enabled: true
```

#### **Production Environment** (`freeleaps-prod`)
```yaml
# Location: freeleaps-ops/freeleaps/helm-pkg/3rd/gitea/values.prod.yaml
postgresql-ha:
  enabled: true   # ✅ High Availability enabled
  global:
    postgresql:
      database: gitea
      password: HFf#Xo4QtfK^CW?@
      username: gitea
  clusterDomain: freeleaps.cluster
  postgresql:
    image:
      repository: freeleaps/postgresql-repmgr
      tag: 16.3.0-debian-12-r20
    repmgrPassword: WGZ47gbUTLvo
    postgresPassword: X9H2*9M2ZWYmuZ
    password: bhYmZ3ivfD@udWc@8jrB4@
  pgpool:
    adminPassword: Rk(*4vc%wFVu5>C|
  persistence:
    size: 25Gi   # 25GB storage for production

postgresql:
  enabled: false  # ❌ Standard PostgreSQL disabled
```

### **🔧 Architecture Comparison**

#### **Alpha Environment (Standard PostgreSQL)**
```
┌─────────────────────────────────────────────────────────────┐
│                    ALPHA ENVIRONMENT                        │
├─────────────────────────────────────────────────────────────┤
│  ┌─────────────────┐  ┌─────────────────┐  ┌──────────────┐ │
│  │   Gitea Pod     │  │   PostgreSQL    │  │   Redis      │ │
│  │   (Web UI)      │  │   (Database)    │  │   (Cache)    │ │
│  └─────────────────┘  └─────────────────┘  └──────────────┘ │
│           │                    │                    │        │
│           │ TCP 5432           │                    │        │
│           └────────────────────┘                    │        │
│  ┌─────────────────────────────────────────────────────────┐ │
│  │              Single PostgreSQL Instance                │ │
│  │  - Database: gitea                                     │ │
│  │  - User: gitea                                         │ │
│  │  - Storage: 5Gi                                        │ │
│  │  - No replication                                      │ │
│  └─────────────────────────────────────────────────────────┘ │
└─────────────────────────────────────────────────────────────┘
```

#### **Production Environment (PostgreSQL HA)**
```
┌─────────────────────────────────────────────────────────────┐
│                  PRODUCTION ENVIRONMENT                     │
├─────────────────────────────────────────────────────────────┤
│  ┌─────────────────┐  ┌─────────────────┐  ┌──────────────┐ │
│  │   Gitea Pod     │  │   PostgreSQL    │  │   Redis      │ │
│  │   (Web UI)      │  │   HA Cluster    │  │   (Cache)    │ │
│  └─────────────────┘  └─────────────────┘  └──────────────┘ │
│           │                    │                    │        │
│           │ TCP 5432           │                    │        │
│           └────────────────────┘                    │        │
│  ┌─────────────────────────────────────────────────────────┐ │
│  │              PostgreSQL HA Cluster                     │ │
│  │  ┌─────────────┐  ┌─────────────┐  ┌─────────────┐    │ │
│  │  │   Primary   │  │   Replica   │  │   PgPool    │    │ │
│  │  │   Node      │  │   Node      │  │   (Proxy)   │    │ │
│  │  └─────────────┘  └─────────────┘  └─────────────┘    │ │
│  │  - Database: gitea                                     │ │
│  │  - User: gitea                                         │ │
│  │  - Storage: 25Gi                                       │ │
│  │  - Automatic failover                                   │ │
│  └─────────────────────────────────────────────────────────┘ │
└─────────────────────────────────────────────────────────────┘
```

---

## 🔧 **Database Configuration**

### **📋 How Gitea Connects to PostgreSQL**

#### **Connection Configuration**
Gitea automatically configures its database connection based on your Helm values:

```yaml
# Gitea database configuration (auto-generated)
database:
  DB_TYPE: postgres
  HOST: <postgresql-service-name>.<namespace>.svc.cluster.local
  NAME: gitea
  USER: gitea
  PASSWD: <password-from-values>
  PORT: 5432
```

#### **Service Discovery**
```bash
# Alpha Environment
HOST: gitea-postgresql.freeleaps-alpha.svc.cluster.local

# Production Environment  
HOST: gitea-postgresql-ha-pgpool.freeleaps-prod.svc.cluster.local
```

### **🔍 Database Schema**

#### **Key Gitea Tables**
```sql
-- User management
user                    -- User accounts and profiles
public_key             -- SSH public keys
access_token           -- API access tokens

-- Repository management  
repository              -- Repository metadata
mirror                  -- Repository mirrors
repo_unit              -- Repository permissions
repo_watch             -- Repository watching

-- Issue tracking
issue                  -- Issues and pull requests
comment                -- Comments on issues/PRs
label                  -- Issue labels
milestone              -- Project milestones

-- Activity and logs
action                 -- User activity logs
webhook                -- Webhook configurations
hook_task              -- Webhook execution history

-- Wiki and documentation
wiki_page              -- Wiki pages
wiki_revision          -- Wiki page revisions
```

---

## 📊 **Data Access Methods**

### **🔑 Direct Database Access**

#### **Method 1: Port Forward (Recommended)**
```bash
# Alpha Environment
kubectl port-forward svc/gitea-postgresql -n freeleaps-alpha 5432:5432

# Production Environment
kubectl port-forward svc/gitea-postgresql-ha-pgpool -n freeleaps-prod 5432:5432

# Connect with psql
psql -h localhost -p 5432 -U gitea -d gitea
# Password: r8sA8CPHD9!bt6d (alpha) or HFf#Xo4QtfK^CW?@ (prod)
```

#### **Method 2: Direct Pod Access**
```bash
# Alpha Environment
kubectl exec -it deployment/gitea-postgresql -n freeleaps-alpha -- psql -U gitea -d gitea

# Production Environment
kubectl exec -it statefulset/gitea-postgresql-ha-postgresql-0 -n freeleaps-prod -- psql -U gitea -d gitea
```

#### **Method 3: Using kubectl exec with psql**
```bash
# Alpha Environment
kubectl exec -it deployment/gitea-postgresql -n freeleaps-alpha -- psql -U gitea -d gitea -c "SELECT * FROM user LIMIT 5;"

# Production Environment
kubectl exec -it statefulset/gitea-postgresql-ha-postgresql-0 -n freeleaps-prod -- psql -U gitea -d gitea -c "SELECT * FROM user LIMIT 5;"
```

### **🔍 Useful Database Queries**

#### **User Management**
```sql
-- List all users
SELECT id, name, email, created_unix, is_admin FROM user;

-- Find admin users
SELECT id, name, email FROM user WHERE is_admin = true;

-- Count total users
SELECT COUNT(*) as total_users FROM user;

-- Find recently created users
SELECT name, email, created_unix 
FROM user 
WHERE created_unix > extract(epoch from now() - interval '30 days')
ORDER BY created_unix DESC;
```

#### **Repository Management**
```sql
-- List all repositories
SELECT id, owner_id, name, description, created_unix 
FROM repository 
ORDER BY created_unix DESC;

-- Find repositories by owner
SELECT r.id, r.name, r.description, u.name as owner_name
FROM repository r
JOIN user u ON r.owner_id = u.id
WHERE u.name = 'your-username';

-- Count repositories per user
SELECT u.name, COUNT(r.id) as repo_count
FROM user u
LEFT JOIN repository r ON u.id = r.owner_id
GROUP BY u.id, u.name
ORDER BY repo_count DESC;
```

#### **Issue Tracking**
```sql
-- List recent issues
SELECT i.id, i.title, i.created_unix, u.name as creator
FROM issue i
JOIN user u ON i.poster_id = u.id
WHERE i.is_closed = false
ORDER BY i.created_unix DESC
LIMIT 10;

-- Count issues by status
SELECT 
  CASE WHEN is_closed = true THEN 'Closed' ELSE 'Open' END as status,
  COUNT(*) as count
FROM issue
GROUP BY is_closed;

-- Find issues with most comments
SELECT i.id, i.title, COUNT(c.id) as comment_count
FROM issue i
LEFT JOIN comment c ON i.id = c.issue_id
GROUP BY i.id, i.title
ORDER BY comment_count DESC
LIMIT 10;
```

#### **Activity Monitoring**
```sql
-- Recent user activity
SELECT a.id, a.user_id, a.op_type, a.repo_id, a.created_unix, u.name as user_name
FROM action a
JOIN user u ON a.user_id = u.id
ORDER BY a.created_unix DESC
LIMIT 20;

-- Most active users
SELECT u.name, COUNT(a.id) as activity_count
FROM user u
JOIN action a ON u.id = a.user_id
WHERE a.created_unix > extract(epoch from now() - interval '7 days')
GROUP BY u.id, u.name
ORDER BY activity_count DESC
LIMIT 10;
```

### **📊 Database Monitoring Queries**

#### **Performance Metrics**
```sql
-- Database size
SELECT 
  pg_size_pretty(pg_database_size('gitea')) as database_size;

-- Table sizes
SELECT 
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size
FROM pg_tables 
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

-- Connection count
SELECT count(*) as active_connections 
FROM pg_stat_activity 
WHERE state = 'active';
```

---

## 🔍 **Database Monitoring**

### **📊 Key Metrics to Monitor**

#### **1. Connection Health**
```bash
# Check if Gitea can connect to PostgreSQL
kubectl logs -f deployment/gitea -n freeleaps-alpha | grep -i "database\|postgres"

# Check PostgreSQL service status
kubectl get svc -n freeleaps-alpha | grep postgresql
kubectl get pods -n freeleaps-alpha | grep postgresql
```

#### **2. Performance Metrics**
```bash
# Check PostgreSQL resource usage
kubectl top pods -n freeleaps-alpha | grep postgresql

# Check storage usage
kubectl get pvc -n freeleaps-alpha | grep postgresql
```

#### **3. Production HA Monitoring**
```bash
# Check HA cluster status (Production only)
kubectl exec -it statefulset/gitea-postgresql-ha-postgresql-0 -n freeleaps-prod -- repmgr cluster show

# Check PgPool status (Production only)
kubectl exec -it deployment/gitea-postgresql-ha-pgpool -n freeleaps-prod -- pgpool -c "SHOW pool_status;"
```

### **🚨 Monitoring Alerts**

#### **Critical Alerts**
```yaml
# Database connection failures
- Alert: GiteaDatabaseConnectionFailed
  Condition: Gitea cannot connect to PostgreSQL
  Action: Check PostgreSQL pod status and logs

# High connection count
- Alert: PostgreSQLHighConnections
  Condition: Active connections > 80% of max_connections
  Action: Investigate connection leaks or scaling needs

# Storage warnings
- Alert: PostgreSQLStorageWarning
  Condition: Storage usage > 80%
  Action: Plan storage expansion
```

---

## 🚨 **Troubleshooting**

### **🔍 Common Issues & Solutions**

#### **1. Gitea Cannot Connect to Database**

##### **Problem**: Gitea fails to start due to database connection issues
```bash
# Symptoms:
# - Gitea pod stuck in CrashLoopBackOff
# - Logs show "database connection failed"
# - Gitea web UI shows database error

# Diagnosis:
kubectl logs -f deployment/gitea -n freeleaps-alpha
kubectl describe pod -l app=gitea -n freeleaps-alpha

# Solutions:
# 1. Check PostgreSQL pod status
kubectl get pods -n freeleaps-alpha | grep postgresql

# 2. Check PostgreSQL logs
kubectl logs -f deployment/gitea-postgresql -n freeleaps-alpha

# 3. Test database connectivity
kubectl exec -it deployment/gitea-postgresql -n freeleaps-alpha -- psql -U gitea -d gitea -c "SELECT 1;"

# 4. Verify credentials
kubectl get secret gitea-postgresql -n freeleaps-alpha -o yaml
```

#### **2. Database Performance Issues**

##### **Problem**: Slow database queries or high resource usage
```bash
# Symptoms:
# - Slow Gitea web interface
# - High CPU/memory usage on PostgreSQL pod
# - Timeout errors in logs

# Diagnosis:
# 1. Check resource usage
kubectl top pods -n freeleaps-alpha | grep postgresql

# 2. Check slow queries
kubectl exec -it deployment/gitea-postgresql -n freeleaps-alpha -- psql -U gitea -d gitea -c "
SELECT query, mean_time, calls 
FROM pg_stat_statements 
ORDER BY mean_time DESC 
LIMIT 10;"

# 3. Check table statistics
kubectl exec -it deployment/gitea-postgresql -n freeleaps-alpha -- psql -U gitea -d gitea -c "
SELECT schemaname, tablename, n_tup_ins, n_tup_upd, n_tup_del, n_live_tup, n_dead_tup
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;"

# Solutions:
# 1. Optimize slow queries
# 2. Add database indexes
# 3. Increase resource limits
# 4. Consider read replicas (Production)
```

#### **3. Storage Issues**

##### **Problem**: Database running out of storage space
```bash
# Symptoms:
# - PostgreSQL pod shows storage errors
# - PVC shows high usage
# - Database operations fail

# Diagnosis:
# 1. Check PVC usage
kubectl get pvc -n freeleaps-alpha | grep postgresql

# 2. Check database size
kubectl exec -it deployment/gitea-postgresql -n freeleaps-alpha -- psql -U gitea -d gitea -c "
SELECT pg_size_pretty(pg_database_size('gitea'));"

# 3. Check table sizes
kubectl exec -it deployment/gitea-postgresql -n freeleaps-alpha -- psql -U gitea -d gitea -c "
SELECT tablename, pg_size_pretty(pg_total_relation_size(tablename)) as size
FROM pg_tables 
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(tablename) DESC;"

# Solutions:
# 1. Increase PVC size
kubectl patch pvc gitea-postgresql -n freeleaps-alpha -p '{"spec":{"resources":{"requests":{"storage":"10Gi"}}}}'

# 2. Clean up old data
kubectl exec -it deployment/gitea-postgresql -n freeleaps-alpha -- psql -U gitea -d gitea -c "VACUUM FULL;"

# 3. Archive old repositories/issues
```

#### **4. Production HA Issues**

##### **Problem**: PostgreSQL HA cluster not healthy
```bash
# Symptoms:
# - Primary node down
# - Replication lag
# - PgPool connection errors

# Diagnosis:
# 1. Check cluster status
kubectl exec -it statefulset/gitea-postgresql-ha-postgresql-0 -n freeleaps-prod -- repmgr cluster show

# 2. Check node status
kubectl exec -it statefulset/gitea-postgresql-ha-postgresql-0 -n freeleaps-prod -- repmgr node status

# 3. Check PgPool status
kubectl exec -it deployment/gitea-postgresql-ha-pgpool -n freeleaps-prod -- pgpool -c "SHOW pool_status;"

# Solutions:
# 1. Restart problematic nodes
kubectl delete pod gitea-postgresql-ha-postgresql-1 -n freeleaps-prod

# 2. Rejoin cluster if needed
kubectl exec -it statefulset/gitea-postgresql-ha-postgresql-1 -n freeleaps-prod -- repmgr node rejoin

# 3. Check replication lag
kubectl exec -it statefulset/gitea-postgresql-ha-postgresql-0 -n freeleaps-prod -- repmgr cluster show
```

### **🛠️ Debugging Commands**

#### **Essential Debugging Commands**
```bash
# Check PostgreSQL status
kubectl exec -it deployment/gitea-postgresql -n freeleaps-alpha -- pg_isready -U gitea -d gitea

# Check database connections
kubectl exec -it deployment/gitea-postgresql -n freeleaps-alpha -- psql -U gitea -d gitea -c "
SELECT count(*) as active_connections 
FROM pg_stat_activity 
WHERE state = 'active';"

# Check database locks
kubectl exec -it deployment/gitea-postgresql -n freeleaps-alpha -- psql -U gitea -d gitea -c "
SELECT * FROM pg_locks WHERE NOT granted;"

# Check slow queries
kubectl exec -it deployment/gitea-postgresql -n freeleaps-alpha -- psql -U gitea -d gitea -c "
SELECT query, mean_time, calls 
FROM pg_stat_statements 
ORDER BY mean_time DESC 
LIMIT 5;"

# Check table bloat
kubectl exec -it deployment/gitea-postgresql -n freeleaps-alpha -- psql -U gitea -d gitea -c "
SELECT schemaname, tablename, n_dead_tup, n_live_tup,
       CASE WHEN n_live_tup > 0 THEN round(100.0 * n_dead_tup / n_live_tup, 2) ELSE 0 END as bloat_ratio
FROM pg_stat_user_tables
WHERE n_dead_tup > 0
ORDER BY bloat_ratio DESC;"
```

---

## 💾 **Backup & Recovery**

### **📦 Backup Strategies**

#### **1. Automated Backups**
```bash
# Create backup script
#!/bin/bash
# scripts/backup-gitea-database.sh

NAMESPACE="freeleaps-alpha"
BACKUP_DIR="/tmp/gitea-backups"
DATE=$(date +%Y%m%d_%H%M%S)

mkdir -p $BACKUP_DIR

echo "📦 Creating Gitea database backup..."

# Create database backup
kubectl exec -it deployment/gitea-postgresql -n $NAMESPACE -- pg_dump -U gitea -d gitea --format=custom --file=/tmp/gitea-backup-$DATE.dump

# Copy backup file
kubectl cp $NAMESPACE/deployment/gitea-postgresql:/tmp/gitea-backup-$DATE.dump $BACKUP_DIR/

echo "✅ Backup created: $BACKUP_DIR/gitea-backup-$DATE.dump"
```

#### **2. Manual Backups**
```bash
# Alpha Environment
kubectl exec -it deployment/gitea-postgresql -n freeleaps-alpha -- pg_dump -U gitea -d gitea > gitea-backup-$(date +%Y%m%d).sql

# Production Environment
kubectl exec -it statefulset/gitea-postgresql-ha-postgresql-0 -n freeleaps-prod -- pg_dump -U gitea -d gitea > gitea-prod-backup-$(date +%Y%m%d).sql
```

#### **3. Backup Verification**
```bash
# Verify backup integrity
pg_restore --list gitea-backup-20241201.dump

# Test restore to temporary database
kubectl exec -it deployment/gitea-postgresql -n freeleaps-alpha -- psql -U gitea -d gitea -c "CREATE DATABASE gitea_test;"
kubectl exec -it deployment/gitea-postgresql -n freeleaps-alpha -- pg_restore -U gitea -d gitea_test gitea-backup-20241201.dump
```

### **🔄 Recovery Procedures**

#### **1. Full Database Restore**
```bash
# Stop Gitea
kubectl scale deployment gitea -n freeleaps-alpha --replicas=0

# Restore database
kubectl exec -it deployment/gitea-postgresql -n freeleaps-alpha -- pg_restore -U gitea -d gitea --clean --if-exists gitea-backup-20241201.dump

# Restart Gitea
kubectl scale deployment gitea -n freeleaps-alpha --replicas=1
```

#### **2. Selective Data Recovery**
```bash
# Restore specific tables
kubectl exec -it deployment/gitea-postgresql -n freeleaps-alpha -- psql -U gitea -d gitea -c "
-- Restore only user table
\copy user FROM '/tmp/user_backup.csv' WITH CSV HEADER;

-- Restore only repository table  
\copy repository FROM '/tmp/repository_backup.csv' WITH CSV HEADER;"
```

#### **3. Point-in-Time Recovery**
```bash
# Enable WAL archiving for point-in-time recovery
# This requires additional PostgreSQL configuration
kubectl exec -it deployment/gitea-postgresql -n freeleaps-alpha -- psql -U gitea -d gitea -c "
-- Restore to specific timestamp
SELECT pg_wal_replay_resume();
SELECT pg_wal_replay_pause();"
```

---

## 🔒 **Security Best Practices**

### **🛡️ Current Security Analysis**

#### **✅ Security Strengths**
1. **Network isolation** - PostgreSQL runs in Kubernetes namespace
2. **Resource limits** - CPU and memory limits set
3. **Non-root user** - Runs as postgres user in container
4. **TLS support** - SSL/TLS configuration available (Production)

#### **⚠️ Security Weaknesses**
1. **Hardcoded passwords** - Passwords in values files
2. **Default permissions** - Overly permissive user access
3. **No audit logging** - Limited security event tracking
4. **No network policies** - No ingress/egress restrictions

### **🔧 Security Improvements**

#### **1. Secret Management**
```yaml
# Use Kubernetes secrets instead of hardcoded values
apiVersion: v1
kind: Secret
metadata:
  name: gitea-postgresql-credentials
  namespace: freeleaps-alpha
type: Opaque
data:
  username: Z2l0ZWE=  # base64 encoded
  password: <base64-encoded-password>
---
# Reference in Helm values
postgresql:
  global:
    postgresql:
      auth:
        existingSecret: gitea-postgresql-credentials
        secretKeys:
          usernameKey: username
          passwordKey: password
```

#### **2. Network Policies**
```yaml
# Restrict network access to PostgreSQL
apiVersion: networking.k8s.io/v1
kind: NetworkPolicy
metadata:
  name: gitea-postgresql-network-policy
  namespace: freeleaps-alpha
spec:
  podSelector:
    matchLabels:
      app: postgresql
  policyTypes:
  - Ingress
  ingress:
  - from:
    - namespaceSelector:
        matchLabels:
          name: freeleaps-alpha
    ports:
    - protocol: TCP
      port: 5432
```

#### **3. Database Hardening**
```sql
-- Limit database connections
ALTER SYSTEM SET max_connections = 100;

-- Enable SSL (Production)
ALTER SYSTEM SET ssl = on;
ALTER SYSTEM SET ssl_cert_file = '/etc/ssl/certs/server.crt';
ALTER SYSTEM SET ssl_key_file = '/etc/ssl/private/server.key';

-- Enable audit logging
CREATE EXTENSION pgaudit;
ALTER SYSTEM SET pgaudit.log = 'all';
ALTER SYSTEM SET pgaudit.log_level = 'log';
```

---

## 📈 **Performance Optimization**

### **🎯 Performance Tuning**

#### **1. Database Configuration**
```yaml
# Optimize PostgreSQL settings
postgresql:
  primary:
    extendedConfiguration: |-
      # Memory settings
      shared_buffers = 256MB
      effective_cache_size = 1GB
      work_mem = 4MB
      maintenance_work_mem = 64MB
      
      # Checkpoint settings
      checkpoint_completion_target = 0.9
      wal_buffers = 16MB
      
      # Query optimization
      random_page_cost = 1.1
      effective_io_concurrency = 200
      
      # Logging
      log_min_duration_statement = 1000
      log_checkpoints = on
      log_connections = on
      log_disconnections = on      
```

#### **2. Index Optimization**
```sql
-- Create indexes for common queries
CREATE INDEX CONCURRENTLY idx_user_email ON user(email);
CREATE INDEX CONCURRENTLY idx_repository_owner ON repository(owner_id);
CREATE INDEX CONCURRENTLY idx_issue_repo ON issue(repo_id);
CREATE INDEX CONCURRENTLY idx_action_user_time ON action(user_id, created_unix);

-- Analyze table statistics
ANALYZE user;
ANALYZE repository;
ANALYZE issue;
ANALYZE action;
```

#### **3. Query Optimization**
```sql
-- Monitor slow queries
SELECT query, mean_time, calls, total_time
FROM pg_stat_statements
WHERE mean_time > 1000
ORDER BY mean_time DESC;

-- Optimize specific queries
-- Example: Optimize user search query
EXPLAIN ANALYZE SELECT * FROM user WHERE name ILIKE '%search%';
```

### **📊 Performance Monitoring**

#### **Key Performance Indicators**
1. **Query Response Time** - Average query execution time
2. **Connection Count** - Active database connections
3. **Cache Hit Ratio** - Buffer cache efficiency
4. **I/O Wait Time** - Disk I/O performance
5. **Lock Wait Time** - Database contention

#### **Performance Benchmarks**
```bash
# Your expected performance:
# - Query response time: < 100ms for simple queries
# - Connection count: < 80% of max_connections
# - Cache hit ratio: > 90%
# - I/O wait: < 5% of total time
# - Lock wait: < 1% of total time
```

---

## 🛠️ **Maintenance Procedures**

### **📅 Regular Maintenance Tasks**

#### **Daily Tasks**
```bash
# 1. Check database health
kubectl exec -it deployment/gitea-postgresql -n freeleaps-alpha -- pg_isready -U gitea -d gitea

# 2. Monitor connection count
kubectl exec -it deployment/gitea-postgresql -n freeleaps-alpha -- psql -U gitea -d gitea -c "
SELECT count(*) as active_connections 
FROM pg_stat_activity 
WHERE state = 'active';"

# 3. Check for long-running queries
kubectl exec -it deployment/gitea-postgresql -n freeleaps-alpha -- psql -U gitea -d gitea -c "
SELECT pid, now() - pg_stat_activity.query_start AS duration, query 
FROM pg_stat_activity 
WHERE state = 'active' 
AND now() - pg_stat_activity.query_start > interval '5 minutes';"
```

#### **Weekly Tasks**
```bash
# 1. Analyze table statistics
kubectl exec -it deployment/gitea-postgresql -n freeleaps-alpha -- psql -U gitea -d gitea -c "
ANALYZE user;
ANALYZE repository;
ANALYZE issue;
ANALYZE action;"

# 2. Check table bloat
kubectl exec -it deployment/gitea-postgresql -n freeleaps-alpha -- psql -U gitea -d gitea -c "
SELECT schemaname, tablename, n_dead_tup, n_live_tup,
       CASE WHEN n_live_tup > 0 THEN round(100.0 * n_dead_tup / n_live_tup, 2) ELSE 0 END as bloat_ratio
FROM pg_stat_user_tables
WHERE n_dead_tup > 0
ORDER BY bloat_ratio DESC;"

# 3. Review slow queries
kubectl exec -it deployment/gitea-postgresql -n freeleaps-alpha -- psql -U gitea -d gitea -c "
SELECT query, mean_time, calls 
FROM pg_stat_statements 
ORDER BY mean_time DESC 
LIMIT 10;"
```

#### **Monthly Tasks**
```bash
# 1. Full database backup
./scripts/backup-gitea-database.sh

# 2. Vacuum and reindex
kubectl exec -it deployment/gitea-postgresql -n freeleaps-alpha -- psql -U gitea -d gitea -c "
VACUUM FULL;
REINDEX DATABASE gitea;"

# 3. Review and optimize indexes
kubectl exec -it deployment/gitea-postgresql -n freeleaps-alpha -- psql -U gitea -d gitea -c "
SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;"
```

### **🔧 Maintenance Scripts**

#### **Health Check Script**
```bash
#!/bin/bash
# scripts/gitea-database-health-check.sh

NAMESPACE="freeleaps-alpha"
POD_NAME=$(kubectl get pods -n $NAMESPACE -l app=postgresql -o jsonpath='{.items[0].metadata.name}')

echo "🐘 Gitea Database Health Check - $(date)"
echo "=================================="

# Check database connectivity
echo "📊 Database Connectivity:"
kubectl exec -it $POD_NAME -n $NAMESPACE -- pg_isready -U gitea -d gitea

# Check active connections
echo "🔗 Active Connections:"
kubectl exec -it $POD_NAME -n $NAMESPACE -- psql -U gitea -d gitea -c "
SELECT count(*) as active_connections 
FROM pg_stat_activity 
WHERE state = 'active';"

# Check database size
echo "💾 Database Size:"
kubectl exec -it $POD_NAME -n $NAMESPACE -- psql -U gitea -d gitea -c "
SELECT pg_size_pretty(pg_database_size('gitea'));"

# Check table statistics
echo "📋 Table Statistics:"
kubectl exec -it $POD_NAME -n $NAMESPACE -- psql -U gitea -d gitea -c "
SELECT schemaname, tablename, n_tup_ins, n_tup_upd, n_tup_del, n_live_tup, n_dead_tup
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 5;"
```

#### **Backup Script**
```bash
#!/bin/bash
# scripts/gitea-database-backup.sh

NAMESPACE="freeleaps-alpha"
BACKUP_DIR="/tmp/gitea-backups"
DATE=$(date +%Y%m%d_%H%M%S)

mkdir -p $BACKUP_DIR

echo "📦 Creating Gitea database backup..."

# Create database backup
kubectl exec -it deployment/gitea-postgresql -n $NAMESPACE -- pg_dump -U gitea -d gitea --format=custom --file=/tmp/gitea-backup-$DATE.dump

# Copy backup file
kubectl cp $NAMESPACE/deployment/gitea-postgresql:/tmp/gitea-backup-$DATE.dump $BACKUP_DIR/

# Verify backup
kubectl exec -it deployment/gitea-postgresql -n $NAMESPACE -- pg_restore --list /tmp/gitea-backup-$DATE.dump > /dev/null

if [ $? -eq 0 ]; then
    echo "✅ Backup created successfully: $BACKUP_DIR/gitea-backup-$DATE.dump"
else
    echo "❌ Backup verification failed"
    exit 1
fi
```

### **🚨 Emergency Procedures**

#### **1. Database Corruption Recovery**
```bash
# If database corruption is detected:
# 1. Stop Gitea
kubectl scale deployment gitea -n freeleaps-alpha --replicas=0

# 2. Restore from latest backup
kubectl exec -it deployment/gitea-postgresql -n freeleaps-alpha -- pg_restore -U gitea -d gitea --clean --if-exists /tmp/gitea-backup-latest.dump

# 3. Restart Gitea
kubectl scale deployment gitea -n freeleaps-alpha --replicas=1
```

#### **2. Performance Emergency**
```bash
# If database performance is severely degraded:
# 1. Check for long-running queries
kubectl exec -it deployment/gitea-postgresql -n freeleaps-alpha -- psql -U gitea -d gitea -c "
SELECT pid, now() - pg_stat_activity.query_start AS duration, query 
FROM pg_stat_activity 
WHERE state = 'active' 
AND now() - pg_stat_activity.query_start > interval '5 minutes';"

# 2. Kill problematic queries
kubectl exec -it deployment/gitea-postgresql -n freeleaps-alpha -- psql -U gitea -d gitea -c "SELECT pg_terminate_backend(pid);"

# 3. Restart PostgreSQL if needed
kubectl rollout restart deployment/gitea-postgresql -n freeleaps-alpha
```

---

## 🎯 **Summary & Next Steps**

### **📊 Current State Assessment**

#### **✅ Strengths**
1. **Production-ready setup** - HA clustering in production
2. **Helm-based deployment** - Consistent and repeatable
3. **Environment separation** - Alpha vs Production configurations
4. **Integration working** - Gitea successfully using PostgreSQL
5. **Monitoring available** - Basic health checks and metrics

#### **⚠️ Areas for Improvement**
1. **Security hardening** - Remove hardcoded passwords, implement secrets
2. **Backup automation** - Implement automated backup procedures
3. **Performance tuning** - Optimize database settings for your workload
4. **Monitoring enhancement** - Add comprehensive monitoring and alerting
5. **Documentation** - Create runbooks for common operations

### **🚀 Recommended Actions**

#### **Immediate (This Week)**
1. **Implement secret management** - Move passwords to Kubernetes secrets
2. **Set up automated backups** - Create daily backup scripts
3. **Add basic monitoring** - Set up database health checks
4. **Document procedures** - Create runbooks for common operations

#### **Short Term (Next Month)**
1. **Security audit** - Review and improve security posture
2. **Performance tuning** - Optimize settings based on usage patterns
3. **Monitoring enhancement** - Add comprehensive metrics and alerting
4. **Training** - Train team on database management and troubleshooting

#### **Long Term (Next Quarter)**
1. **Advanced monitoring** - Implement predictive analytics and alerting
2. **Disaster recovery** - Set up automated backup and recovery procedures
3. **Performance optimization** - Implement query optimization and indexing
4. **Capacity planning** - Plan for growth and scaling

### **📚 Additional Resources**

#### **Official Documentation**
- **[PostgreSQL Documentation](https://www.postgresql.org/docs/)** - Official PostgreSQL guides
- **[Gitea Database Documentation](https://docs.gitea.com/installation/database-prep)** - Database setup guide
- **[Bitnami PostgreSQL Helm Chart](https://github.com/bitnami/charts/tree/main/bitnami/postgresql)** - Helm chart documentation

#### **Community Resources**
- **[PostgreSQL Slack](https://postgresql-slack.herokuapp.com/)** - Community support (Note: May require invitation)
- **[Gitea Community](https://gitea.com/gitea/helm-gitea)** - Gitea Helm chart community
- **[PostgreSQL Wiki](https://wiki.postgresql.org/wiki/)** - Community wiki

#### **Books & Courses**
- **"PostgreSQL: Up and Running"** by Regina Obe and Leo Hsu
- **"Gitea Documentation"** - Official Gitea guides
- **PostgreSQL Tutorials** - Official tutorial series

---

**🎉 You now have a comprehensive understanding of how PostgreSQL supports Gitea in your Kubernetes environment! Use this guide to maintain, monitor, and optimize your database infrastructure.**

---

*Last updated: $(date)*  
*Maintained by: FreeLeaps DevOps Team*