Postgres OnLine Journal   

In this exercise, we'll go thru our steps for upgrading a Redhat Enterprise Linux 64-bit PostgreSQL box from PostgreSQL 8.3 to PostgreSQL 8.4. If you don't have any kind of PostgreSQL installed on your box, you can skip the Upgrade step.

Updgrading from PostgreSQL 8.* to PostgreSQL 8.4

If you are starting from scratch -- just skip this section.

If you are upgrading from 8.4.0 to 8.4.1 you can get away with a simple
yum update postgresql
and skip the rest of this article.

If you are upgrading from PostgreSQL 8.3 to 8.4, in theory you can use PgMigrator, but in practice, particularly with a Yum install, you are bound to run into obstacles. If you are running an older version, you must dump and restore.

Debugmode Wink is a freeware piece of software for both business as well as personal use for doing screencasts and incorporating sound into your screen captures. The main useful format it outputs to is macromedia flash though you can output to PDF for handouts and so forth. While its not a PostgreSQL related item per se, it can be useful for making all sorts of tutorials including PostgreSQL tutorials that involve showing people how to do things on screen. We've started to experiment with tutorials of this format and hope to get into doing more hands on like tutorials. We'll probably be doing a using PostgreSQL in OpenOffice tutorial in this issue since it lends itself well to screen casting.

The upcoming version of PostGIS - PostGIS 1.5 will be an exciting one. It has native geodetic support in the form of the new geography type, similar in concept to SQL Server's geography support. For windows users, we have experimental binary builds hot off the presses for PostgreSQL 8.3 and 8.4

This is an unfortunate predicament that many people find themselves in and does cause a bit of frustration. You bring in some tables into your PostgreSQL database using some column name preserving application, and the casings are all preserved from the source data store. So now you have to quote all the fields everytime you need to use them. In these cases, we usually rename the columns to be all lower case using a script. There are two approaches we have seen/can think of for doing this one to run a script that generates the appropriate alter table statements and the other is to update the pg_attribute system catalog table directly.

This week is a busy week for events. While PostgreSQL is having its PostgreSQL West conference in Seattle, the biggest Open Source GIS conference of the year is happening in Sydney, Australia FOSS4G 2009. Sadly given our schedule and the distances of the commutes, we couldn't make either conference.

Mateusz Loskot pointed out that the video is out for Paul Ramsey's FOSS4G 2009 Keynote speech on Beyond Nerds Bearing Gifts: The Future of the Open Source Economy. I think its a very important distinction Paul makes between selling software and selling a product, that a lot of people miss when trying to evaluate the solvency of open source software.

For those who don't know Paul, he's one of the co-founders of the PostGIS project and Refractions Research.

Vacuuming and analyzing is the process that removes dead rows and also updates the statistics of a table. As of PostgreSQL 8.3, auto vacuuming (the process that runs around cleaning up tables), is on by default. If you are creating a lot of tables and bulk loading data, the vacuumer sometimes gets in your way. One way to get around that is to disable auto vacuuming on the tables you are currently working on and then reenable afterward. You can also do this from the PgAdmin III management console.

In our prior story about allocating people with the power of window aggregation, we saw our valiant hero and heroine trying to sort people into elevators to ensure that each elevator ride was not over capacity. All was good in the world until someone named Frank came along and spoiled the party. Frank rightfully pointed out that our algorithm was flawed because should Charlie double his weight, then we could have one elevator ride over capacity. We have a plan.

This is along the lines of more stupid window function fun and how many ways can we abuse this technology in PostgreSQL. Well actually we were using this approach to allocate geographic areas such that each area has approximately the same population of things. So you can imagine densely populated areas would have smaller regions and more of them and less dense areas will have larger regions but fewer of them (kind of like US Census tracts). So you have to think about ways of allocating your regions so you don't have a multipolygon where one part is in one part of the world and the other in another etc. Using window aggregation is one approach in conjunction with spatial sorting algorithms.

The non-spatial equivalent of this problem is how do you shove people in an elevator and ensure you don't exceed the capacity of the elevator for each ride. Below is a somewhat naive way of doing this. The idea being you keep on summing the weights until you reach capacity and then start a new grouping.

A while ago we wrote about DZone RefCards cheatsheets and how its a shame there isn't one for PostgreSQL. They are a very attractive and useful vehicle for learning and brushing up on the most important pieces of a piece of software or framework. Since that time we have been diligently working on one for PostgreSQL to fill the missing PostgreSQL slot. The fruits of our labor are finally out, and a bit quicker than we expected. The cheatsheet covers both old features and new features introduced in PostgreSQL 8.4. We hope its useful to many old and new PostgreSQL users.

The Essential PostgreSQL Refcard can be downloaded from Essential PostgreSQL http://refcardz.dzone.com/refcardz/essential-postgresql?oid=hom12841

Just read that Microsoft has formed a new foundation called CodePlex foundation, presumably to spinoff their Code plex site and allow it to stand separately from Microsoft. The mission appears to be to allow an easier avenue for developers working for proprietary software companies to contribute to open source projects.

Monty has some details about this on his blog The CodePlex Foundation: Why is Microsoft founding it?.

The line up of people they have on their advisory board (including Monty) and board of directors is interesting CodePlex About. I'm particularly happy that Miguel De Icaza is on the board since he is one of my favorite people and I believe shares my pragmatic ideals on the synergy between open source and non-open source software. I wonder what it takes to get on this board. It would be really nice if someone in the PostgreSQL community were on this board just to ensure the needs of the PostgreSQL community (especially our growing number of windows users) is well represented.

As to the argument of Monty's that software for sell is dying, not sure I quite agree though haven't given it much thought. Certainly I would like to think that service for sell is rising since that's the business we are in and enjoy most. One thing I believe is that software is getting more complicated and people expect more. With that said even as a company that sells software, you would be foolish not to try to leverage on the open source software out there that fits nicely into your codebase. You just won't be able to compete even with the sole proprietor next door who is with it.

One of the small little treats provided in PostgreSQL 8.4 is the new pg_terminate_backend function. In the past when we wanted to kill runaway postgresql queries issued by a database or user or hmm us, we would call the pg_cancel_backend function. The problem with that is it would simply cancel the query in the backend process, but often times the offending application would simply launch the same query again.

In PostgreSQL 8.4 a new function was introduced called pg_terminate_backend. This doesn't completely replace pg_cancel_backend, but basically does what you do when you go into say a Windows Task manager and kill the offending postgres process or on Linux, you call a kill command on a postgres process. Its nicer in the sense that you can do it all within PostgreSQL and you can use the pg_stat_activity query to help you out a bit. Also you don't run the risk as easily of killing the root postgres process and killing the postgres service all together.

One of the most common questions people ask is Which tools work with PostgreSQL. In a sense the measure of a database's maturity/popularity are the number of vendors willing to produce management and development tools for it. Luckily there are a lot of vendors producing tools for PostgreSQL and the list is growing. One set of tools people are interested in are Database administration, ER diagramming, Query tools, and quickie application generators (RAD).

For this issue of our product showcase, we will not talk about one product, but several that fit in the aforementioned category. All the listed products work with PostgreSQL and can be used for database administration and/or architecting or provide some sort of light reporting/rapid application building suite. By light reporting/application building, we mean a tool with a simple wizard that a novice can use to build somewhat functional applications in minutes or days. This rules out all-purpose development things like raw PHP, .NET, Visual Studio, database drivers etc. Things we consider in this realm are things like OpenOffice Base and MS Access. Most of these tools are either free or have 30-day try before you buy options.

You can't really say one tool is absolutely better than another since each has its own strengths and caters to slightly different audiences and also you may like the way one tool does one important thing really well, though it may be mediocre in other respects. We also left out a lot of products we are not familiar with and may have gotten some things wrong.

If we left out your favorite product and you feel it meets these criteria, or you feel we made any errors, please let us know, and we'll add or correct it. We will be including Free open source as well as proprietary products in this mix. If we left out what you consider an important criteria, please let us know and we'll try to squeeze it in somewhere.

As David Page already noted, Leo and I are taking over responsibility of building PostGIS windows one-click installers/stack builder from Mark Cave-Ayland. The PostGIS 1.4 windows packaging was a little late in coming this time since it was our first and also some things changed in the PostGIS packaging for 1.4. Even so we made some mistakes such as statically compiling libproj in with the postgis-1.4.dll and forgetting some new images in the packaged html help, which we will fix in 1.4.1 release.

Mark will still be providing a supporting role and helping out when we screw up or helping us if we run into compile issues as we go along so he's not going away; he will be a great safety net. When Mark started his role a long time ago, he was as many would like to say "Very entrenched in the dark side," and over the years, he has seen the light. As a result, these moments of catching issues in the PostGIS release cycle that effect windows users such as troubleshooting the memory bug in the loader files that affected Windows Vista users and testing on various Windows OS, has fallen on us, because well we have access to all windows os.

It also became painful for Mark to walk in the shadow of darkness once he had seen the light. Luckily we are still windows addicts so this having to constantly test on Windows and building for Windows is something we would naturally do anyway and yes as shocking as it sounds we do run some production PostgreSQL apps on windows and it works pretty well, thank you very much. We don't expect this to change any time soon.

As part of this change, we hope to provide more interim windows builds of PostGIS so windows users can experiment with future releases before they come out. Yes compiling on windows is a tad bit more difficult than on Linux. These PostGIS windows experimental builds can be found http://www.postgis.org/download/windows/experimental.php

Main changes in PostGIS

• The PostGIS steering committee has agreed to be good and not be adding new functions between micro releases of PostGIS as we have done in the past and as we've been smacked around for. As part of that change, from PostGIS 1.4 moving forward each micro version will overwrite the previous micro version in the MS Windows registry. E.g. 1.4.1 will overwrite 1.4.0 so no need to uninstall the old and reinstall to get rid of registry junk. Just install on top of your existing 1.4.
• As of PostGIS 1.4 it is possible to run different versions of PostGIS in different databases on teh same PostgreSQL server install since the .so/.dll from minor to minor have unique names (naming is postgis-1.4.so (postgis-1.4.dll), postgis-1.5.so etc). This is mostly useful for testing and comparing different versions of PostGIS before you officially upgrade and if you have several different spatial apps using different databases, you don't risk breaking them all at once.
• PostGIS is now an official incubation project of OSGEO. Things are still being drafted. But I guess that means our PostGIS defacto steering committee composed of Kevin, Paul, Mark, and myself is now more or less official.

A very long time ago, we wrote an article on how to use PostgreSQL to show the fully qualified name of an item in an inventory tree. Basically we were modeling a paper products tree. The original article can be found here Using PostgreSQL User-Defined Functions to solve the Tree Problem and was based on PostgreSQL 7.4 technology.

We'll repeat the text here for completeness and demonstrate the PostgreSQL 8.4 that solves this and more efficiently.

Comparison of PostgreSQL 8.4, Microsoft SQL Server 2008, MySQL 5.1

In our May 2008 issue of Postgres OnLine Journal, we cross compared Microsoft SQL Server 2005, MySQL 5, and PostgreSQL 8.3. Some people mentioned well since 8.4 has now come out, shouldn't we go back and update the reference. We deliberated and decided not to. To be fair all 3 products have released new versions, so it would seem unfair to compare a newer PostgreSQL against older versions of MS SQL Server and MySQL. We have therefore decided to repeat our exercise and include parts people felt we should have covered, as well as comparing the latest and greatest stable release of each product.

People ask us time and time again what's the difference why should you care which database you use. We will try to be very fair in our comparison. We will show equally how PostgreSQL sucks compared to the others. These are the items we most care about or think others most care about. There are numerous other differences if you get deep into the trenches of each.