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.
One can simply SSH into the docker container running their postgresql, assume the postgres user by running
$ sudo su postgres and then run
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
Since this is just dummy data we’ll grant all access with the following
$ GRANT ALL PRIVILEGES ON DATABASE dvdrental TO ghilston;