Lab 4 companion: PostgreSQL setup and inspection¶

Use this page for infrastructure setup while working on Track B. The assignment's required behaviour and POI read-back checks remain in Lab 4. Commands below use a Bash-style shell; adapt environment-variable and password-prompt syntax if using PowerShell.

Local PostgreSQL and pgAdmin¶

Add PostgreSQL to your existing Compose file. This is an infrastructure starting point, not a replacement for the gamehost services you already have:

services:
  postgres:
    image: gitlab.stud.atlantis.ugent.be:5050/devops/images/postgres:18.6
    environment:
      POSTGRES_DB: colonyops
      POSTGRES_USER: colony-user
      POSTGRES_PASSWORD: colony-password
    ports:
      - "127.0.0.1:5432:5432"
    volumes:
      - postgres-data:/var/lib/postgresql
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U colony-user -d colonyops"]
      interval: 5s
      timeout: 3s
      retries: 10

volumes:
  postgres-data:

The image comes from the course mirrors to avoid Docker Hub pull limits. These are deliberately public credentials for a disposable local setup, bound to localhost. No .env file or password export is needed. Staging uses different passwords supplied through Secrets, not these defaults.

docker compose up -d --wait postgres

PostgreSQL 18 images use versioned data directories under /var/lib/postgresql, which is why the volume mounts there. See the official image's storage notes. The named volume survives container replacement; do not use down -v for routine stopping.

Connect the application¶

Add the org.postgresql:postgresql dependency with runtime scope; Spring Boot manages its version. In application-postgres.yaml, configure a PostgreSQL URL, username, password, driver, and schema strategy. Use local defaults that match Compose, with environment overrides for CI and staging:

spring:
  datasource:
    url: ${DB_URL:jdbc:postgresql://localhost:5432/colonyops}
    username: ${DB_USER:colony-user}
    password: ${DB_PASSWORD:colony-password}
    driver-class-name: org.postgresql.Driver
  jpa:
    hibernate:
      ddl-auto: update

Start the application with the PostgreSQL profile; the local defaults work without extra variables:

./mvnw spring-boot:run -Dspring-boot.run.profiles=postgres

Confirm the active profile and PostgreSQL connection in logs. Run without that profile too: the existing H2 configuration must still work. Overriding just the URL while inheriting org.h2.Driver from Lab 3 will not work; profile-specific settings must agree.

update is a pragmatic lab setting, not a dependable production migration strategy. It does not remove every obsolete column or safely transform arbitrary existing data when an entity changes.

Inspect with pgAdmin¶

Install pgAdmin 4 as a desktop application on your laptop, not as a Compose service. You do not need to install a local PostgreSQL server: Compose runs it. An existing IDE database integration is also fine if it lets you inspect tables and run SQL. IntelliJ IDEA has this but only in the Ultimate edition (which you can enable with a free student license). The free Community edition does not include database tools.

Open pgAdmin and register the local database. Use these settings for your two connections; staging requires the port-forward below:

Field	Local Compose database	Staging via port-forward
Host	`127.0.0.1`	`127.0.0.1`
Port	`5432`	`15432`
Maintenance database	`colonyops`	`colonyops`
Username	`colony-user`	`colonyops`
Password	`colony-password`	Application Secret password

The desktop client reaches Compose through the published host port, not the postgres Compose DNS name. Clearly name local and staging connections. See pgAdmin's server dialog and Query Tool.

Expand Databases → colonyops → Schemas → public → Tables. Inspect column types, constraints, and actual rows after sending requests to your API. Are enums stored as names? Does mission isolation hold? Do the POI rows remain after an application restart, and can your DAO read them back?

PostgreSQL tests in CI¶

The ordinary verify job keeps running against H2. A separate PostgreSQL job can attach gitlab.stud.atlantis.ugent.be:5050/devops/images/postgres:18.6 as a GitLab service with alias postgres; start from GitLab's PostgreSQL example and the course mirror example.

Set POSTGRES_DB=colonyops, POSTGRES_USER=colony-user, and POSTGRES_PASSWORD=colony-password on the disposable service. These initialize the database, not your application; these public test credentials are fine here too.
Configure the application's DB_URL=jdbc:postgresql://postgres:5432/colonyops, DB_USER, and DB_PASSWORD to match. CI's localhost is the job container, not the database service.
No separately provisioned password or protected CI variable is needed for this disposable database. Never use staging credentials here.
Check database readiness rather than adding an arbitrary sleep. A TCP-ready container does not prove the application's credentials or database name are correct.

With Lab 2's Failsafe configuration, this command reruns the integration tests under the PostgreSQL profile without rerunning all plain unit tests:

./mvnw --batch-mode test-compile failsafe:integration-test failsafe:verify \
  -Dspring.profiles.active=postgres

Try it locally against Compose first. Explicit test profiles or hard-coded test datasource properties can override the selected profile. To detect silent H2 fallback, assert the product name from a live connection using DatabaseMetaData.getDatabaseProductName(). One option is to pass the expected product (PostgreSQL) as a job environment variable, independently of the active Spring profile. Check the test report: this assertion must execute, not be skipped, in the PostgreSQL job. Both an invalid URL and a missing/wrong profile must make that job fail. Do not put the assertion only behind the postgres profile it is meant to check.

Keep intentional memory tests separate and isolate test data, without clearing stored rows halfway through a POI read-back check. Avoid Maven's -q while diagnosing tests: it hides the Tests run: summaries.

Include the existing DAO contract/API tests and POI read-back checks. Make the publish job wait for both verification jobs; an explicit needs: [verify] would otherwise bypass the new job.

PostgreSQL in staging¶

Install a single PostgreSQL instance with the CloudPirates chart, version 0.21.1, from the course mirror. Use the mirrored PostgreSQL 18.6 image too, as configured below. Helm uses your current Kubernetes context: check that it targets staging first.

Create credentials outside Git¶

Use these names so the chart and logic Deployment agree:

Purpose	Name / keys
Superuser Secret	`postgres-superuser`: `postgres-password`
Application Secret	`logic-db-credentials`: `CUSTOM_USER`, `CUSTOM_DB`, `CUSTOM_PASSWORD`
Application database / user	`colonyops` / `colonyops`

Follow the shared Secret creation and inspection workflow. Create these two Secrets once in group-N-staging, with different generated passwords:

kubectl create secret generic postgres-superuser \
  --from-literal=postgres-password="$(openssl rand -base64 32)" -n group-N-staging

kubectl create secret generic logic-db-credentials \
  --from-literal=CUSTOM_USER=colonyops --from-literal=CUSTOM_DB=colonyops \
  --from-literal=CUSTOM_PASSWORD="$(openssl rand -base64 32)" -n group-N-staging

Secret does not mean safe to commit

Kubernetes Secret values are base64-encoded in their API representation; base64 is not encryption. Keep staging passwords out of Git, Helm values, logs, and screenshots. Read only the required keys into the application using secretKeyRef, not the superuser credentials.

See Secret-backed environment variables. Changing an existing Secret does not necessarily change a password in an already initialized database; rotating credentials requires coordinating both. Environment variables in existing pods also do not change until the pod is replaced.

Read existing credentials¶

For pgAdmin, retrieve key CUSTOM_PASSWORD from logic-db-credentials, not the superuser Secret. Use the shared private inspection commands, which include Bash and PowerShell starting points. The decoded password is sensitive plaintext.

Install the chart¶

Create helm/postgres-values.yaml. It contains references to Secrets, not their contents:

fullnameOverride: postgres-service
image:
  registry: gitlab.stud.atlantis.ugent.be:5050
  repository: devops/images/postgres
  tag: "18.6"
auth:
  username: postgres
  database: postgres
  existingSecret: postgres-superuser
customUser:
  name: colonyops
  database: colonyops
  existingSecret: logic-db-credentials
service:
  type: ClusterIP
  port: 5432
persistence:
  enabled: true
  storageClass: k8s-studstorage
  size: 2Gi

Mirroring the chart does not redirect its container image: keep the image overrides above. Inspect the chart's settings with helm show values using the same OCI URL and version, then install:

helm install postgres-service oci://gitlab.stud.atlantis.ugent.be:5050/devops/images/charts/postgres \
  --version 0.21.1 --namespace group-N-staging \
  --values helm/postgres-values.yaml --wait --timeout 5m
kubectl get pods,services,pvc -n group-N-staging
kubectl logs statefulset/postgres-service -n group-N-staging --tail=100

Check that the PVC is Bound to k8s-studstorage. Your logic service uses jdbc:postgresql://postgres-service:5432/colonyops, the application credentials, and the postgres profile. If startup fails, distinguish a pending PVC, a connection failure, an authentication failure, and a schema/mapping error using events and logs.

Chart or image pull failures¶

Check the registry named in the error or pod events. 429 / toomanyrequests from Docker Hub usually means an old image URL or a missing chart image override still points there; check the mirror settings above. For access or pull failures against the course mirror, contact staff with the failing image/chart URL and error, not passwords or tokens.

PostgreSQL in production (Lab 5)¶

Before the first production logic-service tag, repeat the credential setup and chart installation in group-N, not group-N-staging. Check your Kubernetes context and explicitly change the namespace in every command.

Create new postgres-superuser and logic-db-credentials Secrets with separate production passwords; do not copy staging passwords or replace existing Secrets blindly.
Reuse helm/postgres-values.yaml, including its mirrored image override, chart version 0.21.1, and k8s-studstorage setting. The same release name in a different namespace is a separate PostgreSQL installation with its own PVC.
Wait for the database and confirm its PVC is Bound before tagging the logic service. Configure that service with the postgres profile, application credentials, and jdbc:postgresql://postgres-service:5432/colonyops; this resolves within its production namespace. After deployment, check the application's database connection in logs.

Manual Helm and Secret setup is allowed for this dependency; the production logic service is deployed only through CI. Keep production data separate from staging experiments.

Inspect staging from your laptop¶

Do not expose the database publicly. Forward its Service temporarily:

kubectl port-forward service/postgres-service 15432:5432 -n group-N-staging

Keep this command running while inspecting staging. In your desktop client, register staging as a separate server at 127.0.0.1:15432, with database/user colonyops and the staging application password, not the local defaults or superuser password. Keep the listener restricted to loopback; do not add --address 0.0.0.0. Stop the port-forward with Ctrl+C when finished. If the desktop client cannot connect, use the in-pod SQL client below while diagnosing it.

SQL directly in the database pod¶

Use kubectl get pods to find the database pod. It contains psql, so no local PostgreSQL client installation is needed:

kubectl exec -it <postgres-pod> -n group-N-staging -- psql -U colonyops -d colonyops

This is for inspection, not checking passwords. The image trusts local connections; -W would force a prompt without making PostgreSQL validate the supplied password. A port-forward can also reach a trusted loopback connection. Verify application credentials through the app's connection to the database Service, not a successful local psql session.

Useful starting points are \dt (list tables), \d <table> (describe one), and a SELECT against your own table names. See the psql reference.

Schema changes and deliberate resets¶

First inspect the error and schema. A failed login is not a reason to drop tables, and a schema reset is not a credential rotation. Back up anything you need and stop writers before resetting.

Compose normally scopes named volumes by its project name (usually the directory name), so an earlier experiment may already have initialized this database. POSTGRES_USER and POSTGRES_PASSWORD only initialize a new database: editing the Compose defaults does not change accounts on an existing volume. Reuse the existing credentials or deliberately reset a disposable local database; do not assume a new container means an empty database.

For a disposable local database, remove selected tables through pgAdmin or psql:

DROP TABLE your_table_name;

Use your own names, account for foreign-key dependencies, and restart the app to let Hibernate create missing tables. Do not add CASCADE blindly. To deliberately discard the entire local Compose database, docker compose down -v also deletes its named volumes (including other named volumes in that Compose project): inspect the project before using it.

For staging, agree with teammates before any destructive SQL and stop the logic service's writes. Use the port-forwarded client or in-pod psql to inspect and reset only the intended tables. Do not delete the PVC or uninstall the release as a routine reset. k8s-studstorage uses Retain, so deleting a PVC can leave a volume requiring staff cleanup. A single PVC provides storage across pod replacement, not a backup or high availability.