Hello Postgres#

Postgres is an popular and fast open source SQL database. Django integrates perfectly with Postgress and other database management systems, including non-relational ones like MongoDB. I chose Postgres because it’s popular and easy. You are free to use any DBMS you like to implement your project.

Explore Dockerhub#

Search Dockerhub for the official Postgres container. There are key questions you need answers for:

  1. What versions are available and what should I use?

  2. How do I configure the container?

  3. Where is the data stored?

  4. What tools do I need on my dev box to access the database?

Answer these questions before you continue.

Start Postgres#

Let’s start the container:

$ docker run -d --rm -p 5432:5432 -e POSTGRES_PASSWORD=django -e POSTGRES_DB=mysite postgres:14.1

You should now see the container running:

$ docker ps 
CONTAINER ID   IMAGE           COMMAND                  CREATED          STATUS          PORTS                                       NAMES
56dd612d6697   postgres:14.1   "docker-entrypoint.s…"   21 seconds ago   Up 20 seconds   0.0.0.0:5432->5432/tcp, :::5432->5432/tcp   stupefied_meninsky

Note

Make a note of your container’s name. Mine as shown is stupefied_meninsky

The Postgres container is running in the background. We can now connect to it using the postgres client, psql.

Install the Postgres Client on Your Dev Box#

Databases are structured storage. That means that the data isn’t as simple to access as files, where any program can see the data. Databases have clients that make the data accessible. In order to access the database on your dev box install the Postgres client using apt:

$ sudo apt install postgresql-client

Now that you have the client installed, you can connect to your database from the command line:

$ psql -h localhost -U postgres 
Password for user postgres: 
psql (14.7 (Ubuntu 14.7-0ubuntu0.22.04.1), server 14.1 (Debian 14.1-1.pgdg110+1))
Type "help" for help.

postgres=#

Connect to Postgres and Make a Table#

Let’s create and examine some data the SQL way. At the end of the last part you should have connected to your database and seen the Postgres command prompt. In a relational database data is stored in tables called relations. Run this command to create a table:

postgres=# create table mydata (id INT PRIMARY KEY, name TEXT);

Now let’s see the table we created:

postgres=# \d
         List of relations
 Schema |  Name  | Type  |  Owner   
--------+--------+-------+----------
 public | mydata | table | postgres
(1 row)

Let’s store some data in our table:

postgres=# insert into mydata values (1, 'Mike'), (2, 'Bike'); 
INSERT 0 2

Now let’s view the data we stored:

postgres=# select * from mydata; 
 id | name 
----+------
  1 | Mike
  2 | Bike
(2 rows)

Now let’s quit the database shell:

postgres=# \q
mmatera@dev-box:~/cis-92-sp23$

Stop and Remove Postgres#

The container for this lab is temporary (we ran it with --rm). Stop it using its name:

$ docker stop stupefied_meninsky 
stupefied_meninsky

Do it Again#

Re-run this lab from the start. When you get to the Postgres command line run \d. Do you still see the table you created?