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.

$ GRANT ALL PRIVILEGES ON DATABASE dvdrental TO ghilston;

Python, Pandas and SQL (Oh my!)

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