Introduction

PostgreSQL is the most popular RDBMS in Ruby world. When I started Ruby development in early 2013 75% of Rails projects would use PostgreSQL. 2 years later when I became a full time Ruby developer, its market share rose to 85%. Unlike MySQL PGSQL is ACID compliant (atomicity, consistency, isolation, durability). In addition it has a strong community, support better sub-queries and more.

Note: This tutorial assumes you are using Ubuntu 20.04. If you are on a Mac running MacOS, you can continue the tutorial on a Ubuntu 20.04 Server virtual machine or install it on your system using Homebrew. Installation through Homebrew will receive its own tutorial.

Note: This is not written in the intention to be used in a production environment (ex: using Digital Ocean, Linode etc to host an app). Although it will work fine, I would make some changes. When I find the time write another article, I will cover the topic of setting up PostgreSQL in a production environment as well.

Installation

First we will update our package information and then install the database engine. In addition to the database engine we will need the postgresql-contrib which will add additional functionality we would need.

sudo apt update
sudo apt install postgresql postgresql-contrib

Note: Applications available through apt would be fixed throughout the lifetime of your installation. For example the PostgreSQL version available for Ubutu 20.04 is 10 (10.4 to be specific). If version 13 is released I would have to use the PostgreSQL APT repositories to install it. I will write a tutorial on the matter, but for now this documentation is good enough for one to install a specific version of PostgreSQL.

Understanding Roles and Databases

"Roles" is a concept used in PostgreSQL database that is similar to Unix style users and groups. Roles handle authentication and authorization. By default PostgreSQL uses ident authorization that matches with your Linux or Unix system account. For example a Linux/Unix user logged in as johndoe can sign in to a role johndoe if it exists in the database without the need of a password. You can also have user accounts that require passwords.

Out of the box PostgreSQL has the postgres role. You can access psql - the PostgreSQL Interactive Terminal through the postgres user account created upon installation of postgres.

sudo -i -u postgres
psql

Note: You can quit psql - with \q command.

postgres=# \q

or... you can boil down the 2 commands above used to open psql to one command as...

sudo -u postgres psql

Create a new role

Since PostgreSQL uses indent, we should first create a role with a similar name to your Linux / Unix username. In my computer it is ziyan. If it is not already showing on the terminal, you can use the who command.

who
ziyan    :0           2020-09-06 08:48 (:0)

Now that we know the username, lets create the user.

sudo -u postgres createuser --interactive

Ex output:

Enter name of role to add: ziyan
Shall the new role be a superuser? (y/n) y

One more thing PostgreSQL database assumes is that every role has a database named after it self. If it doesn't when you access psql without specifying the database it will give an error. So lets create a database for you.

sudo -u postgres createdb ziyan

Now we should be able to psql from our user account it self without needing sudo -u postgres psql.

psql

This will connect you to the database we created with your username. For me that is ziyan. Now assume we want to connect to another database, we can use the -d <database-name> option to specify the database.

psql -d postgres

That is it folks, now you have PostgreSQL configured and ready for your next Ruby on Rails, Django, Laravel or other project.