Tuesday, 16 December 2008

PostgreSQL management on the iPhone

Back in the summer Bob Zurek (CTO at EnterpriseDB) persuaded me to spend some time on a simple iPhone application for remote administration of PostgreSQL and Postgres Plus. I decided to write it as a web app as I didn't really have the spare time to learn Objective-C to the level required and besides, there are certain advantages to handling most of the processing server-side - for example, paging of large result sets from arbitrary user queries can be made more efficient. As is often the way, other projects took over and it fell by the wayside despite being more or less complete, but I finally managed to find the spare cycles to get Sachin from our installer team to package it up (thanks Sachin) - and I'm pleased to say that as of today it's finally available through StackBuilder for those of you with an iPhone or iPod touch.

So, what does it do? Not a massive amount admittedly - it's designed primarily for emergency maintenance (and frankly, who wants to do their day to day work on a phone?), but as can be seen from the main menu there are options to view server info, connections, GUC variables, locks, prepared transactions, and the ability to run arbitrary SQL queries.

The Info tool for example, displays various details of the server you're connected to. The installer will setup a connection to the local server, but you can add as many more as you like:

Probably the most useful feature is the ability to run arbitrary SQL queries. Batches of rows are summarised in a list, with the usual option at the end to get fetch the next batch. Selecting a row will display the complete set of values - the screenshot below captures the row details sliding across the display from the right:

If you want to have a go, just select the pgPhoneHome download in StackBuilder.

Oh... and while I think of it, we also updated the installer for the PostgreSQL JDBC driver to v8.3-604 at the same time.

Enjoy :-)


  1. Wao, It looks greater than WinCE which I tried.:-)

  2. Is there a portable PostgreSQL version for windows?

    May something like XAMPP does with MySQL. Thanks!

    (newbie .NET programmer here, hope I don't disgust you *nix guyz)

  3. That's kinda the point of StackBuilder - Install PostgreSQL, and at the end of the installation it'll run StackBuilder, where you can choose from a bunch of different tools, drivers and applications to install. It'll install all the required dependencies for whatever you choose, and where appropriate, configure it to work with the PostgreSQL installation.

    Oh, and FYI - I'm one of the PostgreSQL Windows guys :-)

  4. While we’re on appliances, Dave Page shows off PostgreSQL management on the iPhone with an application he himself wrote. Stealth DBA for the bus-rise home.

    Log Buffer #128

  5. Is this available on stackbuilder for the Postgres Plus Advanced Server as well??

  6. Hi Kyle,

    StackBuilder isn't included as part of Postgres Plus Advanced Server yet. It should work with the existing 8.3 release on Windows, and will work with future 8.4 releases.

    The apps themselves should work just fine though - if you're running AS on Windows, just try running StackBuilder as normal and selecting your AS installation on the first screen. On Linux or Mac, you should choose the 'Remote Server' option, and enter the local server details manually when requested.

  7. Thanks for your help Dave. I am hoping to use this app to connect with my psql DB to retrieve GIS data for a mobile GIS application. I want to use pgPhoneHome as a starting point for my app to prove that I can connect to my server remotely from the iPhone.

    I am thankful that I came across this application since I have been stuck for several weeks trying to figure out how to make changes to my DB from my mobile device.