CIS-2730, Lab #3: Configuring PostgreSQL

Reading: None.

In this lab, you will configure the PostgreSQL database server on your BeaglePlay to hold data temporarily when the network is not available.

You will need Internet access for Part 1 of this lab.

Part 1: Install the PostgreSQL Database Server

Do the following steps to install and configure the PostgreSQL database server on your BeaglePlay.

Use the Debian package management tool to download and install PostgreSQL.
```
  $ sudo apt install postgresql
```
It might be necessary to first run sudo apt update to freshen the package database before the postgresql package can be found. Ignore the updates reported by sudo apt update. If you apply them all, you will get a new kernel that is incompatible with the BeaglePlay (the apt program is interacting with Debian's package database, not BeagleBoard's).

This command also creates a postgres Linux user account that "owns" the server. In addition, there is a postgres database role that has administrative access over the server process itself. The postgres Linux user will automatically authenticate to the server as the postgres role. Finally, the server is configured to automatically start when the system boots.
Give the postgres Linux user a password. You can do this as root, and you can access root privilege with the sudo command.
```
  $ sudo passwd postgres
```
Choose a password that you can remember. Be aware that sudo may first prompt you for debian's password, and then the passwd program will ask you for the new postgres password twice. However, sudo may also skip the prompt for debian's password if you used sudo recently. Be careful about which passwords you are typing at which prompts!
Next, "become" the postgres Linux user with the su (switch user) command.
```
  $ su - postgres
```
The dash means to execute the postgres login scripts (if any), creating an environment that is identical to if you logged in as the user normally.

Now, create a database role for the debian Linux user. The commands and their responses should look something like as follows.
```
  postgres@BeagleBone:~$ psql
  psql (15.10 (Debian 15.10-0+deb12u1))
  Type "help" for help.

  postgres=# CREATE ROLE debian WITH LOGIN CREATEDB;
  CREATE ROLE
  postgres=# \q
  postgres@BeagleBone:~$
```
The CREATE ROLE command specifies that the new role can log into the server and can create databases. The \q command quits from the psql program. Use the exit command to return to the debian Linux user's shell.
Now create a database with the same name as the debian Linux user.
```
  $ createdb debian
```
We will use this "default" database for our work. It is possible for the debian Linux user to create additional databases if desired because of the CREATEDB attribute assigned to its role.

Check your work by using psql as the debian Linux user. It should automatically authenticate as the debian role and attach to the debian database as follows.
```
  debian@BeagleBone:~$ psql
  psql (15.10 (Debian 15.10-0+deb12u1))
  Type "help" for help.

  debian=>
```
You can use the \q command to quit the PostgreSQL interactive tool.

Part 2: The Data

In this part, you will create a simple database to hold temperature data. Proceed as follows.

Create a file named create-tables.sql containing the following text.
```
  CREATE TABLE reading (
      id           INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
      reading_time TIMESTAMP WITHOUT TIME ZONE,  -- Timestamps are in UTC.
      temperature  NUMERIC(4, 2)                 -- Degrees Celsius.
  );
```
For more information on the data types supported by PostgreSQL, see the documentation. The id attribute is a "reading ID" that is unique on the local database and unrelated to reading IDs on other systems.

The phrase "WITHOUT TIME ZONE" on the TIMESTAMP data type is optional, since the default behavior of the TIMESTAMP data type is to not include time zones. We will record times using Coordinated Universal Time (UTC).

The temperature readings themselves, in degrees Celsius, range from -99.99 degrees to +99.99 degrees. This is because of the NUMERIC(4, 2) type, which specifies a total of four significant figures with two after the decimal point.
Execute this file in the default database by running psql.
```
    $ psql --file=create-tables.sql
  
```
If you then go into psql interactively and use the \d command, you should see your table listed.

Part 3: The Program

Create a file named lab-03.py containing a program that writes a file (named temperature-data.sql) in the following format.

  INSERT INTO reading (reading_time, temperature) VALUES
    (reading_time, temperature),
    ...
  ;

Note that each record consists of two values in parentheses. All records but the last one have a trailing comma. Here is a concrete example of what the file should look like:

  INSERT INTO reading (system_id, reading_time, temperature) VALUES
    ('2025-02-14 15:42:37', 25.45),
    ('2025-02-14 15:52:39', 25.27),
    ('2025-02-14 16:02:43', 24.92)
  ;

Notice that the time format includes both the date and the time, with time resolution to the second. PostgreSQL can accept TIMESTAMPs in many formats, but this format is standard and includes all the information we will need. The temperatures in the above example, are made up values (for now).

Here is an outline of the program.

  from datetime import datetime

  # Return the current date/time.
  def get_timestamp():
      return datetime.utcnow().isoformat()

  # Return a fake temperature.
  def get_temperature():
      # Later this function will do something real.
      return 25.0

  # Finish Me!

  # Pseudocode:
  # <Open temperature-data.sql for writing
  #    Include an exception handler (at the bottom) to deal with open failures>
  # <Write the header (first line) to the output file>
  #
  # for i in range(0, 10):
  #     <Write a record to the output file>
  #     IF <This is not the last record> THEN
  #         <Write a comma followed by a newline to the output file>
  #     ELSE
  #         <Write a newline to the output file>
  #     END
  #
  # <Write the last line of the output file>
  # <Close the output file>

Run the program to test it. When you are happy with how it is working, run psql as follows.

  $ psql --file=temperature-data.sql

This will cause psql to execute the commands in the named file (the file that was written by your program) to install data into the database.

Check your work by running psql interactively and executing the following SQL statement:

  debian=> SELECT * FROM reading;

You should see the data you just inserted.

Submission

Submit your program (lab-03.py) to Canvas. Be sure to include your name in a comment at the top of your program!