]po[ Windows NSIS Installer Including PostgreSQL

This document describes some of the tricks used in the ]po[ V4.0 installer in order to include PostgreSQL 8.4 as part of the installation.

Directory Structure and Included Software

The ]po[ installer consists of the following three main software packages:

• CygWin:
  A compatibility layer for running the GNU toolstack (Bash, Gcc, ...) on Windows
• PostgreSQL 8.4:
  An open-source database and
• AOLserver:
  A Web- and application server that executes our project management Web-application
• JRE:
  Java Runtime Environment
• ]project-open[ application
  TCL source code to be executed by AOLserver and
• ]po[ Service Monitor:
  An auxillary Java application
  

The ]po[ installer contains the following directory structure:

• C:/project-open/ - The default installation directory.
• bin - CygWin executables
  
• installer - Installer source code, ]po[ service monitor and auxillary installer files
  
• jre - Java Runtime Environment
  
• pgsql - PostgreSQL unpacked from postgresql-8.4.15-1-windows-binaries.zip
  
• servers - Source code for the ]po[ application
• usr/local/aolserver451 - AOLserver
  
• other CygWin directories - dev, etc, home, lib, tmp, usr and var
  

Installation Action

During installation, the installer copies the directory tree into the target machine and performs a number of changes in the target Windows system:

• Create a new user account called "postgres" with suitable privileges for running the PostgreSQL database,
  
• register the "postgresql-8.4" service for running the PostgreSQL database,
• register the "aolserver-projop" service for running the ]po[ application using AOLserver,
• install the MS Visual C redistributable,
  
• modify the system PATH environment variable in order to include the CygWin and PostgreSQL binaries,
• create a "]project-open[" program group with a number of shortcuts,
• set a number of environment variables and
• setup an uninstaller for the previous actions.
  

NSIS - Installer Construction Kit

The installer uses NSIS   (Nullsoft Scriptable Install System) for creating an installer.

NSIS is working perfectly for us and includes a large number of user written plugins for a wide range of needs.

Installing PostgreSQL

In ]po[ V4.0 we have decided to include the PostgreSQL database as part of the ]po[ installer, instead of calling the PosgreSQL installer as a kind of sub-installer. The reason for this extra step is that the PostgreSQL installer didn't completely uninstall the PosgreSQL files and didn't remove the "postgres" user account that it creates. These leftovers used to create a lot of user complaints in ]po[ V3.5 and earlier.

Instead of using the PostgreSQL 8.4  ".exe" installer, we now use the ".zip" binary Windows distribution as part of the ]po[ installer. We copied the content of the ZIP file into C:\project-open\pgsql\ and created the following NSIS installer file that creates a "postgres" user account and sets up PostgreSQL as a Windows service:

# --------------------------------------------------------------
# Install PostgreSQL
# --------------------------------------------------------------

# set the name of the installer
outfile "C:\project-open\installer\install_postgres.exe"
Name "Install PostgreSQL"

!include Registry.nsh
!include LogicLib.nsh
!include MultiUser.nsh
!include Sections.nsh
!include MUI2.nsh

!define TARGET c:\project-open

Function .onInit
    StrCpy $INSTDIR "c:\project-open"
FunctionEnd

section
    UserMgr::CreateAccountEx "postgres" "AcpfP0st" "PostgreSQL" "PostgreSQL Database User" "Database user created by ]po[ installer" "UF_PASSWD_NOTREQD|UF_DONT_EXPIRE_PASSWD"
    pop $R0
    DetailPrint "After creating account: result=$R0"

    UserMgr::AddPrivilege "postgres" "SeBatchLogonRight"
    pop $R0
    DetailPrint "SeBatchLogonRight: result=$R0"
    
    UserMgr::AddPrivilege "postgres" "SeServiceLogonRight"
    pop $R0
    DetailPrint "SeServiceLogonRight: result=$R0"
 
    nsExec::ExecToLog '"$INSTDIR\pgsql\bin\initdb.exe" --username=postgres --locale=C --encoding=UTF8 -D "$INSTDIR\pgsql\data"'
    pop $R0
    DetailPrint "After initializing database: result=$R0"

    nsExec::ExecToLog 'sc create postgresql-8.4 binpath= "c:\project-open\pgsql\bin\pg_ctl.exe runservice -N postgresql-8.4 -D c:/project-open/pgsql/data -w" DisplayName= "PostgreSQL 8.4" start= "demand" type= own obj= ".\postgres" password= "AcpfP0st" '
    pop $R0
    DetailPrint "After registering the service: result=$R0"
sectionEnd

• Debugging:
  PG on Windows uses the Windows "Event Log" for its error messages. It doesn't write any error.log.
• Account Permissions:
  The "postgres" account should NOT be member of the group Administrators. Privileged users are blocked from running PostgreSQL and PG will report an error #1061.
• Service User Syntax:
  When creating a service, you need to use for the user account the string ".\postgres" (including the dot and the backslash).
• Option -w in the service command line:
  The PG service needs to include the "-w" option in order to tell the Windows Service Management that it's still working when starting up.
• Folder permissions:
  C:\project-open\pgsql\ (the PG folder) needs to be owned by user postgres aparently.
  

Installer Details

For more details please see the file "project_open_installer.nsi" in the "/installer/" folder of ]po[ Windows installer. The "/installer/" contains the complete source code for building the installer.