Speaking of the 84%…

HT to Sophia Parafina for the 84%.

UPDATE: The NSIS script at utility batch file discussed here is now on github at https://github.com/geobabbler/pgstandalone. I’ll post a readme in the next day or so.

A few months ago, I asked the following question on Twitter and got this reply from Paul Ramsey:

http://twitter.com/pwramsey/status/136565522836897796

We are working with a Federal Government customer that had the interesting policy that users can install software as long as it makes no changes to the Windows registry. These users are currently running a mix of Windows 7 and XP. We are working with them to help manage one of their data models. In this case, it’s more about performing configuration management on the model/standard itself rather than physical databases with real data in them. It’s a topic we touched on over at the Zekiah blog here and an approach we have used successfully for years to manage the SDSFIE data standard.

So we’ve applied the technique to another data standard for another Federal organization. In this case, we need to distribute tools to help data modelers work with the platform-independent logical model (PIM) on their own desktops. Typically, this has been centrally managed and accessible via an implementation in SQL Server. (Don’t get wrapped around the term “platform-independent.” We use it in a different context within this work.) Since we have to distribute to desktops, and SQL Server makes a slew of registry entries, and PostgreSQL has no licensing issues, we decided to go the PostgreSQL route.

It turns out, this was pretty easy. After doing the work for our approach, I have subsequently found posts showing a similar approach (as always, it depends on the day and the search term), which was nice because they sort of validated what I had already done.

Paul was on the right track with using the Windows binaries from the PostgreSQL site. Natively, PostgreSQL doesn’t need any registry entries, which makes sense given that it also runs on Linux and Unix. Registry entries are typically introduced by the one-click installers that do helpful things like setting PostgreSQL up as a service and a few other things that require registry entries on Windows. These are very useful things that I avail myself of when I use PostgreSQL on Windows. In this particular use case, those features were not helpful.

One thing that you’ll notice when you use the one-click installer is that you’ll end up with a batch file called “pg_env.bat” in the install folder for your version. It looks something like this:

[sourcecode language=”powershell”]
@ECHO OFF
REM The script sets environment variables helpful for PostgreSQL

@SET PATH="C:Program Files (x86)PostgreSQL8.4bin";%PATH%
@SET PGDATA=C:Program Files (x86)PostgreSQL8.4data
@SET PGDATABASE=postgres
@SET PGUSER=postgres
@SET PGPORT=5432
@SET PGLOCALEDIR=C:Program Files (x86)PostgreSQL8.4sharelocale
[/sourcecode]

As you can see, I’m working with version 8.4 of PostgreSQL. That version is what has been blessed but I am also rolling up an implementation of this approach for 9.1 as we expect to get that approval soon.

With this batch file and the unzipped binaries, we have everything we need to run PostgreSQL without running an installer that makes registry entries. Now all we need to do is tell each user to unzip the binaries, edit the batch file to point to the correct paths, open a command window and run the batch file…

…Clearly, there was a little more work to do to make this operate smoothly.

First, we wanted PostGIS in our build. As I mentioned before, the PIM represents a logical model that doesn’t store actual data, but we wanted it to know about PostGIS data types. Additionally, we wanted to distribute the PIM with PostgreSQL. In this case, we did a little bit of prep work using a standard install of PostgreSQL to add PostGIS (and its template database) and build/populate our PIM database.

Once we had this done, we moved the data directory over to our “unzipped” instance of PostgreSQL. We tested by running our batch file from the command prompt and starting PosgreSQL the same way. In our case, we changed the listening port to 54325. We then attempted to connect via pgAdmin3 and had success:

This is all well and good, but probably still a bit much to ask a user to do. So we built an installer. In this case, we used the Nullsoft Scriptable Install System (NSIS) to build our own installer and ensure that no registry entries were made. So once we had our instance/data prepped the way we wanted it, we zipped it all back up for inclusion in the installer. The NSIS script for the installer is at the end of this post.

For the installer to work, we also made use of the ZipDLL plug-in for NSIS. The installer essentially prompts the user to specify the install location then unzips the PostgreSQL binaries/data, builds the driver batch file and places shortcuts on the user’s desktop. It’s really that simple. We have a utility (also a batch file) that builds the driver batch file as the installer runs. When it’s done, the installer cleans up the zip file and the utility. The resulting driver batch file looks like this (where the user specified “C:Program Files (x86)PGStandalone11″ as the install location):

[sourcecode language=”powershell”]
REM This file was automatically generated
REM This script sets environment variables helpful for PostgreSQL
@SET PATH="C:Program Files (x86)PGStandalone11bin";%PATH%
@SET PGDATA=C:Program Files (x86)PGStandalone11data
@SET PGDATABASE=postgres
@SET PGUSER=postgres
@SET PGPORT=54325
@SET PGLOCALEDIR=C:Program Files (x86)PGStandalone11sharelocale
CALL "C:Program Files (x86)PGStandalone11binpostgres.exe"
[/sourcecode]

We also drop two shortcuts on the desktop:

The shortcut labeled “Start PostGIS PIM” executes the batch file above. The shortcut labeled “Manage PostGIS PIM” starts pgAdmin3. I am currently trying to get pgAdmin3 to read the connection information from its settings.ini file instead of looking to the registry. If the user adds the connection on their own, it will write information to the HKCU hive of the registry. This may end up being acceptable but I probably still won’t let it go until I solve this problem.

Once we get this completely finalized, I’ll make the script and the utilities available. Another reason that I love PostgreSQL is that things like this seem to end up being easier than you would think. That’s a sign of a well-built piece of software, in my opinion.

And here’s the installer’s NSIS script:

[sourcecode language=”text”]
!define CO_DIR "Zekiah Technologies"
!define NAME "Standalone PostgreSQL/PostGIS Install for Windows"
!define SHORTNAME "PGStandalone"
!define UNINSTALLER "uninstall.exe"

OutFile "setup_${SHORTNAME}.exe"
Name "${NAME}"
Caption "${NAME} Setup"
InstallDir "$PROGRAMFILES${SHORTNAME}"
CompletedText "Success."
XPStyle "On"
InstallColors /windows

Page directory
Page instfiles
ShowInstDetails show

Section "Install"
SetOutPath $INSTDIR

; Create uninstaller first, so user can clean up if we barf.
WriteUninstaller "$INSTDIR${UNINSTALLER}" ;this actually does nothing right now

; Extract all the files.
DetailPrint "Extracting files…"
File pg.zip
ZipDLL::extractall "$INSTDIRpg.zip" "$INSTDIR"

;Write batch file using INSTDIR to set correct paths
Call WriteBatchFile
DetailPrint "OK: Batch file written"

Call OutputToTemp
GetFullPathName /short $0 $INSTDIR
Delete "$INSTDIRpg.zip"

; Add start menu shortcuts.
DetailPrint "Adding shortcuts…"
SetShellVarContext all
CreateDirectory "$SMPROGRAMS${NAME}"
SetOutPath "$SMPROGRAMS${NAME}"
CreateShortCut "Uninstall.lnk" "$INSTDIR${UNINSTALLER}"
CreateShortCut "$DESKTOPStart PostGIS PIM.lnk" "$INSTDIRpg_standalone.bat" ""
CreateShortCut "$DESKTOPManage PostGIS PIM.lnk" "$INSTDIRbinpgAdmin3.exe" ""

; Success.
DetailPrint "All Done!"
SectionEnd

Function OutputToTemp
ExpandEnvStrings $5 "%TEMP%"
SetOutPath $5
FunctionEnd

Function un.OutputToTemp
ExpandEnvStrings $5 "%TEMP%"
SetOutPath $5
FunctionEnd

Function WriteBatchFile
; execute utility to create batch file to execute PostgreSQL

Call OutputToTemp
File "utilsmake_pg_env.bat"
ExecWait ‘"$5make_pg_env.bat" "$INSTDIR"’
FunctionEnd

UninstPage uninstConfirm
UninstPage instfiles
ShowUninstDetails hide

Section "Uninstall"
; Remove all start menu shortcuts.
DetailPrint "Removing shortcuts…"
SetShellVarContext all
Delete "$SMPROGRAMS${NAME}*"
RmDir "$SMPROGRAMS${NAME}"

DetailPrint "Removing files…"

Delete "$INSTDIR${UNINSTALLER}"
RmDir "$INSTDIR"
SectionEnd
[/sourcecode]