Udemy SQL Course

March 23, 2021
sql postgres udemy
Estimated Reading Time: 2 minute(s)

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 against Instead of installing Postgresql locally, I elected to leveraeg my home server and run Postgresql using this Docker image and PG Admin.

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.

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.

Restoring From Backup 

The course gives you a backup 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 and into a psql shell and run:

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

which breaks down to:

-c to clean the database
-U to force a user
-d to select the database
-v verbose mode, don't know why
"$$" the location of the files to import in tmp to get around permission issues
-W to force asking for the password to the user (postgres)

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

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.


Python, Pandas and SQL (Oh my!)

July 11, 2019
pandas python sql databases
comments powered by Disqus