How to install PostgreSQL 9.5 on Ubuntu 16.04

PostgreSQL is an open source relational database system that has been around for well over a decade and has proven to be a great all around storage choice when developing a web application.

In this guide we are going to walk through installing PostgreSQL 9.5 on Ubuntu 16.04 from scratch so that we can eventually start using it with a Go application, but you can follow along with this guide to set up Postgres for use with pretty much anything, including Rails, Django, or Go.

Once we have Postgres setup we will also look at setting up the postgres role so that we can access it from any linux user using a password. This will make it easier for our applications to connect to the database without having to be run by the postgres system user.

Installation

The first thing we want to do before we start installing anything is to update apt-get. This will ensure that we are installing from an updated repository.

sudo apt-get update

After that finishes, we can go ahead and install all of the packages we are going to need.

sudo apt-get install postgresql postgresql-contrib

When you are prompted asking if you want to continue, type y and hit enter. This will go ahead and install both the postgres package and the postgres-contrib package, which adds some additional functionality to Postgres.

Congrats! You should now have Postgres installed, but by default you need to be logged into the postgres user account to access PostgreSQL. That likely isn’t what you want - instead you probably want to set a password for postgres role and then use that password to log into Postgres from another user.

Setting up a password for the postgres role

By default, local connections to PostgreSQL use the peer authentication system. That means that instead of asking you for a password, they check to see if you are currently logged into a system user that matches the user name in Postgres.

We are going to change the way we do authentication and instead tell Postgres to use an encrypted password, but first we need to actually set a password for the postgres user. To do this we need to open up psql as the user postgres.

sudo -u postgres psql

You should see output that looks something like this:

could not change directory to "/root": Permission denied
psql (9.5.4)
Type "help" for help.

postgres=#

Don’t worry about the permission denied error. This isn’t important right now, I promise.

Now that we are connected to Postgres, we want to change the password for the user postgres. Be sure to replace the xxxxxxx below with an actual password.

# Replace xxxxxxx with your own password
ALTER USER postgres WITH ENCRYPTED PASSWORD 'xxxxxxx';

What we are doing here is telling Postgres that we want to update the user postgres by setting an encrypted password of xxxxxxx. If you did this correct, you should get the following output.

ALTER ROLE
postgres=#

That is all we need to do inside of Postgres. Go ahead and quit by typing \q and then hitting enter.

Now that we have a password set for the postgres user we want to update Postgres to use this password. To do this we need to edit the pg_hba.conf file.

# Feel free to replace nano with an editor of your choice
sudo nano /etc/postgresql/9.5/main/pg_hba.conf

Look for an uncommented line (a line that doesn’t start with #) that has the contents shown below. The spacing will be slightly different, but the words should be the same.

local   all   postgres   peer

The last part of this line is what we want to change. This is what determines how we authenticate the postgres user when making a local connection. Instead of peer, which uses your system user name to authenticate you, we want to use md5 which uses an encrypted password for authentication. Replace the word peer with md5.

local   all   postgres   md5

Press ctrl+x to close nano, then hit y to confirm that you want to save, and hit enter to confirm the same file name as the original.

Now we need to restart Postgres so the changes take effect.

sudo service postgresql restart

Now if we want to connect to PostgreSQL we can using the postgres user and a password.

psql -U postgres
# When prompted for your password, type it in

Up Next…

Now that you have PostgreSQL 9.5 installed you are ready to start using it! Check out the next article in this series to learn how to do that - Creating PostgreSQL databases and tables with raw SQL.