Configuring A Postgres Database For Studying

link to course

One of the first things the course walks you through is setting up a Postgresql Database so that one can practice. I’ve elected to set my Postgres database inside a Docker container to make the installation process simpler, instead of installing Postgresql locally, I elected to leverage my home server and run Postgresql using this Docker image and PG Admin as a UI interface.

This approach allowed me to continue studying regardless of which machine I was on, as I have access to the home server through its configured VPN.

SSHing Into Postgres Docker Container

Find container id $ docker ps | grep postgres

Exec into it $ docker exec -it <container id> /bin/bash

Accessing psql

One can simply SSH into the docker container running their postgresql, assume the postgres user by running $ sudo su postgres and then run $ psql or $ psql -U postgres.

Listing Postgres Users

• In a psql shell run \du

Which should look something like this

1
2
3
4
5
6
7

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

One can run \du+ for more information, which adds a description column

Notice that the account I created ghilston is under privileged. In the next section we’ll grant this account more privileges.

Granting Access To Database

Since I created my own username to access postgres, which I named ghilston, I’ll need to grant this account access to this dvdrental database.

Since this is just dummy data we’ll grant all access with the following psql command.

postgres=# ALTER USER ghilston WITH SUPERUSER, CREATEROLE, CREATEDB; ALTER ROLE

Where postgres=# represents these commands are to run inside a psql shell.

This ensures we do not experience the following error:

ERROR: permission denied for table actor SQL state: 42501

when running a query such as SELECT * FROM actor; in pgAdmin4

Restoring From Backup At CLI

The course gives you a backup, or export, of their database so that we can import it via a restore. Before we can do this we’ll need to install wget as our image is running with minimal packages.

# apt update && apt install wget -y

At that point you can SCP the dvdrental.tar to the machine running postgres or use a free public file uploading service such as filebin.

To perform the actual restore jump back to the postgres user

$ sudo su postgres

and run:

$ pg_restore -c -U postgres -d dvdrental -v "/path/to/dvdrental.tar" -W

note this works for the exercises.tar as well, which generally we store in /var/lib/postgresql_

which breaks down to:

1
2
3
4
5
6

-c to clean the database
-U to force a user
-d to select the database
-v verbose mode to see more output
"$$" the location of the files to import in tmp to get around permission issues
-W to force asking for the password for the user (postgres)

Reminder: The default password for the postgres username is empty, so just slap enter when prompted.

Restoring From Backup At UI

This didn’t work for me

from here

You may encounter a Configuration Required Error when attempting to right click on “restore” with the error text.

Please configure the PostgreSQL Binary Path in the Preferences dialog.

To resolve this, in pgadmin4, go to File > Preferences > Paths > Binary Paths > Postgres Binary Path.

And enter the result of running $ which psql in the container running postgres. In my container this was:

/usr/bin/psql

Steps:

1. Open your server, right click Databases and then Create -> Database…
2. Fill the Database field with dvdrental.
3. Click on Save.
4. Right click on your newly created dvdrental database and select Restore…
5. Under Filename, click on the three dots button and navigate to the location where you downloaded the dvdrental.tar file. If the file doesn’t show up, select All Files from the dropdown menu in the bottom right corner.
6. In the Restore options tab, enable Pre-data, Post-data and Data, then click Restore.
7. Right click on dvdrental database and select Refresh…
8. Right click on dvdrental database and select Query Tool…
9. Try to run this query to check if the database was restored correctly: SELECT * FROM film;

Connecting pgadmin4 To Our Postgres Database

I don’t remember how I setup the account to login to pgadmin4 but the username/email I used to login was pgadmin4@pgadmin.org and the password was admin.

After authenticating one click

Servers > Create > Server…

Fill out the form as follows

Name = udemy Host name/address Port Maintenance database = postgresql Username Either postgres or the account you made yourself, which mine was ghilston

Running A Query

Open up the pgAdmin, double click on the udemy server to connect. Right click on dvdrental “Query Tool…” which will give us a panel to run queries against.

Inspect Tables

Open up the pgAdmin, double click on the udemy server to connect. Expand the dvdrental drop down and then open Schemas > public > Tables.