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.
If you're using WSL2 as your development environment, you can install PostgreSQL by opening your WSL terminal and running the following commands:
$ sudo apt update
$ sudo apt install postgresql
$ 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 "username" 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.
When running that command you may see a message along these lines could not change directory to "/home/username/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 "username" does not exist
$ createdb $LOGNAME
$ psql
psql (9.6.11)
Type "help" for help.
username=#
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, ensure that Homebrew is up to date:
$ brew update
$ brew doctor
$ brew upgrade
Then, follow the instructions on the PostgreSQL wiki here.
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.
If you are using Codespaces, run the following commands from the Codespaces terminal:
$ sudo apt-get update
$ sudo apt-get install postgresql postgresql-contrib libpq-dev
$ sudo service postgresql start
$ sudo -i
$ sudo -u postgres createuser --superuser codespace
$ sudo -u postgres createdb codespace
$ exit
$ sudo -u postgres createuser --superuser $USER
$ sudo -u postgres createdb $USER
You should now be able to use psql and other commands.
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.