13 min read

Learning Objectives

By the end of this module, you will be able to:

  • Connect application code to DBaaS for PostgreSQL over TLS with certificate verification, using psycopg and asyncpg
  • Configure application-level connection pooling that stays inside the PostgreSQL connection limit, and decide when to enable the managed pooler
  • Implement cache-aside and write-through caching with redis-py against DBaaS for In-Memory DB
  • Connect to DBaaS for MariaDB and MongoDB with TLS, using the connection details from Terraform outputs
  • Trigger point-in-time recovery for PostgreSQL through the API and schedule logical dumps from code

Unit 4.1: Database and Caching Integration

Introduction

TaskBoard's database tier from Unit 2.5 is running: a PostgreSQL cluster and an In-Memory DB cluster in the private DB LAN. Now your API code has to use them. It must open TLS connections that verify the server, share a limited number of database connections across many requests, and keep hot reads away from the database.

PostgreSQL standby instances provide high availability, not read capacity, so every query hits the primary and the cache is your read-scaling layer. In this unit you write the PostgreSQL connection code, add pooling, build the cache on In-Memory DB, and apply the same pattern to MariaDB and MongoDB. You also wire up recovery: point-in-time recovery (PITR) through the API and scheduled dumps.

1. Connecting to DBaaS for PostgreSQL

import os
import psycopg

conn = psycopg.connect(
    host=os.environ["PG_HOST"],          # terraform output pg_host (the cluster dns_name)
    port=5432,
    dbname="taskboard",
    user="taskboard",
    password=os.environ["PG_PASSWORD"],  # from your secret store, never from Terraform state
    sslmode="verify-full",
    sslrootcert="/etc/taskboard/isrg-root-x1.pem",
)
with conn.cursor() as cur:
    cur.execute("select version()")
    print(cur.fetchone()[0])

The host is the cluster's DNS name, the dns_name attribute of ionoscloud_pg_cluster_v2 and metadata.dnsName in the API. It looks like pg-abc123.postgresql.de-txl.ionos.com. Port 5432 is the default.

1.1 TLS Is Always On: Verify the Server

All client connections are encrypted with TLS. The default SSL mode is prefer, and clients cannot disable encryption. Encryption alone does not prove that you talk to your cluster, so your code sets sslmode=verify-full.

For verify-ca and verify-full, the client needs the root certificate ISRG Root X1. The server certificates are issued by Let's Encrypt for the cluster's DNS name:

curl -s https://crt.sh/?q=9314791 > /etc/taskboard/isrg-root-x1.pem
export PGSSLROOTCERT=/etc/taskboard/isrg-root-x1.pem
export PGSSLMODE=verify-full
psql -h "$PG_HOST" -U taskboard taskboard -c 'select 1'

Client libraries must support SCRAM-SHA-256 authentication. A recent psycopg or asyncpg does. Database access uses PostgreSQL users and passwords.

1.2 Async Code with asyncpg

TaskBoard's API runs on uvicorn, so async handlers use asyncpg with an ssl.SSLContext:

import ssl, asyncpg

ctx = ssl.create_default_context(cafile="/etc/taskboard/isrg-root-x1.pem")
ctx.check_hostname = True

pool = await asyncpg.create_pool(
    host=os.environ["PG_HOST"], port=5432,
    database="taskboard", user="taskboard",
    password=os.environ["PG_PASSWORD"],
    ssl=ctx, min_size=2, max_size=10,
)
row = await pool.fetchrow("select count(*) from tasks where done = false")

2. Connection Pooling Within the Limit

The max_connections value of PostgreSQL is calculated from the RAM size of the instances:

RAM Size max_connections
4GB 384
5GB 512
6GB 640
7GB 768
8GB 896
> 8GB 1000

The platform reserves 11 of these connections for internal use. TaskBoard's cluster from Unit 2.5 has 4 GB per instance, so your application has 373 connections. Divide that budget across every process that opens a pool: API pods, the worker, migrations and your own psql sessions.

2.1 Application-Level Pooling with SQLAlchemy

from sqlalchemy import create_engine

engine = create_engine(
    f"postgresql+psycopg://taskboard:{os.environ['PG_PASSWORD']}@{os.environ['PG_HOST']}:5432/taskboard",
    connect_args={"sslmode": "verify-full", "sslrootcert": "/etc/taskboard/isrg-root-x1.pem"},
    pool_size=10,        # steady connections per process
    max_overflow=5,      # short bursts
    pool_pre_ping=True,  # drop dead connections after a failover
    pool_recycle=1800,
)

With 2 API replicas, 2 uvicorn workers each and pool_size + max_overflow = 15, the API can hold 60 connections. Scale the formula with your replica count before you raise the pool size. pool_pre_ping matters because standby nodes accept no read connections: only primary disconnections happen, and after a failover the client must reconnect.

2.2 The Managed Pooler

When your application opens many short-lived connections, or client connections may exceed max_connections, enable the managed connection pooler (pgbouncer). It is one argument on the cluster:

resource "ionoscloud_pg_cluster_v2" "taskboard" {
  # ... arguments from Unit 2.5
  connection_pooler = "TRANSACTION"   # DISABLED, TRANSACTION or SESSION
}

The pooler is reachable on the same DNS name. In TRANSACTION mode, each server connection goes back to the pool after each transaction, so avoid session-level features such as prepared statements and advisory locks. For asyncpg behind a transaction pooler, turn off its statement cache with statement_cache_size=0. For long-running analytical queries, use a direct connection instead.

3. Caching with DBaaS for In-Memory DB

In-Memory DB runs Valkey and works with Redis-compatible clients such as redis-py. Connect to the cluster's dns_name, which points to the primary instance, with TLS:

import redis

cache = redis.Redis(
    host=os.environ["CACHE_HOST"],
    username="taskboard",
    password=os.environ["CACHE_PASSWORD"],   # the plain password behind the SHA-256 hash
    ssl=True,
    ssl_ca_certs="/etc/taskboard/isrg-root-x1.pem",
    decode_responses=True,
    socket_timeout=0.5,
)
cache.ping()

All IONOS CLOUD DBaaS certificates are issued by Let's Encrypt. If your system trust store is up to date, you can omit ssl_ca_certs. Sentinel is not supported, so do not use a Sentinel client: connect to the DNS name directly.

3.1 Cache-Aside for Task Lists

import json
from sqlalchemy import text

def list_tasks(board_id: int) -> list[dict]:
    key = f"tasks:board:{board_id}"
    try:
        hit = cache.get(key)
        if hit is not None:
            return json.loads(hit)
    except redis.RedisError:
        pass  # cache down: fall through to the database

    with engine.connect() as c:
        rows = c.execute(text("select id, title, done from tasks where board_id = :b"),
                         {"b": board_id}).mappings().all()
    tasks = [dict(r) for r in rows]
    try:
        cache.set(key, json.dumps(tasks), ex=60)
    except redis.RedisError:
        pass
    return tasks

Cache-aside reads the cache first, loads from PostgreSQL on a miss and stores the result with a TTL. A cache failure degrades to database reads instead of failing the request.

3.2 Write-Through and Sessions

def update_task(task_id: int, board_id: int, done: bool) -> None:
    with engine.begin() as c:
        c.execute(text("update tasks set done = :d where id = :i"), {"d": done, "i": task_id})
    cache.delete(f"tasks:board:{board_id}")          # invalidate the list
    cache.hset(f"task:{task_id}", mapping={"done": int(done)})  # write-through the item

def save_session(sid: str, user_id: int) -> None:
    cache.set(f"session:{sid}", user_id, ex=3600)

Write to PostgreSQL first, then update or invalidate the cache. Sessions live only in the cache with a TTL. The allkeys-lru eviction policy from Unit 2.5 removes old keys when memory is full, so a session store you cannot lose needs persistence and its own keyspace planning.

You get all command categories except the "dangerous" category; INFO, KEYS and SORT are allowed.

4. MariaDB and MongoDB Clients

4.1 DBaaS for MariaDB

import mysql.connector

db = mysql.connector.connect(
    host=os.environ["MARIADB_HOST"],   # ionoscloud_mariadb_cluster_v2 dns_name
    port=3306,
    user="app", password=os.environ["MARIADB_PASSWORD"], database="app",
    ssl_ca="/etc/taskboard/isrg-root-x1.pem",
    ssl_verify_identity=True,
)

All client connections are encrypted with TLS, with server certificates issued by Let's Encrypt for the cluster's DNS name. The cluster runs on port 3306, and you cannot change it. Each user has 250 connections and max_connections is set to 500, so give each service its own user and keep its pool size well below 250.

4.2 MongoDB

from pymongo import MongoClient

client = MongoClient(
    os.environ["MONGO_URL"],          # connection_string output, mongodb+srv://m-....mongodb.de-txl.ionos.com
    username="app", password=os.environ["MONGO_PASSWORD"],
    authSource="app",
    maxPoolSize=20,
)
client.admin.command("ping")
output "mongo_url" {
  value = ionoscloud_mongo_cluster.reports.connection_string
}

The connection_string uses the mongodb+srv format, and client-to-cluster communication is secured with TLS using Let's Encrypt. The +srv URL lets the driver discover the replica set members, so you do not list hosts or a replica set name yourself. There is no managed pooler for MongoDB, so maxPoolSize in each client is your only control. Connect from a VM or node pool in the cluster's LAN.

5. Recovery: PITR and Scheduled Dumps

Backup Service does not back up managed DBaaS clusters. Your recovery tools are the built-in PITR backups and logical dumps that you schedule yourself.

5.1 Point-in-Time Recovery via the API

PITR restores a cluster to any moment within the backup window. Backup retention is configurable from 1 to 365 days. First find an active backup:

API=https://postgresql.de-txl.ionos.com/v2
curl -s -H "Authorization: Bearer $IONOS_TOKEN" \
  "$API/backups?filter.clusterId=$CLUSTER_ID" \
  | jq '.items[] | {id, active: .properties.isActive,
        from: .properties.earliestRecoveryTargetTime, to: .properties.latestRecoveryTargetTime}'

Two restore paths exist. To verify data before replacing anything, create a new cluster with restoreFromBackup holding sourceBackupId and an optional recoveryTargetDatetime. In Terraform, this is the restore_from_backup block of ionoscloud_pg_cluster_v2.

To restore the existing cluster in place, send the full cluster object with only recoveryTargetDatetime:

import requests

def restore_in_place(cluster_id: str, target_iso: str) -> None:
    url = f"{API}/clusters/{cluster_id}"
    h = {"Authorization": f"Bearer {os.environ['IONOS_TOKEN']}"}
    props = requests.get(url, headers=h, timeout=30).json()["properties"]
    props["restoreFromBackup"] = {"recoveryTargetDatetime": target_iso}  # no sourceBackupId here
    r = requests.put(url, headers=h, json={"properties": props}, timeout=30)
    r.raise_for_status()   # 200, cluster goes UPDATING, then AVAILABLE

The PostgreSQL v2 API uses an Ensure pattern: a PUT replaces the configuration, and any field you omit resets to its default. That is why the code sends the full properties. An in-place restore overwrites all current data and cannot be undone. Poll metadata.state until it is AVAILABLE, as in Unit 1.1.

5.2 Scheduled Logical Dumps

apiVersion: batch/v1
kind: CronJob
metadata: { name: taskboard-pg-dump }
spec:
  schedule: "15 2 * * *"
  jobTemplate:
    spec:
      template:
        spec:
          restartPolicy: OnFailure
          volumes: [{ name: dump, emptyDir: {} }]
          initContainers:
            - name: pg-dump
              image: postgres:17
              envFrom: [{ secretRef: { name: taskboard-db } }]   # DATABASE_URL from Unit 3.2
              command: ["sh", "-c", "pg_dump -F c -f /dump/tb.dump \"$DATABASE_URL\""]
              volumeMounts: [{ name: dump, mountPath: /dump }]
          containers:
            - name: upload
              image: amazon/aws-cli
              envFrom: [{ secretRef: { name: taskboard-s3 } }]   # AWS_ACCESS_KEY_ID, AWS_SECRET_ACCESS_KEY
              command: ["sh", "-c", "aws s3 cp /dump/tb.dump s3://taskboard-backups-7f3a/pg/$(date +%F).dump --endpoint-url https://s3.eu-central-3.ionoscloud.com"]
              volumeMounts: [{ name: dump, mountPath: /dump }]

Superuser access is not available on the cluster, so dump per database with pg_dump instead of pg_dumpall. Use a client image whose major version is at least your cluster's. The upload container writes the dump to IONOS Cloud Object Storage with an Access Key and Secret Key, which Unit 4.2 covers. For MariaDB, the equivalent tool is mariadb-dump.

API Reference Quick Card

Key PostgreSQL v2 API endpoints for this unit:

Method Endpoint Description
GET /clusters/{clusterId} Read metadata.dnsName, metadata.state and the full configuration
GET /backups?filter.clusterId={clusterId} List backups with isActive and the recovery window
POST /clusters Create a cluster; with restoreFromBackup.sourceBackupId it restores into a new cluster
PUT /clusters/{clusterId} Ensure the full configuration; with restoreFromBackup.recoveryTargetDatetime it restores in place

Base URL: https://postgresql.de-txl.ionos.com/v2 (regional, for example https://postgresql.de-fra.ionos.com/v2) Authentication: Authorization: Bearer <token> (token authentication only)

Code Lab

Objective: Connect the TaskBoard API to PostgreSQL and In-Memory DB, insert tasks, cache reads, and prove that the connections are TLS-verified.

Prerequisites:

  • The database tier from Unit 2.5, applied, and the API server with a NIC in the DB LAN
  • Python 3.12 on the API server with psycopg[binary], sqlalchemy and redis
  • PG_PASSWORD and CACHE_PASSWORD from your secret store

Step 1: Export the connection details

export PG_HOST=$(terraform output -raw pg_host)
export CACHE_HOST=$(terraform output -raw cache_host)
curl -s https://crt.sh/?q=9314791 > isrg-root-x1.pem && head -1 isrg-root-x1.pem

Expected output:

-----BEGIN CERTIFICATE-----

Step 2: Verify TLS to PostgreSQL

psql "host=$PG_HOST dbname=taskboard user=taskboard sslmode=verify-full sslrootcert=isrg-root-x1.pem" \
  -tc "select ssl from pg_stat_ssl where pid = pg_backend_pid();"

Expected output:

 t

Step 3: Create the schema and insert tasks

import os
from sqlalchemy import create_engine, text
PG_HOST, PG_PASSWORD = os.environ["PG_HOST"], os.environ["PG_PASSWORD"]
engine = create_engine(f"postgresql+psycopg://taskboard:{PG_PASSWORD}@{PG_HOST}:5432/taskboard",
    connect_args={"sslmode": "verify-full", "sslrootcert": "isrg-root-x1.pem"}, pool_pre_ping=True)
with engine.begin() as c:
    c.execute(text("create table if not exists tasks (id serial primary key, board_id int, title text, done bool default false)"))
    c.execute(text("insert into tasks (board_id, title) values (1, 'Write unit tests'), (1, 'Ship v1')"))
print("inserted")

Expected output:

inserted

Step 4: Connect to the cache

import redis
CACHE_HOST, CACHE_PASSWORD = os.environ["CACHE_HOST"], os.environ["CACHE_PASSWORD"]
cache = redis.Redis(host=CACHE_HOST, username="taskboard", password=CACHE_PASSWORD,
                    ssl=True, ssl_ca_certs="isrg-root-x1.pem", decode_responses=True)
print(cache.ping())

Expected output:

True

Step 5: Read through the cache twice

Add list_tasks() from Section 3.1, then time two calls:

import time
for i in range(2):
    cached = cache.exists("tasks:board:1")
    t = time.perf_counter(); n = len(list_tasks(1))
    print(i, n, "hit" if cached else "miss", f"{(time.perf_counter()-t)*1000:.1f} ms")

Expected output:

0 2 miss ... ms
1 2 hit ... ms

Step 6: Invalidate on write

update_task(task_id=1, board_id=1, done=True)
print(cache.exists("tasks:board:1"), list_tasks(1)[0]["done"])

Expected output:

0 True

Validation Checklist:

  • [ ] pg_stat_ssl shows ssl = t for the API's connection
  • [ ] The second list_tasks() call is served from the cache
  • [ ] An update removes the cached list, and the next read returns the new value
  • [ ] No password appears in code, Terraform state or Git

Cleanup:

psql "host=$PG_HOST dbname=taskboard user=taskboard sslmode=verify-full sslrootcert=isrg-root-x1.pem" -c "drop table tasks;"

Keep the clusters for Units 4.3 and 4.4, or remove them with terraform destroy -auto-approve.

Common Pitfalls

Developer mistakes to avoid with this unit's topic:

  1. Read traffic sent to a "replica"

    • Problem: You configure a second engine for reads against a standby, and it never connects, or all load still lands on one node.
    • Why it happens: Standby instances serve high availability, and read connections to standby nodes are not allowed. There are no read replicas.
    • Fix: Use one engine against the DNS name and scale reads with the cache (Section 3).
  2. Pools that exhaust max_connections

    • Problem: After scaling the API Deployment, new requests fail with FATAL: sorry, too many clients already.
    • Why it happens: Each pod multiplies pool_size + max_overflow, and the total exceeds the RAM-based limit minus the 11 reserved connections.
    • Fix: Budget connections per process (Section 2.1), raise instance RAM, or enable the managed pooler in TRANSACTION mode.
  3. In-place restore rejected

    • Problem: Your restore PUT returns 400, or a later PUT silently resets settings.
    • Why it happens: An in-place restore accepts only recoveryTargetDatetime; sourceBackupId is valid only on creation. The Ensure pattern also resets every omitted field.
    • Fix: Send the full properties from a fresh GET and only {"recoveryTargetDatetime": ...} in restoreFromBackup.

Summary

You can now connect TaskBoard to its managed databases the way production code needs: TLS with full certificate verification, pooled connections that respect the RAM-based limit, and a Valkey cache that absorbs read traffic. MariaDB and MongoDB use the same ingredients. For recovery, you restore through the PostgreSQL v2 API and schedule pg_dump yourself.

Key Points:

  • Connect with sslmode=verify-full and ISRG Root X1; encryption is always on, verification is your job
  • PostgreSQL standbys accept no read connections, so the cache is the read-scaling layer
  • Budget pools against max_connections minus the 11 reserved connections; enable pgbouncer for many short connections
  • In-Memory DB clients connect to the DNS name with TLS; Sentinel is not supported
  • Restore in place with PUT and recoveryTargetDatetime only; Backup Service does not cover DBaaS, so schedule dumps

Important Terminology:

  • verify-full: The libpq SSL mode that checks the certificate chain and that the certificate matches the host name
  • Connection budget: The share of max_connections that one process may hold through its pool
  • Cache-aside: Read from the cache, load from the database on a miss, then store with a TTL
  • Write-through: Update the database, then write or invalidate the matching cache entries in the same code path
  • In-place restore: A PITR that overwrites the existing cluster's data with its state at recoveryTargetDatetime

Next Steps

Continue Learning: Unit 4.2: Object Storage Integration

Related Topics: