Lab: practise SQL and recovery with PostgreSQL

Scope and prerequisites

Complete the eight PostgreSQL bites in sequence using the isolated learnwithsk-postgres container. Keep the study fixture created in the schema bite unchanged; reversible examples end in rollback. This lab uses synthetic data only. It needs neither MicroBank nor a Kubernetes cluster running.

Record the version, image digest, resource limits, actual results, and failures in a local evidence file. The expected results below give you a comparison for your run; they do not establish that a production recovery has been verified.

1. Assert the data contract

Save as verify.sql:

DO $
BEGIN
  IF (SELECT count(*) FROM study.accounts) <> 3
     OR (SELECT count(*) FROM study.entries) <> 3
     OR (SELECT coalesce(sum(amount_cents), 0) FROM study.entries) <> 2750
     OR (SELECT coalesce(sum(amount_cents), 0) FROM study.entries WHERE account_id = 1) <> 750
     OR NOT EXISTS (SELECT 1 FROM study.accounts WHERE account_id = 3)
     OR EXISTS (SELECT 1 FROM study.entries WHERE account_id = 3)
  THEN
    RAISE EXCEPTION 'Fixture count or balance invariant failed';
  END IF;
END
$;
SELECT 'fixture invariants passed' AS result;

docker exec -i learnwithsk-postgres psql -X -v ON_ERROR_STOP=1 \
  -U postgres -d study < verify.sql

Also run your HAVING and empty-account queries from the SQL bite. The selected accounts should be 2 and 3 respectively. Record the actual rows rather than only a success message.

2. Confirm rejected input stays rejected

Save as constraints.sql. Exception blocks are test assertions: each invalid attempt must raise its expected database error.

DO $
BEGIN
  BEGIN
    INSERT INTO study.entries (account_id, request_key, amount_cents, created_at)
    VALUES (1, 'r-1', 900, now());
    RAISE EXCEPTION 'Duplicate request unexpectedly accepted';
  EXCEPTION WHEN unique_violation THEN NULL;
  END;
  BEGIN
    INSERT INTO study.entries (account_id, request_key, amount_cents, created_at)
    VALUES (999, 'unknown-account', 900, now());
    RAISE EXCEPTION 'Unknown account unexpectedly accepted';
  EXCEPTION WHEN foreign_key_violation THEN NULL;
  END;
  BEGIN
    INSERT INTO study.entries (account_id, request_key, amount_cents, created_at)
    VALUES (1, 'zero-value', 0, now());
    RAISE EXCEPTION 'Zero value unexpectedly accepted';
  EXCEPTION WHEN check_violation THEN NULL;
  END;
  BEGIN
    INSERT INTO study.entries (account_id, request_key, amount_cents, created_at)
    VALUES (1, NULL, 900, now());
    RAISE EXCEPTION 'Null key unexpectedly accepted';
  EXCEPTION WHEN not_null_violation THEN NULL;
  END;
END
$;
SELECT 'constraint checks passed' AS result;

docker exec -i learnwithsk-postgres psql -X -v ON_ERROR_STOP=1 \
  -U postgres -d study < constraints.sql

Run verify.sql again. Failed inserts may advance the identity sequence, but must not create accepted entry rows.

3. Collect contention and query-plan evidence

Run the bounded two-session lock exercise. Keep the lock-timeout message and verify both transactions ended. Then record the before/after plans and unchanged five IDs from Indexes and query plans. Explain the plans PostgreSQL actually chooses, including any difference from your prediction.

4. Back up and restore to a new database

Run in your local lab directory. Do not redirect over a backup you need to keep. study_restore must not already exist; if it does, stop and identify the previous exercise rather than dropping it automatically.

umask 077
docker exec learnwithsk-postgres pg_dump -U postgres -d study \
  --format=custom --schema=study --no-owner --no-acl > study.dump
docker exec -i learnwithsk-postgres pg_restore --list < study.dump
docker exec learnwithsk-postgres createdb -U postgres study_restore
docker exec -i learnwithsk-postgres pg_restore -U postgres -d study_restore \
  --exit-on-error --no-owner --no-acl < study.dump
docker exec -i learnwithsk-postgres psql -X -v ON_ERROR_STOP=1 \
  -U postgres -d study_restore < verify.sql
docker exec -i learnwithsk-postgres psql -X -v ON_ERROR_STOP=1 \
  -U postgres -d study_restore < constraints.sql

Use no TTY when streaming the binary dump. Check every command's exit status before proceeding; a partial dump is not a backup. The assertions should pass against the restored database too. Inspect \d study.entries there.

Because this restore deliberately omits ACLs, study_reader should not initially have schema/table access in study_restore. Reapply its grants in that database, then verify the allowed read and denied write from the roles bite. The role itself remains in this server; a new server would need role provisioning too. pg_restore↗, dump reference↗.

5. Save the evidence and release resources

Your evidence should include the fixture assertions, constraint failures, lock timeout, query plans, role checks, restore duration, and limitations. This is a logical restore on the same server with lab data; it does not establish MicroBank disaster recovery, PITR, or network/TLS security.

After saving anything you need, stop this profile before starting MicroBank:

docker stop learnwithsk-postgres

To resume your own fixture later, use docker start learnwithsk-postgres. When finished permanently, the following removes only the named fixture container and its database volume. This erases both teaching databases. Inspect the names/ownership first; do not substitute MicroBank volumes.

docker rm learnwithsk-postgres
docker volume rm learnwithsk-postgres-data

Keep or remove your local dump according to your evidence needs. Continue with MicroBank database inspection.

Interpret the completed round trip

Expected counts and totals check selected invariants; rejected writes check that selected constraints still apply. Neither alone proves every row matches. The grant rehearsal adds another dimension: restored data without the intended reader permissions is not a complete operational handoff. A failed insert can consume an identity sequence value without creating an entry, so compare row counts rather than expecting contiguous IDs.

If restore verification fails, retain the source fixture and dump, inspect the first reported restore error, and resolve it before claiming success. Keep the original database until the new one is verified. Then move to MicroBank inspection with the fixture's guarantees clearly separated from the application's actual schema.