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:

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:

  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:
    deb http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg main
  2. 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
  3. Install Postgres 9.4:
    sudo apt-get install postgresql-9.4
  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

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:

    sudo -u postgres psql template1
  2. 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.

  3. 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
  4. Restart the database:

    sudo /etc/init.d/postgresql restart

    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):

    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).

  6. 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
  7. Restart Postgres (like in step 4) and check that you can login without -U postgres:

    psql template1

    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.

    createdb <dbname>

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:

export PGPASSWORD=roger

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

A poor man’s alternative to 4K

I use a MacBook Pro running VirtualBox to provide Linux development environment VMs. I recently explored the possibility of getting a 4K monitor to provide a much-needed increase in display capabilities. I learned that while a 4K monitor is currently beyond my humble hardware’s capabilities, Samsung has an affordable option that exceeds the resolution of my current external monitor by 33% and works with my old Mac.

8 million pixels

4K monitors are called that because their horizontal resolution is (nearly) 4K pixels. With 3,840 by 2,160 resolution, these monitors provide more than 8 million pixels of viewing pleasure. That means a 4K monitor can display four discrete 1920 by 1080 displays—at one time. That’s like getting four monitors in one!

fourKMonitor

 

 

 

 

 

 

 

Alas, there are a couple of catches:

  • 4K monitors are expensive. While prices did drop dramatically during 2014, entry-level 4K monitors currently hover at about $500 and go up substantially from there. 4K monitors aren’t quite a commodity yet.
  • Hardware compatibility is iffy, at best, right now. Driving a 4K monitor is beyond the reach of many of today’s PCs and video cards. Carefully check your existing hardware before buying a 4K monitor. Also, beware your cabling needs (more on this in a moment).
  • Read the fine print. It’s important to read all specs very carefully. It might be possible for your current hardware to support 4K, but only at a 30MHz refresh rate. For simple work with a text editor, that might be fine but for tasks that tax video output like video editing or Photoshop, you’ll probably want to ensure your hardware supports a 4K monitor at a 60MHz refresh rate.

It’s not 4K, but…

My personal laptop is a hand-me-down, late 2008, MacBook Pro. The memory’s been bumped to its 8GB max and it sports a 512GB SSD primary drive and its original 750GB SATA drive resides in what was the superdrive slot. Otherwise, it’s box stock, currently running Mavericks. The MacBook’s internal display provides 1440 x 90 but for external monitors it supports a maximum resolution of 2560 x 1080.

Given my old Mac’s constraints I’ve given up on the hunt for a 4K monitor for now. But while Christmas shopping at CostCo the other day, I saw the LG 25UM65 25″ monitor on sale for $199. Generally, anything wider than 23″ or so doesn’t get my attention. At a 1920 x 1080 resolution bigger isn’t always better. At that resolution, the sweet spot seems to be about 23 or 24 inches. After that, you’re not really gaining useful real estate you’re just getting bigger pixels. TheLG 25UM65 got my attention because its maximum resolution is 2560 x 1080. This resolution would provide two side-by-side 1280 x 1920 displays on the LG. Not quite four 1920 x 1080 displays, but better than a single 1920 x 1080 display by 33%.

I rolled the dice on the LG 25UM65; it seemed like a good partner for my old Mac and one of these days when I do get a more capable Mac that supports 4K, I’m hoping the LG will make a good second monitor for that setup. The purchase was a bit of a gamble because not only did I hope for the monitor to provide 2560 x 1080 on my Mac, but I also hoped that VirtualBox would work with the monitor so that I could exploit its screen real estate with Linux VMs.

Get the right cable

Attaching the LG monitor to the Mac required a $10 mini display port to display port cable. Be careful to note that a display port isn’t an HDMI port nor is it a Thunderbolt port. A Best Buy blue-shirt assured me otherwise–don’t believe anything the BB blue shirts say. To get 60MHx refresh rate 2560 x 1080 resolution with the LG monitor on the MacBook Pro, this mini display port to display port cable is an absolute requirement. With that cable, installation was a snap and the monitor did exactly what it claimed it would do. I didn’t need the software included with the monitor and I didn’t need to read any special directions. I just plugged the monitor in, ensured the Mac’s display settings were set to 2560 x 1080 for the external monitor, and got happy.

Linux VMs through VirtualBox play along with the LG monitor nicely, but often, on session start up I need to fiddle with the monitor settings (I’m pretty sure that with a little more effort fixing that can be done scripting xrandr but I haven’t cracked that nut yet). But that’s something I’ve often needed to do when using multiple display sessions with VirtualBox on my old 1920 x 1080 monitor. The result on the LG monitor is a very wide display that is great for having a browser, Sublime Text, a terminal session, and a file manager (and whatever else I need). An example screen shot is of the LG in action is shown below.

wide-screen

 

 

 

 

 

 

I also have the Mac’s internal 1440 x 900 display running another VirtualBox display just in case.

Highly recommended

The LG 25UM65’s colors are bright, its display is crisp, and with nearly the same physical footprint as my old 1920 x 1080 monitor, it provides 33% more viewing area. The LG monitor claims to have speakers but they don’t work through the display port cable on my Mac (having said that, I spent about three seconds trying to make them work).

Overall, the LG 25UM65 is a great value, especially at $200. That’s a little more than a similar-sized 1920 x 1080 monitor but you are getting 33% more pixels. The stand is not height adjustable, but it does adjust for angle. It does work with VESA wall mounts. If you get one of these extended resolution monitors, be sure to confirm your current PC’s video capabilities and carefully research your cabling needs. You’ll not fully exploit the power of this monitor otherwise.

LG 25UM64 monitor at Amazon  mini display port to display port cable

Core Git command line reference

Inspired by John Nickell and Jeffrey Way I am making a renewed commitment to use Git primarily from the command line. While the command line may seem like many steps backwards from using a rich graphical interface, I am finding that it makes me much more aware of exactly what each step of Git is doing and it provides infinitely more quick repeatability of Git tasks.

This is list of Git commands I find myself using over and over. Be sure to familiarize yourself with Git aliases; they go a long way towards making the command concise and effective.

The easy way to put Git on a Windows box is to install Github for Windows and then use its Git shell. The added bonus here is you have good GUI available for basic Git operations as well.

Initialize a local repository

git init 

Stage all file changes to the index

git add .

Stage all file changes, including deletes, to the index

git add -A

Commit changes

git commit -m ‘commit message here’
or
git commit -a -m ‘commit message here’
which automatically stages modified and deleted files before the commit. However, new files you have not explicitly added to Git with ‘git add’ are not affected.

Discard changes

To remove unstaged changes from a specific file:

git checkout path/to/file/to/revert

To delete all unstaged changes:
git checkout — .

Remove a file from the repository

git rm <file name>

Discard all changes since last commit

git reset –hard HEAD
See this link for more info.

Show the repository’s current status

git status

Show all commits in an abbreviated form

git log –pretty=oneline –abbrev-commit

Show the SHA1 for the current commit

git rev-parse HEAD

Change to another commit and then make it a branch

git checkout nnnnnn
where nnnnnn is the first six characters of the desired commit’s SHA1. This puts the repo in a detached HEAD state. Now make a branch out of the commit you just checked out:
git checkout -b new_branch_name
That new branch is made the current branch. You can now switch among branches.

Show all branches

git branch -v

Create a branch

git branch new_branch_name

Create a branch and immediately switch to it

git checkout -b new_branch_name

Switch to a different branch

git checkout new_branch_name

Delete a branch

git branch -d branch_name

Rename current branch

git branch -m

Rename a branch that isn’t the current branch

git branch -m

Show all modified files

git ls-files -m

Show all files

git ls-files

Create an annotated tag

git tag -a v{x.x.x} -m ‘tag message’

Delete a tag

git tag -d abcde
git push origin :refs/tags/abcde

Use Sublime Text as Git’s editor on Windows

git config –global core.editor “‘c:/program files/sublime text 2/sublime_text.exe’ -w”

Use Sublime Text as Git’s editor on a Mac

git config –global core.editor ‘sublime -n -w’
Where ‘sublime’ is a symlink in the $PATH. This Stack Overflow entry was helpful for this.

Show all aliases

git config –get-regex alias

Add a Git command alias

git config –global alias.nn ‘xxxx’
where nn is the name of the alias and ‘xxxx’ is the Git command and its arguments. For example:
git config –global alias.rplog ‘log –pretty=oneline –abbrev-commit’
Call this alias with this command line:
git rplog

Delete a Git command alias

git config –global –unset alias.nn

Clone a repo

git clone <uri>
From a command line, go to the parent folder where you want the repo located. Cloning the repo will create a child folder under the parent folder you selected.

List remote repos

git remote -v

Show a remote repo

git remote show origin

Remove remote repo location

git remote rm origin

Set remote repo location

git remote add origin <uri>

Update remote repo location

git remote set-url origin <uri>

Update a remote repo

git push -u origin –all or git push -u origin master

Update a remote repo with current tags

git push -u origin –tags

Filtering XML with LINQ to XML

Filtering an XML document with LINQ to XML is very easy.

<TRUCKS>
    <TRUCKCOUNT>3</TRUCKCOUNT>
    <TRUCK>
        <CARRIER>MERKIN TRANSPORTATION INC</CARRIER>
        <CARRIERPHONE>(919)258-0939</CARRIERPHONE>
        <UNITNO>509</UNITNO>
        <UNITTYPE>TRACTOR</UNITTYPE>
        <DIMS>
            <BOXLENGTH>0</BOXLENGTH>
            <BOXWIDTH>0</BOXWIDTH>
            <BOXHEIGHT>0</BOXHEIGHT>
        </DIMS>
        <SATELLITE>N</SATELLITE>
        <AVAILABLE>05/24/2012 07:30</AVAILABLE>
        <STATUS>In Service</STATUS>
        <ISCONFIRMED>Y</ISCONFIRMED>
        <TEAM>N</TEAM>
        <LOCATION>GAS CITY, IN</LOCATION>
    </TRUCK>
    <TRUCK>
        <CARRIER>NICKELL FLIGHT SCHOOLS</CARRIER>
        <CARRIERPHONE>(805)767-6700</CARRIERPHONE>
        <UNITNO>958</UNITNO>
        <UNITTYPE>VAN</UNITTYPE>
        <DIMS>
            <BOXLENGTH>144</BOXLENGTH>
            <BOXWIDTH>50</BOXWIDTH>
            <BOXHEIGHT>69</BOXHEIGHT>
        </DIMS>
        <SATELLITE>N</SATELLITE>
        <AVAILABLE>05/26/2012 07:00</AVAILABLE>
        <STATUS>On A Load</STATUS>
        <ISCONFIRMED>Y</ISCONFIRMED>
        <TEAM>N</TEAM>
        <LOCATION>GAS CITY, IN</LOCATION>
    </TRUCK>
    <TRUCK>
        <CARRIER>BEST BRAINS, INC</CARRIER>
        <CARRIERPHONE>(888)345-5655</CARRIERPHONE>
        <UNITNO>427</UNITNO>
        <UNITTYPE>MEDIUM STRAIGHT</UNITTYPE>
        <DIMS>
            <BOXLENGTH>276</BOXLENGTH>
            <BOXWIDTH>92</BOXWIDTH>
            <BOXHEIGHT>88</BOXHEIGHT>
        </DIMS>
        <SATELLITE>N</SATELLITE>
        <AVAILABLE>05/24/2012 09:00</AVAILABLE>
        <STATUS>In Service</STATUS>
        <ISCONFIRMED>Y</ISCONFIRMED>
        <TEAM>N</TEAM>
        <LOCATION>GAS CITY, IN</LOCATION>
    </TRUCK>
</TRUCKS>

Here is the C# to use LINQ to XML.

public void Main()
{   
    string fileName = @"C:\Contents\\Web\MyApp\App_Data\trucks.xml"; 
    string buffer = File.ReadAllText( fileName );
    XDocument xDoc = GetSortedTrucksXml( buffer );
    xDoc.Dump();
}

public XDocument GetSortedTrucksXml( string xmlBuffer )
{
    XDocument xdoc = XDocument.Parse( xmlBuffer );
    int Counter = 0;    
    var trucks = from s in xdoc.Element( "TRUCKS" ).Elements( "TRUCK" )
                 orderby (string)s.Element( "CARRIER" ), 
                         (string)s.Element( "UNITNO" ) 
                 select getNewTruckElement( s, Counter++ );

    var newDoc = new XDocument();
    newDoc.Add( new XElement( "TRUCKS", trucks ) );
    return newDoc;
}

private XElement getNewTruckElement( XElement s, int Counter ) {
    // Add as many elements from the TRUCK as needed 
    // here. 
    XElement result = new XElement( "TRUCK" ,
                          s.Element( "CARRIER" ), 
                          s.Element( "UNITNO" ),
                          s.Element( "UNITTYPE" ),
                          s.Element( "LOCATION" ),
                          s.Element( "DIMS" ) );

    result.SetAttributeValue( "Counter", Counter.ToString() );                  
    return result;
}