07: PostgreSQL Architecture, Deployment & Operations¶

7.1 Introduction¶

PostgreSQL is the primary relational database engine used in LocalCloudLab. It provides:

• ACID-compliant transactions
• Rich SQL capabilities
• JSON support
• Full-text search
• Strong indexing capabilities
• Excellent performance in microservice architectures
• Native compatibility with .NET via Npgsql

In this section, we will cover:

• Installing PostgreSQL on Kubernetes (using Helm)
• Understanding the storage model and persistence
• Connecting microservices (.NET APIs) to PostgreSQL
• Running schema migrations safely
• Monitoring database health and performance
• Backups and restore strategies
• Best practices for production-like deployments

By the end of Section 7, your database layer will be fully operational, persistent, observable, and integrated with the LocalCloudLab cluster.

7.2 PostgreSQL Architecture in LocalCloudLab¶

Your environment uses single-node k3s, therefore PostgreSQL is deployed:

• As a StatefulSet in Kubernetes
• With a PersistentVolumeClaim for data durability
• Behind a ClusterIP service within the cluster

The typical flow:

    +-------------------+
    |   .NET API        |
    | (Search / Checkin)|
    +---------+---------+
              |
              | Npgsql (pooling, SSL, tracing)
              v
    +---------+---------+
    | PostgreSQL        |
    | StatefulSet + PVC |
    +-------------------+
              |
              | Persistent storage
              v
    +-------------------+
    |   Disk / Volume   |
    +-------------------+

Why PostgreSQL?¶

✔ Stable and proven
✔ Excellent for relational workloads
✔ Very strong ecosystem with .NET
✔ Easy horizontal read-scaling (future option)
✔ Strong indexing & performance tooling
✔ Easy migration from MS SQL when done correctly

7.3 Installing PostgreSQL with Helm (Bitnami Chart)¶

We use Bitnami because:

• The chart is production-quality
• Well-maintained
• Supports backup options
• Has built-in replication (optional)
• Works great with k3s

7.3.1 Add the Helm repo¶

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

7.3.2 Create namespace “database”¶

kubectl create namespace database

7.3.3 Install PostgreSQL¶

helm install postgres bitnami/postgresql       -n database       --set auth.postgresPassword=YourStrongPassword       --set primary.persistence.size=20Gi       --set primary.persistence.storageClass="local-path"

Explanation of parameters:

• postgresPassword → sets the superuser password
• persistence.size → defines disk size
• storageClass → uses k3s local-path storage

Check installation:

kubectl get pods -n database
kubectl get pvc -n database
kubectl get svc -n database

Expected service:

postgres-postgresql.database.svc.cluster.local

7.4 Connecting .NET APIs to PostgreSQL¶

Your .NET microservices (Search API, Checkin API) communicate with PostgreSQL via the Npgsql driver.

7.4.1 Connection string format¶

The Kubernetes DNS name for the DB is:

Host=postgres-postgresql.database.svc.cluster.local;Port=5432;
Database=appdb;Username=postgres;Password=YourStrongPassword;

Add SSL Mode if required (optional):

SSL Mode=Disable

7.4.2 Registering DbContext in .NET¶

Example (Search API):

builder.Services.AddDbContext<SearchDbContext>(options =>
    options.UseNpgsql(builder.Configuration.GetConnectionString("Postgres")));

7.4.3 Performance recommendations¶

• Set MaxPoolSize in connection string
• Avoid long blocking transactions
• Use async APIs everywhere
• Index frequently queried columns
• Avoid selecting entire rows unnecessarily

7.4.4 Checking connectivity¶

Run inside the cluster:

kubectl exec -it <search-pod> -- bash
apt-get update && apt-get install -y postgresql-client
psql -h postgres-postgresql.database.svc.cluster.local -U postgres

If the login succeeds, your cluster networking is correct.

(End of Part 1 — Part 2 will continue with migrations, backups, monitoring, and failover.)