Setup PostgreSQL Database¶

How to deploy PostgreSQL for Mailu using either a simple Helm chart or production-grade CloudNativePG.

Problem¶

Mailu requires a PostgreSQL database for storing user accounts, domains, aliases, and configuration. You need to deploy PostgreSQL before deploying Mailu.

Solution Options¶

Choose the approach that matches your needs:

Approach	Use Case	HA	Backups	Complexity
Bitnami PostgreSQL	Development, testing, small deployments	Optional	Manual	Low
CloudNativePG (CNPG)	Production, HA required	Built-in	Automatic	Medium

Option 1: Bitnami PostgreSQL (Simple)¶

Prerequisites¶

Helm 3 installed
Kubernetes cluster with persistent storage

Step 1: Add Bitnami Helm Repository¶

helm repo add bitnami https://charts.bitnami.com/bitnami
helm repo update

Step 2: Create Values File¶

Create postgres-values.yaml:

# Bitnami PostgreSQL configuration for Mailu

auth:
  database: mailu
  username: mailu
  password: "CHANGE_ME_SECURE_PASSWORD"  # Change this!
  postgresPassword: "CHANGE_ME_ADMIN_PASSWORD"  # Change this!

primary:
  persistence:
    enabled: true
    size: 20Gi
    storageClass: longhorn  # Your storage class

  resources:
    requests:
      cpu: 250m
      memory: 512Mi
    limits:
      cpu: 1000m
      memory: 1Gi

  # Optional: Configure backups
  # initdb:
  #   scripts:
  #     setup_backup.sh: |
  #       #!/bin/bash
  #       # Backup configuration here

metrics:
  enabled: true
  resources:
    requests:
      cpu: 50m
      memory: 64Mi

Security note: Generate secure passwords:

# Generate random passwords
MAILU_DB_PASSWORD=$(openssl rand -base64 32)
POSTGRES_ADMIN_PASSWORD=$(openssl rand -base64 32)

echo "Mailu DB password: $MAILU_DB_PASSWORD"
echo "PostgreSQL admin password: $POSTGRES_ADMIN_PASSWORD"

# Update values file with these passwords

Step 3: Install PostgreSQL¶

# Create namespace
kubectl create namespace postgres

# Install PostgreSQL
helm install postgres bitnami/postgresql \
  --namespace postgres \
  --values postgres-values.yaml

# Wait for pod to be ready
kubectl wait --for=condition=ready pod -n postgres -l app.kubernetes.io/name=postgresql --timeout=300s

Step 4: Verify Installation¶

# Check pod status
kubectl get pods -n postgres

# Test database connection
kubectl exec -n postgres postgres-postgresql-0 -- \
  psql -U mailu -d mailu -c "SELECT version();"

Step 5: Get Connection Details¶

# Get service name (for Mailu configuration)
kubectl get svc -n postgres

# Service name will be: postgres-postgresql
# Connection string: postgres-postgresql.postgres.svc.cluster.local:5432

Step 6: Create Kubernetes Secret for Mailu¶

# Create secret with database credentials
kubectl create secret generic postgres-credentials \
  --namespace=mailu \
  --from-literal=username="mailu" \
  --from-literal=password="$MAILU_DB_PASSWORD"

Configure Mailu to Use This Database¶

In your CDK8S MailuChart configuration:

database: {
  type: 'postgresql',
  postgresql: {
    host: 'postgres-postgresql.postgres.svc.cluster.local',
    port: 5432,
    database: 'mailu',
    secretName: 'postgres-credentials',
    secretKeys: {
      username: 'username',
      password: 'password',
    },
  },
}

Option 2: CloudNativePG (Production)¶

Prerequisites¶

CloudNativePG operator installed
S3-compatible storage for backups (optional but recommended)
Kubernetes cluster with persistent storage

Step 1: Install CloudNativePG Operator¶

# Add CNPG Helm repository
helm repo add cnpg https://cloudnative-pg.github.io/charts
helm repo update

# Install operator
helm install cnpg-operator cnpg/cloudnative-pg \
  --namespace cnpg-system \
  --create-namespace

# Verify operator is running
kubectl get pods -n cnpg-system

Step 2: Create PostgreSQL Cluster¶

Create postgres-cluster.yaml:

apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: postgres
  namespace: postgres
spec:
  instances: 3  # High availability with 3 replicas

  postgresql:
    parameters:
      max_connections: "200"
      shared_buffers: "256MB"
      effective_cache_size: "1GB"
      maintenance_work_mem: "64MB"
      checkpoint_completion_target: "0.9"
      wal_buffers: "16MB"
      default_statistics_target: "100"
      random_page_cost: "1.1"
      effective_io_concurrency: "200"

  bootstrap:
    initdb:
      database: mailu
      owner: mailu
      secret:
        name: postgres-app

  storage:
    size: 20Gi
    storageClass: longhorn

  resources:
    requests:
      cpu: 250m
      memory: 512Mi
    limits:
      cpu: 1000m
      memory: 1Gi

  # Monitoring
  monitoring:
    enablePodMonitor: true

  # Connection pooler (recommended)
  enableSuperuserAccess: true

  # Automatic backup to S3 (optional but recommended)
  # backup:
  #   barmanObjectStore:
  #     destinationPath: s3://backup-bucket/postgres-mailu/
  #     endpointURL: https://s3.your-region.amazonaws.com
  #     s3Credentials:
  #       accessKeyId:
  #         name: s3-credentials
  #         key: access-key-id
  #       secretAccessKey:
  #         name: s3-credentials
  #         key: secret-access-key
  #     wal:
  #       compression: gzip
  #   retentionPolicy: "30d"

---
apiVersion: v1
kind: Secret
metadata:
  name: postgres-app
  namespace: postgres
type: kubernetes.io/basic-auth
stringData:
  username: mailu
  password: "CHANGE_ME_SECURE_PASSWORD"  # Change this!

Generate secure password:

MAILU_DB_PASSWORD=$(openssl rand -base64 32)
echo "Mailu DB password: $MAILU_DB_PASSWORD"

# Update password in postgres-cluster.yaml

Step 3: Create Connection Pooler (Recommended)¶

Create postgres-pooler.yaml:

apiVersion: postgresql.cnpg.io/v1
kind: Pooler
metadata:
  name: postgres-pooler
  namespace: postgres
spec:
  cluster:
    name: postgres

  instances: 2  # Multiple pooler instances for HA
  type: rw  # Read-write pooler

  pgbouncer:
    poolMode: session
    parameters:
      max_client_conn: "1000"
      default_pool_size: "25"

  template:
    metadata:
      labels:
        app: postgres-pooler
    spec:
      containers:
      - name: pgbouncer
        resources:
          requests:
            cpu: 50m
            memory: 64Mi
          limits:
            cpu: 200m
            memory: 128Mi

Step 4: Deploy PostgreSQL¶

# Create namespace
kubectl create namespace postgres

# Apply cluster configuration
kubectl apply -f postgres-cluster.yaml

# Wait for cluster to be ready (may take 2-3 minutes)
kubectl wait --for=condition=Ready cluster -n postgres postgres --timeout=600s

# Apply pooler (optional but recommended)
kubectl apply -f postgres-pooler.yaml

# Verify cluster status
kubectl get cluster -n postgres
kubectl get pods -n postgres

Step 5: Verify Installation¶

# Check cluster status
kubectl describe cluster -n postgres postgres

# Test database connection (via pooler)
kubectl exec -n postgres postgres-1 -- \
  psql -U mailu -d mailu -c "SELECT version();"

# Check replication status
kubectl exec -n postgres postgres-1 -- \
  psql -U postgres -c "SELECT * FROM pg_stat_replication;"

Step 6: Get Connection Details¶

# List services
kubectl get svc -n postgres

# Services available:
# - postgres-rw (read-write service, direct to primary)
# - postgres-r (read-only service, load balanced across replicas)
# - postgres-pooler (connection pooler, recommended)

# Use pooler for best performance and connection management

Configure Mailu to Use CNPG¶

In your CDK8S MailuChart configuration:

database: {
  type: 'postgresql',
  postgresql: {
    host: 'postgres-pooler',  // Use pooler service
    // or: host: 'postgres-rw',  // Direct to primary
    port: 5432,
    database: 'mailu',
    secretName: 'postgres-app',  // CNPG auto-generated secret
    secretKeys: {
      username: 'username',
      password: 'password',
    },
  },
}

Note: CNPG automatically creates the postgres-app secret. You can reference it directly in Mailu configuration.

Configure Automatic Backups (CNPG Only)¶

Prerequisites for S3 Backups¶

Create S3 credentials secret:

kubectl create secret generic s3-credentials \
  --namespace=postgres \
  --from-literal=access-key-id="YOUR_S3_ACCESS_KEY" \
  --from-literal=secret-access-key="YOUR_S3_SECRET_KEY"

Enable Scheduled Backups¶

Add to postgres-cluster.yaml:

spec:
  # ... existing spec ...

  backup:
    barmanObjectStore:
      destinationPath: s3://backup-bucket/postgres-mailu/
      endpointURL: https://s3.your-region.amazonaws.com
      s3Credentials:
        accessKeyId:
          name: s3-credentials
          key: access-key-id
        secretAccessKey:
          name: s3-credentials
          key: secret-access-key
      wal:
        compression: gzip
        maxParallel: 2
    retentionPolicy: "30d"

  # Schedule daily backups at 2 AM
  scheduledBackup:
  - name: daily-backup
    schedule: "0 2 * * *"
    backupOwnerReference: self

Apply changes:

kubectl apply -f postgres-cluster.yaml

# Trigger manual backup to test
kubectl cnpg backup postgres -n postgres

Comparison: Bitnami vs CNPG¶

Bitnami PostgreSQL¶

Pros:

✅ Simple setup with Helm
✅ Familiar Helm chart configuration
✅ Quick deployment (< 2 minutes)
✅ Good for development/testing

Cons:

❌ No built-in HA (single instance by default)
❌ Manual backup configuration
❌ Limited automation
❌ No connection pooling

Best for: Development, testing, small deployments, learning

CloudNativePG¶

Pros:

✅ Native Kubernetes operator
✅ Built-in HA (3 replicas)
✅ Automatic failover
✅ Continuous backup to S3
✅ Point-in-time recovery (PITR)
✅ Connection pooling (PgBouncer)
✅ Rolling updates
✅ Declarative configuration

Cons:

❌ More complex setup
❌ Requires operator installation
❌ Longer initial deployment time
❌ More moving parts to understand

Best for: Production, HA requirements, large deployments, critical workloads

Troubleshooting¶

Bitnami PostgreSQL Issues¶

Pod not starting:

# Check pod status
kubectl describe pod -n postgres postgres-postgresql-0

# Check logs
kubectl logs -n postgres postgres-postgresql-0

# Common causes:
# - PVC not bound (storage class issue)
# - Resource limits too low
# - Init scripts failing

Cannot connect to database:

# Verify service exists
kubectl get svc -n postgres postgres-postgresql

# Test connection from pod
kubectl run -it --rm debug --image=postgres:15 --restart=Never -- \
  psql -h postgres-postgresql.postgres.svc.cluster.local -U mailu -d mailu

# Check password is correct
kubectl get secret -n postgres postgres-postgresql -o jsonpath='{.data.password}' | base64 -d

CloudNativePG Issues¶

Cluster not becoming ready:

# Check cluster status
kubectl describe cluster -n postgres postgres

# Check operator logs
kubectl logs -n cnpg-system -l app.kubernetes.io/name=cloudnative-pg

# Check pod logs
kubectl logs -n postgres postgres-1

Backup failing:

# Check backup status
kubectl get backup -n postgres

# Check S3 credentials
kubectl get secret -n postgres s3-credentials -o yaml

# Test S3 connectivity
kubectl exec -n postgres postgres-1 -- \
  barman-cloud-wal-archive --test s3://backup-bucket/postgres-mailu/

Pooler connection issues:

# Check pooler status
kubectl get pooler -n postgres
kubectl logs -n postgres -l app=postgres-pooler

# Test direct connection (bypass pooler)
kubectl exec -n postgres postgres-1 -- \
  psql -U mailu -d mailu -c "SELECT 1;"

Monitoring and Maintenance¶

Check Database Size¶

# Bitnami
kubectl exec -n postgres postgres-postgresql-0 -- \
  psql -U postgres -c "SELECT pg_size_pretty(pg_database_size('mailu'));"

# CNPG
kubectl exec -n postgres postgres-1 -- \
  psql -U postgres -c "SELECT pg_size_pretty(pg_database_size('mailu'));"

Check Active Connections¶

kubectl exec -n postgres <pod-name> -- \
  psql -U postgres -c "SELECT count(*) FROM pg_stat_activity;"

Vacuum Database (Maintenance)¶

kubectl exec -n postgres <pod-name> -- \
  psql -U postgres -d mailu -c "VACUUM ANALYZE;"

Migration Between Solutions¶

Migrate from Bitnami to CNPG¶

Backup Bitnami database:

kubectl exec -n postgres postgres-postgresql-0 -- \
  pg_dump -U postgres -d mailu > mailu-backup.sql

Deploy CNPG cluster (follow Option 2 above)
Restore to CNPG:

cat mailu-backup.sql | kubectl exec -i -n postgres postgres-1 -- \
  psql -U postgres -d mailu

Update Mailu configuration to point to CNPG
Test and verify, then remove Bitnami deployment

Setup PostgreSQL Database¶

Problem¶

Solution Options¶

Option 1: Bitnami PostgreSQL (Simple)¶

Prerequisites¶

Step 1: Add Bitnami Helm Repository¶

Step 2: Create Values File¶

Step 3: Install PostgreSQL¶

Step 4: Verify Installation¶

Step 5: Get Connection Details¶

Step 6: Create Kubernetes Secret for Mailu¶

Configure Mailu to Use This Database¶

Option 2: CloudNativePG (Production)¶

Prerequisites¶

Step 1: Install CloudNativePG Operator¶

Step 2: Create PostgreSQL Cluster¶

Step 3: Create Connection Pooler (Recommended)¶

Step 4: Deploy PostgreSQL¶

Step 5: Verify Installation¶

Step 6: Get Connection Details¶

Configure Mailu to Use CNPG¶

Configure Automatic Backups (CNPG Only)¶

Prerequisites for S3 Backups¶

Enable Scheduled Backups¶

Comparison: Bitnami vs CNPG¶

Bitnami PostgreSQL¶

CloudNativePG¶

Troubleshooting¶

Bitnami PostgreSQL Issues¶

CloudNativePG Issues¶

Monitoring and Maintenance¶

Check Database Size¶

Check Active Connections¶

Vacuum Database (Maintenance)¶

Migration Between Solutions¶

Migrate from Bitnami to CNPG¶

See Also¶