Configuring A Postgres Database For Studying ¶
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;