06 · Cloud Databases¶

Databases for PostgreSQL is one of several fully-managed engines under IBM Cloud's "Databases for..." family (also MySQL, MongoDB, Redis, Elasticsearch, and more) — IBM handles patching, backups, and failover, you handle schema and queries. This module provisions an instance and connects an app to it, the same database the capstone in Module 10 will use.

Provision a PostgreSQL instance¶

ibmcloud resource service-instance-create mastery-postgres \
  databases-for-postgresql standard us-south \
  --resource-group-name mastery-path \
  -p '{"members_memory_allocation_mb": 1024, "members_disk_allocation_mb": 5120}'

# Provisioning takes several minutes -- poll status
ibmcloud resource service-instance mastery-postgres

Get connection credentials¶

ibmcloud resource service-key-create mastery-postgres-cred Administrator \
  --instance-name mastery-postgres

ibmcloud resource service-key mastery-postgres-cred --output json

The key contains a connection.postgres block with hosts, authentication (username/password), the default database name, and a certificate (base64 CA cert) — PostgreSQL deployments require TLS by default.

# Save the CA cert locally so psql/your driver can verify the connection
echo "<certificate_base64>" | base64 -d > mastery-postgres-ca.crt

Connect with `psql`¶

psql "host=<hostname> port=<port> dbname=ibmclouddb \
  user=<username> password=<password> \
  sslmode=verify-full sslrootcert=mastery-postgres-ca.crt"

CREATE TABLE visits (
    id SERIAL PRIMARY KEY,
    path TEXT NOT NULL,
    visited_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

INSERT INTO visits (path) VALUES ('/');
SELECT * FROM visits;

Connecting from application code (Node.js example)¶

// db.js
const { Pool } = require("pg");
const fs = require("fs");

const pool = new Pool({
  host: process.env.PGHOST,
  port: process.env.PGPORT,
  database: "ibmclouddb",
  user: process.env.PGUSER,
  password: process.env.PGPASSWORD,
  ssl: {
    ca: fs.readFileSync("./mastery-postgres-ca.crt").toString(),
    rejectUnauthorized: true,
  },
});

async function recordVisit(path) {
  const result = await pool.query(
    "INSERT INTO visits (path) VALUES ($1) RETURNING id, visited_at",
    [path]
  );
  return result.rows[0];
}

module.exports = { recordVisit };

Credentials come from environment variables sourced from the service key — never hardcode them, and never commit the CA cert alongside secrets in a public repo.

Backups and scaling¶

# On-demand backup (automatic daily backups are already included)
ibmcloud cdb deployment-backup mastery-postgres

# List available backups / point-in-time recovery windows
ibmcloud cdb deployment-backups mastery-postgres

# Scale memory/disk (triggers a rolling, minimal-downtime resize)
ibmcloud cdb deployment-scale mastery-postgres \
  --memory 2048 --disk 10240

How It Actually Works¶

IBM Cloud Databases (ICD) is a multi-tenant control plane wrapped around single-tenant data planes — each deployment gets its own set of VSI-backed compute and storage, never a shared database process with other customers' data, which is what lets ICD offer real memory/disk/CPU sizing and guaranteed resource isolation rather than the noisy-neighbor risk of a shared cluster; the "managed" part is IBM's control plane automating provisioning, patching, and failover around that dedicated footprint.
A high-availability deployment isn't one database with a backup copy — it's a primary plus one or more standby replicas continuously applying the primary's write-ahead log (or equivalent replication stream), with a health-checking control plane watching heartbeats. Failover is the control plane detecting the primary stopped responding, promoting the most caught-up standby to primary, and repointing the connection endpoint's DNS/routing — which is why a failover briefly interrupts connections (clients must reconnect to the same endpoint) rather than losing the endpoint address itself.
Scaling memory/disk/CPU is not a live resize of the running process — it's the control plane provisioning new, larger-specced compute, replicating the current data onto it, and cutting the endpoint over, which is why scaling operations take real minutes and briefly interrupt connections even though the deployment name and connection string never change.
Point-in-time backups work by combining periodic full snapshots with continuously archived write-ahead logs — restoring "to 3:14pm yesterday" means the control plane loads the nearest snapshot before that time and replays the log forward exactly to that timestamp, rather than IBM having taken a snapshot at that exact second.

Cheat sheet¶

Command	Purpose
`ibmcloud resource service-instance-create <name> databases-for-postgresql <plan> <region>`	Provision a PostgreSQL deployment
`ibmcloud resource service-key-create <key> Administrator --instance-name <db>`	Get connection credentials
`ibmcloud cdb deployment-backup <name>`	Trigger an on-demand backup
`ibmcloud cdb deployment-backups <name>`	List backups
`ibmcloud cdb deployment-scale <name> --memory <mb> --disk <mb>`	Resize a deployment
`ibmcloud resource service-instance-delete <name>`	Deprovision the deployment

Exercise¶

Provision a databases-for-postgresql instance on the standard plan, create a service key, extract the CA certificate, and connect with psql using sslmode=verify-full. Create the visits table above, insert three rows with different path values, then write a SELECT ... GROUP BY path query that counts visits per path.