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:
apt-get install postgresql-9.4
this message is displayed:
Couldn't find any package by regex postgres-9.4
Having been down similar Ubuntu roads a time or two in the past, the first thing I was did was:
apt-get update apt-get upgrade apt-get install postgresql-9.4
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:
- 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:
deb http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg main
- Update the apt-get repostitor with the following two lines (note the first threes lines are a single continued command line)
wget --quiet -O - \ https://www.postgresql.org/media/keys/ACCC4CF8.asc | \ sudo apt-key add - sudo apt-get update
- Install Postgres 9.4:
sudo apt-get install postgresql-9.4
- Install pgAdmin III:
sudo apt-get install pgadmin3
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:
$ psql --version
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.
Connect to the default database with user postgres:
sudo -u postgres psql template1
Set the password for user postgres, then exit psql (Ctrl-D or \q):
ALTER USER postgres with encrypted password 'xxxxxxx';
Don’t forget the trailing semicolon for the SQL statement above.
Edit the pg_hba.conf file
sudo vim /etc/postgresql/9.4/main/pg_hba.conf
And change peer to md5 on the line referencing postgres:
local all postgres peer md5
Restart the database:
sudo /etc/init.d/postgresql restart
Here you can check it worked with psql -U postgres.
Create a user having the same name as you (to find it, you can type whoami):
createuser -U postgres -d -e -E -l -P -r -s <my_name>
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).
Again, edit the pg_hba.conf file (see step 3 above), and change peer to md5 on the line concerning all other users:
local all all peer md5
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.
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:
psql -d geocode -a -f createtable.sql