Installing Our Database: PostgreSQL

We'll be using PostgreSQL for our SQL and analysis tasks. You can use other systems and they work just fine - but for this one we'll be using PostgreSQL. We have a bunch of data to load up and poke through, so let's get rolling quickly OK?


There are numerous ways to get a PostgreSQL server up and running - the easiest is usually your best choice!

I like to run Postgres locally, so I typically run Postgres.app because I'm working on a Mac. It's very flexible and is a drag/drop install.

You can also work with Docker if you like, but beware that you might run low on resources later in the course as we dive into millllllions of records. Here's a docker-compose file for you to get started with:

version: "3"
services:
postgres:
image: postgres:13.3
environment:
- POSTGRES_USER=dee
- POSTGRES_PASSWORD=deedle
- POSTGRES_DB=cassini
ports:
- "8088:5432"
volumes:
- ./data/pg:/var/lib/postgresql/data
- ./csvs:/home
container_name: pg
pgweb:
container_name: pgweb
restart: always
image: sosedoff/pgweb
ports:
- "8080:8081"
links:
- postgres:postgres
environment:
- DATABASE_URL=postgres://dee:[email protected]:5432/cassini?sslmode=disable
depends_on:
- postgres

Here are some fun aliases for you to pop into your .env file if you have a plugin that will auto load them into your shell:

alias up="docker-compose up"
alias down="docker-compose down"
alias in="docker exec -it pg /bin/bash"

If you're on Windows, it's probably easiest to download Postgres and run it as a service.

Clients

We'll be using the the shell for most of our work, but at times you might want to be working in a GUI. If you run the docker bits above, a GUI is there for you and should be running on port 8080 when you docker-compose-up.

I like Postico and have paid the $35 happily. It's Mac-only, so if you're on Windows I would encourage you to download the Azure Data Studio.

You need to be logged in to leave a comment.

Pen
>