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. |
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.
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 are using AWS Cloud9 with Amazon Linux 2 or Amazon Linux 2023, the following commands should work instead to install an acceptable version of PostgreSQL:
$ sudo yum install postgresql postgresql-server
$ sudo service postgresql initdb
$ sudo service postgresql 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.
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 Homebrew installed, follow the installation instructions on its homepage.
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.
Homebrew may complain that there is "No available formula with the name "postgres" when you try to run brew install postgres. If you see this message, try installing one of the specific versions listed, preferring one of the most recent versions. For instance, to install version 14, you would run brew install postgresql@14.
On a linux machine (other than Amazon Linux), 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.
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.
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.