Installing PostgreSQL 9.4 on Ubuntu 14.04

postgresql-logo

Postgres 9.4 is a powerful (albeit silly-named) open source database. With 9.4 some powerful features have been added to enhance Postgres’s support for Json. The claim is that Postgres 9.4 offers the power of a relational database coupled with flexible document database features offered by databases such as Casandra, CouchDB, and Mongo DB. This post documents how to install and initially configured Postgres on Ubuntu 14.04.

My first attempt to install Postgres 9.4 on Ubuntu 14.04 failed. After issuing:

this message is displayed:

Having been down similar Ubuntu roads a time or two in the past, the first thing I was did was:

Which resulted in the same package-not-found message being displayed.

Avoid the Postgres graphical installer!

In a desperate attempt to install Postgres 9.4, I used the Postgres graphical installer. While this installer is convenient, it installs components to odd directories and it doesn’t integrate with apt-get. I also had path trouble and psql command line issues. Even though the graphical installer did seem to install a working Postgres 9.4 when used through pgAdmin III (which is also installed with the graphical installer), there were enough off-by-one oddities with the graphical installer that I gave up on it. In its last effort to annoy me, I was also unable to uninstall Postgres when installed with the graphical installer. When installed that way it can’t be uninstalled with with apt-get and its uninstaller binary wouldn’t run for me. The Postgres graphical installer confirmed for me that with Linux the command line is your friend and point-and-click installations should generally be avoided.

Third time’s the charm

My third attempt was my successful attempt to install Postgres 9.4. Had I had the patience to read the Postgres site a little more closely, I would have probably avoided the debacle of the graphical installer.

Update the apt-get repository using the instructions on this Postgres page (see the section PostgreSQL Apt Repository). There is a nifty dropdown on the page that, after you select your Ubuntu version, provides the correct command lines for you. The four steps from that page to install Postgres 9.4 on Ubuntu 14.04 are:

  1. Update (or create it if it isn’t there) the file named /etc/apt/sources.list.d/pgdg.list and add this line to it:
  2. Update the apt-get repostitor with the following two lines (note the first threes lines are a single continued command line)
  3. Install Postgres 9.4:
  4. Install pgAdmin III:

There isn’t yet an apt-get package for pgAdmin 1.20.0. Step four above installs pgAdmin 1.18.0 which works with Postgres 9.4 but doesn’t provide 9.4-specific features. If you want to install pgAdmin 1.20.0 you currently need to install it from source. (Update: on 6 January, Ubuntu’s automatic software updates upgraded pgAdmin to 1.20.0.)

To confirm Postgres’s installation, use this command to display the version installed:

Configuring Postgres

After installing Postgres, you need to do a little user configuration before you can use it. I struggled with this task until I found this answer on StackOverflow. It very clearly provides the steps necessary to perform first-time Postgres configuration. The steps below are StackOverflow user manur‘s work; I’m repeating them here for convenience.

  1. Connect to the default database with user postgres:

  2. Set the password for user postgres, then exit psql (Ctrl-D or \q):

    Don’t forget the trailing semicolon for the SQL statement above.

  3. Edit the pg_hba.conf file

    And change peer to md5 on the line referencing postgres:

  4. Restart the database:

    Here you can check it worked with psql -U postgres.

  5. Create a user having the same name as you (to find it, you can type whoami):

    The options tell Postgres to create a user that can login, create databases, create new roles, is a superuser, and will have an encrypted password. The really important ones are -P -E, so that you’re asked to type the password that will be encrypted, and -d so that you can do a createdb.

    Take care entering the new user password. You’ll be prompted for a password three times. You’ll first be asked twice for the new password for the new user, and then once for the postgres password (the one specified on step 2).

  6. Again, edit the pg_hba.conf file (see step 3 above), and change peer to md5 on the line concerning all other users:

  7. Restart Postgres (like in step 4) and check that you can login without -U postgres:

    Note that if you do a mere psql, it will fail since it will try to connect you to a default database having the same name as you (ie. whoami). template1 is the admin database that is here from the start.

  8. Now you should be able to create a database.

Configuring the Linux environment

Postgres can use environment variables to provide several default values. At the very least, to execute SQL statements through psql, set the PGPASSWORD environment variable with the new user’s password:

Assuming you had created a database named geocode and a file named createtable.sql with an SQL Create Table statement in it, you could perform that SQL with this psql command line:

12 thoughts on “Installing PostgreSQL 9.4 on Ubuntu 14.04

  1. Thaks for the info, based on what I had to do at step 3, I think you might have left out the “ql” in:
    sudo apt-get install postgresql-9.4
    sudo apt-get install postgres-9.4

  2. Hi rp, lovely example. I also went through the graphic installer saga. Uninstalling, i did using the “uninstall-postgresql.dat” which i don’t remember where that file was but a quick “locate ‘the file name’ ” terminal command should locate it.
    If i had been told about the graphic installer I would have stayed away.
    Thanks for the run through, very informative.

Leave a Reply

Your email address will not be published. Required fields are marked *