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.
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.
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
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
# 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
local all postgres md5
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
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.
Getting started with SQL in Go can be tricky. How do you design your code so that it is testable? How do you make your database connections available in your http handlers?
In my course, Web Development with Go, we use the SQL database in a real web application and cover all of these details in more. You will learn how to properly build a robust, testable database layer and how to make it available to your http handlers.
If you sign up for my mailing list (down there ↓over there →) I'll send you a FREE sample so you can see if it is for you. The sample includes over 2.5 hours of screencasts and the first three chapters from the book.
You will also receive notifications when I release new articles, along with other freebies that I only share with my mailing list.
Jon Calhoun is a full stack web developer who also teaches about Go, web development, algorithms, and anything programming related. He also consults for other companies who have development needs. (If you need some development work done, get in touch!)
Jon is a co-founder of EasyPost, a shipping API that many fortune 500 companies use to power their shipping infrastructure, and prior to founding EasyPost he worked at google as a software engineer.
More in this series
This post is part of the series, Using PostgreSQL with Go.
Spread the word
Did you find this page helpful? Let others know about it!
Sharing helps me continue to create both free and premium Go resources.
Want to discuss the article?
©2018 Jonathan Calhoun. All rights reserved.