Sharp Knives: A PostgreSQL Workshop

Installation and setup

Basics

We will use a Vagrant box with Ubuntu 16 TLS in order to:

Have a uniform setup for everyone
Not mess up anyone's existing setup

The host OS for Vagrant will be your regular Dev OS, and you will be able to edit everything in your favourite editor.

Desired end state:

A server with PostgreSQL >=9.5 installed
SSH access to this server
Ability to copy and edit files on this server
Ability to run scripts on this server
A working CLI to interact with PostgreSQL instance
A working GUI to interact with PostgreSQL instance

For 1, 2, 3, and 4 we will use Ubuntu 16 LTS through Vagrant. For 5, we will use psql on the guest box. For 6, we will use DataGrip. If you want, you can use a tool that you are already familiar with. Here are some great options:

Terminology

Host box: Your own development machine
Guest box: The virtual machine created by Vagrant

Procedure

Checkout the latest copy of the cloned repo. This is important.
Install the latest Vagrant. The version is 2.2.x as of writing this. You will very likely need VirtualBox or VMWare to go along with Vagrant. See their installation guide for more details.
Install the latest DataGrip. The version is 2018.3.x as of writing this.
Fork this repo under a private repository. Fork is important, because you will want to push your own changes, and private is important because this material is only available to workshop attendees. GitHub now supports private forks. Gitlab has always had private forks. Make your choice, and get started!

Ensure vagrant works on your dev box by running vagrant list-commands. You should get an output like:

 Below is a listing of all available Vagrant commands and a brief
 description of what they do.
 
 box             manages boxes: installation, removal, etc.
 cap             checks and executes capability
 cloud           manages everything related to Vagrant Cloud
 destroy         stops and deletes all traces of the vagrant machine
 docker-exec     attach to an already-running docker container
 docker-logs     outputs the logs from the Docker container
 docker-run      run a one-off command in the context of a container
 global-status   outputs status Vagrant environments for this user
 halt            stops the vagrant machine
 help            shows the help for a subcommand

 ... more lines follow

Go to the checked out copy of your repository, and run vagrant up. Ensure you have a reasonably high bandwidth internet connection. This stage downloads and installs the Linux VM, PostgreSQL and other libraries. You should see output that ends with lines like these:

 ==> default: Configuring and enabling network interfaces...
 ==> default: Mounting shared folders...
     default: /vagrant => /Users/swanand/projects/courses/pg-workshop
     default: /vagrant_data => /Users/swanand/projects/courses/pg-workshop/shared
 ==> default: Machine already provisioned. Run `vagrant provision` or use the `--provision`
 ==> default: flag to force provisioning. Provisioners marked to run always will still run.
 ==> default: Running provisioner: shell...
     default: Running: inline script

If everything went well, you can vagrant ssh and you will be logged into the box, with a prompt that reads: vagrant@ubuntu-xenial:~$ . This is the guest box we mentioned earlier.
On the guest box, run
```
 psql -U pgw -d pgw-main
```
to ensure everything works. If that didn't work,
```
 sudo su - postgres
 psql -U pgw -d pgw-main 
```
Email me if this didn't work.
From your host box, try to configure a connection to the PostgreSQL on the guest box. In DataGrip, create a new project (if you have the code checked out, you can select it when the prompt appears). Here are my connection setup windows:

Final Checklist

You are able to bring up the guest box: vagrant up
You are able to connect to PostgreSQL on the guest box through psql on the guest box. Run this query and confirm that you get 91 as the answer:

select count(*) from authors_books;
You are able to connect to PostgreSQL on the guest box through DataGrip on the host box. Run this query from DataGrip and confirm that you get 1567 as the answer:

select count(*) from editions_shelves;

tucosaurus/sharp-knives-pgw

Sharp Knives: A PostgreSQL Workshop

Installation and setup

Basics

Procedure

Final Checklist