Launching your first PostgreSQL server on Ubuntu 16.04

I had a difficult time launching PostgreSQL on my local machine.

With Postgres, you have to run a server that handles database requests. It’s not the same as your web server that routes web pages. It’s a dedicated Postgres server that makes your databases available to your web application.

This guide will walk you through setting up a local PostgreSQL server on your Linux (Ubuntu 16.04, specifically) computer. I’m mostly writing this guide so that I have these steps to refer back to in the future. This is the guide I wish I could have found on the internet, instead of spending time troubleshooting each problem myself.

Step 1. Update your system

sudo apt-get update && sudo apt-get upgrade

Troubleshooting 1.1

An error occurred during the signature verification. The repository is not updated and the previous index files will be used. GPG error: http://apt.postgresql.org/pub/repos/apt xenial-pgdg InRelease: The following signatures couldn't be verified because the public key is not available: NOPUBKEY 7FCC7D46ACCC4CF8

This error probably occurred because I installed PostgreSQL incorrectly the first time (don’t ask…) and had to remove it. The solution was to get a new public key from the Ubuntu key server:

sudo apt-key adv — recv-keys — keyserver keyserver.ubuntu.com 7FCC7D46ACCC4CF8

Step 2. Install PostgreSQL via apt-get

Following the PostgreSQL docs and several guides I found online, I installed PostgreSQL and PostgreSQL-contrib via apt-get:

sudo apt-get install postgresql postgresql-contrib

Step 3. Meet the postgres user on your computer

When you want to do something in Postgres, you’ll probably want to switch to that user on your computer, since postgresowns all the files that the install script created.

The postgres user is the one in charge of everything Postgres on your machine. So, switch to that user:

sudo -i -u postgres

Step 4. If you’re lucky, start psql

psql

And you’ll get an interactive screen and a prompt for issuing SQL commands to your PostgreSQL database. If that happens for you, amazing!

That’s not what happened for me.

Troubleshooting 4.1.

psql: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

The issue is kind of obvious — the server for PostgreSQL isn’t running yet! Sure, I installed the package and logged in as postgres, but I didn’t boot up the postgres server at all.

Typically, the installer should start Postgres automatically. But because I have a unique setup (I work on a chromebook running Linux as a chroot), I need to start the Postgres server manually on my machine.

Now, I needed a command to boot the server. First, I found pg_ctl. And that is indeed the command to start a server. I even read the whole man page for it to make sure I understood how it worked. But when I typed it into the command line I was told that command didn’t exist.

Stack Overflow to the rescue! A commenter on a post about pg_ctl mentions that you should really use pg_ctlcluster to start a server, as it wraps the pg_ctl command. From the man page:

pg_ctlcluster determines the cluster version and data path and calls the right version of pg_ctl with appropriate configuration parameters and paths.

Read the rest of the man page and you’ll find the correct syntax for starting a local server:

pg_ctlcluster [version] [name] [action]

In our case, we want to start a version 11 server and name it main, so:

pg_ctlcluster 11 main start

07/26/19 update: I’ve found that `$ sudo /etc/init.d/postgresql restart` will also do the trick in my particular setup

Step 5. Test it out

Jeez, what a mouthful.

So, does it work? Let’s try it:

(xenial)postgres@localhost:~$ psql -l

List of databases

Name | Owner | Encoding | Collate | Ctype | Access privileges

postgres | postgres | UTF8 | C.UTF-8 | C.UTF-8 |

template0 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres

template1 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres

(3 rows)

You have no idea how happy I was when I finally saw that ^^^ table after all that troubleshooting.

Conclusion

Free Resources

Want my best content on web development and becoming a better programmer?

Join my free email series.

Top writer in Technology | Backend Web Developer | bennettgarner.com

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store