Today I Learned how to Install PostgreSQL in Ubuntu

For an upcoming project I’m thinking of using PostgreSQL.  I’ve heard lots of great things about PostgreSQL in the past but have been too scared lazy busy to try it.

What changed my mind was JetBrains DataGrip database client.  I’m sure there are other PostgreSQL clients but DataGrip is included in my JetBrains subscription so why not give it a try.   I’m a sucker for GUI database clients.  As a generalist it’s too hard to remember all the command lines for each individual database.  Plus it’s really hard to view more then a few rows or columns of data in the command line.

Anyway, let’s get to installing Postgresql in a Ubuntu development environment.  The initial installation instructions can be found here.  First lets add the PostgreSQL Apt Repository.  We do this so we can get he latest version of PostgreSQL and aren’t stuck with the Ubuntu version.

First create a file that will point to the PostgreSQL Apt Repository:

sudo nano /etc/apt/sources.list.d/pgdg.list

Then add the following to the file:

deb -pgdg main

Finally import the repository signing key:

wget --quiet -O - | sudo apt-key add -

Now do a apt update and you should see the PostgreSQL listed:

sudo apt update

Finally install it:

sudo apt install postgresql

You can check if PostgreSQL was installed correctly by trying to connect to it:

sudo su - postgres psql

Notice you had to switch to the postgres user before attempting to connect.  This is a new user that was created during the PostgreSQL installation and is the default user for new database installs.

By default PostgreSQL does not have a default password in Ubuntu.  You can only login via the above command and can’t connect using other methods, such as DataGrip.  To change the postgres default user password run the following command when logged into PostgreSQL:

ALTER USER postgres PASSWORD 'password';

Once that is done you can logout of PostgreSQL by typing “\q”.

Now lets try to connect to our localhost PostgreSQL install in DataGrip.  Run DataGrip then choose File–>Data Sources.  Then click PostgresSQL and you should see something similar to the below.

DataGrip PostgreSQL Data Source

DataGrip PostgreSQL Data Source Advanced Options

If prompted download the driver files.  Now lets try to create a connection by clicking the green plus sign in the top right and choosing PostgreSQL:

DataGrip PostgreSQL Create Datasource Menu

You should see something similar to the below:

DataGrip PostgreSQL Create Datasource Download Missing Drivers

If there is a message saying a driver is missing then download it.  If this is your first time installing PostgreSQL there will be no database so leave that field blank but fill in the username and password.  The username is “postgres” and the password if the one you created in the above ALTER statement.  Click Test Connection to make sure everything works.

Test Database Connection

When you close this form you might be prompted to store the password in your keyring.  You don’t have to but I like too so I don’t have to keep entering it.

Key Chain Prompt

Now you should be able to see the PostgreSQL database in DataGrip:

PostgreSQL Database in DataGrip


P.S. – Spotify listed Bored to Death by Blink 182 as my most played song of 2017.  The second most played song was Sober also by Blink 182.  I wonder what targeted ads I would get if that information feel into Google or Facebook’s hands.

Life is too short to last long

This entry was posted in Code Examples, Software Development, Today I Learned and tagged , , . Bookmark the permalink.