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

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

Laravel’s Eloquent ORM versus Query Builder

Laravel offers two ways to work with your database:

  • Eloquent ORM. Eloquent provides an active record-based ORM. It provides a fluent interface with built-in relational capabilities.
  • Query Builder. The Query Builder provides a flexible, and also fluent, way to create SQL queries. It provides an intuitive way to create SQL queries with nearly all of the flexibility of using SQL direct.

Making my way through learning Laravel, most of the videos and articles I read focused on using Eloquent for database access. However, I found that using Eloquent for generating JSON, at least for some tasks, limited the control I had over how result set JSON was generated. This article shows how I turned to the Query Builder for generating the result sets to be expressed as JSON.

As I make my way further into Laravel, I am sure there will tasks for which Eloquent is superbly suited. However, I learned this weekend that there are also tasks for which the Query Builder is superbly suited.

Using Eloquent ORM

This Eloquent code (assuming the corresponding Laravel relations have been established)

$tasks = Task::with( ['category', 'state', 'type'] )-&gt;
           where('status_id', '&lt;&gt;', 3)-&gt;
           orderBy('description')-&gt;
           orderBy('id', 'desc')-&gt;get(['tasks.id',
                                       'tasks.type_id',
                                       'tasks.status_id',
                                       'tasks.category_id',
                                       'tasks.description',
                                       'tasks.start_time']);
return $tasks;

produces this JSON:

[
    {
        id: "2",
        type_id: "2",
        status_id: "1",
        category_id: "1",
        description: "Dr Schnieder",
        start_time: "2013-08-04 16:00:00",
        category: {
            id: "1",
            text: "Personal",
            created_at: "2013-07-17 04:47:37",
            updated_at: "2013-07-17 04:47:37"
        },
        state: {
            id: "1",
            text: "Open",
            created_at: "2013-07-17 04:47:37",
            updated_at: "2013-07-17 04:47:37"
        },
        type: {
            id: "2",
            text: "Appointment",
            created_at: "2013-07-17 04:47:37",
            updated_at: "2013-07-17 04:47:37"
        }
    },
    ...
]

Notice how the data from the related tables is placed into seperate JSON nodes (ie, the category, state, and type nodes). Unless you use the $hidden array in a model definition, there isn’t a way to limit the columns returned from related tables. The $hidden array can’t be changed at runtime so it must be specified in the model. Thus you either need special-cased models or learn to live with all of the columns being returned for related tables.

Laravel’s Query Builder to the rescue

Using Laravel’s Query Builder, you can get much less verbose JSON. The following query

$tasks = DB::table('tasks')
         ->join('task_categories', 'tasks.category_id', '=', 'task_categories.id')
         ->join('task_states'    , 'tasks.status_id'  , '=', 'task_states.id')
         ->join('task_types'     , 'tasks.type_id'    , '=', 'task_types.id')
         ->orderBy('tasks.description')
         ->orderBy('tasks.id', 'desc')
         ->where('status_id', '<>', 13)
         ->select('tasks.id',
                  'tasks.status_id',
                  'tasks.notes',
                  'tasks.start_time',
                  'tasks.end_time',
                  'tasks.labels',
                  'tasks.description', 
                  'task_categories.text as category',
                  'task_states.text as status',
                  'task_types.text as type')
         ->get();

return $tasks;

produces this JSON. The Query Builder provides a greater level of control over what is included in the query. For example, note how you can use the ‘as’ clause in the select() method to alias result columns.

[
    {
        id: "2",
        status_id: "1",
        notes: "check-up",
        start_time: "2013-08-04 16:00:00",
        end_time: "2013-08-04 17:00:00",
        labels: "",
        description: "Dr Schnieder",
        category: "Personal",
        status: "Open",
        type: "Appointment"
     },
    ...
]

 

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;
}