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],sqlalchemyandredis PG_PASSWORDandCACHE_PASSWORDfrom 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_sslshowsssl = tfor 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:
-
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).
-
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
TRANSACTIONmode.
- Problem: After scaling the API Deployment, new requests fail with
-
In-place restore rejected
- Problem: Your restore
PUTreturns400, or a laterPUTsilently resets settings. - Why it happens: An in-place restore accepts only
recoveryTargetDatetime;sourceBackupIdis valid only on creation. The Ensure pattern also resets every omitted field. - Fix: Send the full properties from a fresh
GETand only{"recoveryTargetDatetime": ...}inrestoreFromBackup.
- Problem: Your restore
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-fulland 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_connectionsminus 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
PUTandrecoveryTargetDatetimeonly; 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_connectionsthat 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: