Lab 2021-05-28: Database Administration

Table of Contents

1 Project 3 Q&A

2 Database Administration

Note: the cs34803 server has been shut down. If you would still like to work through this lab, you'll need a Linux or MacOS computer with Docker or Postgres installed. With the latter, skip to step 4.

  1. In a terminal (locally or on mantis), connect to our temporary database playground server using ssh (replace CARLETON_USERNAME with your username—this will be the only time you need to alter a given command): 

    $ ssh CARLETON_USERNAME@cs34803.mathcs.carleton.edu

    Answer yes when it asks you about a fingerprint and enter your Carleton password.

  2. Use Docker1 to create your own instance of Postgres to administer: 

    $ docker run --name $USER -e POSTGRES_PASSWORD=hhh -d postgres

    Check that it worked by running 

    $ docker container ls -a

    Among the listed containers, you should see a line like this (but with your username at the end instead of mine): 

    2b956f0a9a04   postgres      "docker-entrypoint.s…"   8 seconds ago   Up 5 seconds              5432/tcp   awb

  3. Connect to your container with 

    $ docker exec -it $USER bash

    You should now have a terminal prompt within the container that looks something like this 

    root@2b956f0a9a04:/#
  4. Try connecting to Postgres by running the psql command and see what happens

  5. We're faced with an inscrutable error psql: error: FATAL: role "root" does not exist — what does this mean? When connecting to a Postgres database, you do so as a particular role (i.e., user). By default, this is the user you are currently logged in as. Since we are connected to the container as root, psql tried to connect to the database as this user. A fresh Postgres install, however, will only have the default postgres user. So we need to tell psql to connect as that user: 

    root@2b956f0a9a04:/# psql --user postgres
psql (13.3 (Debian 13.3-1.pgdg100+1))
Type "help" for help.

postgres=#

  6. Now that we're connected to the database, let's take a look around. Are there any tables? 

    postgres=# \d
Did not find any relations.

    There are not. What do we see in the list of current roles? 

     postgres=# \dg
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

    Just the postgres user, who has the permission to do all sorts of things.

  7. Since one of those things is creating other roles, let's make one for root so we don't have to connect as the postgres user. 

    postgres=# CREATE USER root;
CREATE ROLE
postgres=# \dg
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 root      |                                                            | {}

  8. Let's disconnect from Postgres (\q) and try and reconnect as root: 

    postgres=# \q
root@2b956f0a9a04:/# psql
psql: error: FATAL:  database "root" does not exist

    At first it might appear we've made no progress, but a close look shows we've gone from role "root" does not exist to database "root" does not exist. This is because when we run psql without arguments, it tries to connect as the current user to a database with the same name (e.g., the database "root").

  9. So what databases do exist? Back to Postgres (as the postgres user) to take a look: 

    root@2b956f0a9a04:/# psql --user postgres

    First up, what database are we connected to now? We can get information about our current connection: 

    postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".

    We can also list all databases: 

    postgres=# \l+
                                                                   List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges   |  Size   | Tablespace |                Description
-----------+----------+----------+------------+------------+-----------------------+---------+------------+--------------------------------------------
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 |                       | 7877 kB | pg_default | default administrative connection database
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +| 7729 kB | pg_default | unmodifiable empty database
           |          |          |            |            | postgres=CTc/postgres |         |            |
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +| 7729 kB | pg_default | default template for new databases
           |          |          |            |            | postgres=CTc/postgres |         |            |
(3 rows)

    Ok, so the default postgres database (owned by the postgres user) is described at the default administrative connection database. Probably not a good idea to play around in this one, so let's create a database for that purpose (and make it owned by our root user): 

    postgres=# CREATE DATABASE labfuntimes WITH OWNER=root;
CREATE DATABASE
postgres=# \l+
                                                                     List of databases
    Name     |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges   |  Size   | Tablespace |                Description
-------------+----------+----------+------------+------------+-----------------------+---------+------------+--------------------------------------------
 labfuntimes | root     | UTF8     | en_US.utf8 | en_US.utf8 |                       | 7729 kB | pg_default |
 postgres    | postgres | UTF8     | en_US.utf8 | en_US.utf8 |                       | 7877 kB | pg_default | default administrative connection database
 template0   | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +| 7729 kB | pg_default | unmodifiable empty database
             |          |          |            |            | postgres=CTc/postgres |         |            |
 template1   | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +| 7729 kB | pg_default | default template for new databases
             |          |          |            |            | postgres=CTc/postgres |         |            |
(4 rows)

  10. Disconnect from Postgres, and reconnect to the labfuntimes database: 

    postgres=# \q
root@2b956f0a9a04:/# psql labfuntimes
psql (13.3 (Debian 13.3-1.pgdg100+1))
Type "help" for help.

labfuntimes=>

    Note how the Postgres prompt has changed to indicate which database we are connected to. Since the root user doesn't have permission to create databases, running CREATE DATABASE as root would result in an error.

  11. Before we can start adding data to labfuntimes, we need to disconnect from Postgres once more and download some SQL files. Since Docker containers are isolated, it's fine to install whatever software in them (and you have the ability to do so. We'll install the wget utility, and then use that to download our SQL files. Conveniently, Postgres lets us run shell commands via \!. 

    labfuntimes=>\! apt update
labfuntimes=>\! apt install wget
labfuntimes=>\! wget cs.carleton.edu/faculty/awb/cs334/s21/notes/DDL+drop.sql
labfuntimes=>\! wget cs.carleton.edu/faculty/awb/cs334/s21/notes/smallRelationsInsertFile.sql

  12. DDL+drop.sql creates the tables for the university database we've seen before. smallRelationsInsertFile.sql inserts tuples into these tables. We can execute commands from a file using \i: 

    labfuntimes=> \i DDL+drop.sql
labfuntimes=> \i smallRelationsInsertFile.sql

    This will take a little while to complete. Use \d to see the list of tables, and \d TABLE to see the schema for a specific table. Run a few queries until you're satisfied the data inserted correctly.

  13. This concludes a basic introduction to administering a newly installed instance of Postgres. Feel free to continue experimenting, full documentation on Postgres administration is here: https://www.postgresql.org/docs/13/admin.html.

Footnotes:

1

Docker is virtualization platform that allows users to create isolated containers. These containers each act like a seperate system with its own operating system, files, etc. This will allow us to all have our own isolated installations of Postgres coexisting on the same server.