Preparations

Vocabulary

Throughout this book, we'll be using certain vocabulary related to RDBMSs and SQL. We've mentioned some of these terms already in this book, but it's worth recapping them here while introducing a few other terms that you should familiarize yourself with.

Term Definition
Relational Database A structured collection of data that follows the relational model.
RDBMS Relational Database Management System. A software application for managing relational databases, such as PostgreSQL.
Relation A set of individual but related data entries; analogous to a database table.
SQL Structured Query Language. The language used by RDBMSs.
SQL Statement A SQL command used to access/use the database or the data within that database via the SQL language.
SQL query A subset of a "SQL Statement". A query is a way to search, or lookup data within a database, as opposed to updating or changing data.

Installing PostgreSQL

Before you can start using PostgreSQL, you first need to install it for your Operating System. Please follow the directions below to install it.

You may already have PostgreSQL installed, in which case you may be able to skip some parts of the installation procedures described below. Unfortunately, we can't help you customize a previously installed version of PostgreSQL.

Cloud9

By far the simplest solution would be to use a cloud-based development environment such as AWS Cloud9. If your environment uses Amazon Linux AMI, the following procedure should work to install PostgreSQL 9.6:

$ sudo yum install postgresql96 postgresql96-server
$ sudo service postgresql96 initdb
$ sudo service postgresql96 start

If you try to run the psql console simply by using the psql command, you'll receive an error which looks something like this:

$ psql
psql: FATAL:  role "ec2-user" does not exist

This is because you are trying to run Postgres as the local user, and there is no user role set up within Postgres for that user.

Running Postgres as the postgres user

In order to run Postgres as the default postgres user, you need to prepend whichever command you are running with sudo -u postgres. For instance, to run the psql command, type:

$ sudo -u postgres psql

To create a new database, type:

$ sudo -u postgres createdb my_database

Creating a Postgres role for the local user

Another option is to create a Postgres role for the local user (passing the -s flag to createuser creates the user as a superuser):

$ sudo -u postgres createuser -s $LOGNAME

Note: when you run this command bash will automatically replace $LOGNAME with whatever the name is for the local user, which will usually be ec2-user.

When running that command you may see a message along these lines could not change directory to "/home/ec2-user/environment": Permission denied. Don't worry about this, the user should still be created.

With the new user role created, you should now be able to run Postgres commands from the terminal without the need to prepend them with sudo -u postgres.

It's usually a good idea to also create a database of the same name as the user role. When you run the psql command, Postgres automatically tries to connect to a database of the same name as the user running the command:

$ psql
psql: FATAL:  database "ec2-user" does not exist
$ createdb $LOGNAME
$ psql
psql (9.6.11)
Type "help" for help.

ec2-user=#

Cloud9 is an especially good option if your main OS is Windows, or if you're fairly new to programming or configuring and installing development tools. If you need help setting up Cloud9, see our instructions in the LS95 Prep Course.

Mac OSX install

One option for installing PostgreSQL on a Mac is to use Postgres.app. There are instructions for installing Postgres.app on the its homepage. The site also has instructions for using the Postgres.app command line tools.

Alternatively you can use Homebrew to install PostgreSQL. If you don't have it installed follow the instructions on the homebrew's homepage to install it.

First, we ensure that Homebrew is up to date

$ brew update
$ brew doctor
$ brew upgrade

Next, follow the instructions listed in this blog post.When reading this article, you only need to follow instructions for installing PostgreSQL; skip the section on Rails.

Linux Install

On a linux machine, your easiest route for installation is to use the package manager included with your distribution. Instructions on how to install PostgreSQL are included in this link. You should skip the section on Rails.

Optional PostgreSQL Password

Some users may want to add a bit of security so that not just anyone can access their PostgreSQL databases. PostgreSQL gives us the option to set a password for the entire installation of PostgreSQL, and even for particular databases. You can find more information about setting a password in the docs for psql: look for the meta command \password and the option --password. For more information about setting passwords for particular users, databases, and hosts, check out the page on the password file.

Summary

In this chapter we've highlighted some specific terms and phrases which we'll be using throughout this book.

We've also directed you to how to install PostgreSQL on both Mac and Linux systems. You should now have PostgreSQL installed and ready to use; in the next chapter we'll start looking at how to interact with PostgreSQL.