If even one person references this page, even future me, then this writeup was worth it :)
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.
Find container id
$ docker ps | grep postgres
Exec into it
$ docker exec -it <container id> /bin/bash
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.
- In a
Which should look something like this
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.
Since I created my own username to access postgres, which I named
ghilston, I’ll need to grant this account access to this
Since this is just dummy data we’ll grant all access with the following
postgres=# ALTER USER ghilston WITH SUPERUSER, CREATEROLE, CREATEDB; ALTER ROLE
postgres=# represents these commands are to run inside a
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
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
$ sudo su postgres
$ 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:
Reminder: The default password for the
postgres username is empty, so just slap enter when prompted.
This didn’t work for me
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:
- Open your server, right click Databases and then Create -> Database…
- Fill the Database field with dvdrental.
- Click on Save.
- Right click on your newly created dvdrental database and select Restore…
- 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.
- In the Restore options tab, enable Pre-data, Post-data and Data, then click Restore.
- Right click on dvdrental database and select Refresh…
- Right click on dvdrental database and select Query Tool…
- Try to run this query to check if the database was restored correctly:
SELECT * FROM film;
I don’t remember how I setup the account to login to
pgadmin4 but the username/email I used to login was
email@example.com and the password was
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
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.
Open up the pgAdmin, double click on the udemy server to connect. Expand the
dvdrental drop down and then open Schemas > public > Tables.