Lab: Docker

In this lab, you will use Docker to run a PostgreSQL database on your device. You will load a database dump into the database, then run some simple queries.

Instructions

All students should complete this assignment on their own devices. It is important that all students get Docker installed and learn to use Docker. However, you are welcome to discuss the assignment with other students.

Task 1: Install Docker

macOS and Linux users should follow the official instructions for installing Docker Desktop.

Windows users may find it helpful to follow Steps 1 and 3 from my guide to Configuring Windows for Docker Development. Step 2 can be skipped for now, since we won’t be cloning a Git repository in this lab.

Use docker ps to check that Docker is running. You should see:

> docker ps
CONTAINER ID   IMAGE     COMMAND   CREATED   STATUS    PORTS     NAMES

Task 2: Run PostgreSQL

Run this command to start a PostgreSQL container:

docker run \
  --rm \
  --env POSTGRES_PASSWORD=mysecretpassword \
  --env POSTGRES_USER=django \
  postgres:16.1

If copy-pasting this command gives an error, write it on one line:

docker run --rm --env POSTGRES_PASSWORD=mysecretpassword ...

In another shell, run docker ps to identify the name of the container. In the subsequent commands, replace CONTAINER_NAME with your container’s name.

Next, open a SQL prompt in your container using the psql command:

docker exec \
  --interactive \
  --tty \
  CONTAINER_NAME psql --username=django

Finally, list the database tables:

\dt

You should see that the database doesn’t contain any tables.

Note: To save typing, there are short versions of command line flags. For example:

docker exec -it CONTAINER_NAME psql -U django

Note: Use the --help flag to view documentation for Docker commands:

docker run --help

docker exec --help

Task 3: Load a Database Dump

It is essential to make periodic backups of your database. For this purpose, PostgreSQL offers the pg_dump command. I used pg_dump to back up an instance of the credit card database.

Download my backup: 2024-02-15.pg_dump

First, copy the backup into your PostgreSQL container:

docker cp 2024-02-15.pg_dump CONTAINER_NAME:/2024-02-15.pg_dump

Next, use the pg_restore command to restore the database from the backup:

docker exec CONTAINER_NAME \
  pg_restore --username=django --dbname=django /2024-02-15.pg_dump

Note: I created a custom-format backup archive, which has some advantages and disadvantages relative to the default plain-text format.

Task 4: Run Simple Queries

Open a SQL prompt in your container and list the database tables. You should see tables for the credit card database (ignore tables without the cc_ prefix).

Finally, write queries to count the number of records in each table:

For example, there are 148 records in the cc_customer table:

django=# SELECT COUNT(*) FROM cc_customer;
 count
-------
   148
(1 row)

Submit

Submit the results of your queries on Gradescope.

This lab will be graded for completion, as part of your attendance and participation grade.