Create a installer with inno setup and postgreSQL in Windows X64

Question

I've created an installer for my application that also installs postgreSQL.

The first question:

What is the best way to configure my database, I mean, is it better to use the postgres default user account? Or should I create a new user. Also is it better to use default public schema or should I create a new schema? As one can see, I'm seeking the best practices to use databases. Also, what kind of care do I need to take with possible already installed versions of postgreSQL from any other application?

The second question:

How to create a user using the installer? I'm trying to create a new user different from the default user but how can I set a password for this user? I would like to do this without prompting the user in a command shell.

If it's helpful, I will happily post any of my code!

Answer 1

Let's go for some points that you've asked in your question.

What is the best way to configure my database, I mean, is it better to use the postgres default user account?

That doesn't matter at all as long as you have defined a strong password for the user to install it you will be good.

is it better to use default public schema or should I create a new schema?

That will depends on your software. If you want to have everything well defined and organized on the use of the database go on, create a schema to your system. If your system will be the only one using PostgreSQL why botter? Read this answer on this matter from DBA forum PostgreSQL and default Schemas

Also, what kind of care I might to take with possible already installed versions of postgreSQL of any other application?

That's a trick one and the very first problem you will have. I find out the hard way that you can't install PostgreSQL database if you already have one previously installed. Not even if you uninstall it (in this case you have to delete all associated registries from windows registry of PostgreSQL).

My advice here is that you put a message on your installer that your system needs a fresh install of PostgreSQL on the target machine and it can't have a previously installed one on it otherwise the install will fail. I know that sucks, but I couldn't find anything to do here. One other thing that I did was to add a note on the installer messages to the user that if He still want to install the system on that machine He would have to contact the "support" to do it manually.

How to create a user using the installer?

From here I will add some commands to you install PostgreSQL in unattended mode.

I'm trying to create a new user different from default user but how to set a password to this user?

The command you will need to install PostgreSQL with a user different from the default one will be:

postgresqlInstallFile[withversion].exe --mode unattended --superaccount super_user_name --superpassword yourStrongPassowordHere 

It would be great here if you could ask for the password on your installer program and use a variable within above command.

That alone will install the database with a user defined by you. Here is the documentation about all parameters for PostgreSQL Unattended Install

The second problem is regarding the database of you system. The best course of action I could find was to make a Dump file from the database that I need to install (this database would be empty, with just the structure), added this dump file to the installer so I could import it to the fresh installation of the database.

The problem with this approach is you can't create a database or restore it to PostgreSQL without entering the password for the super account. What I did was

• Add messages to the user that He should pay attention to the installer messages and steps so he can't get it wrong
  
• After installing PostgreSql database comes the step to create the database so I use the command:

[path_for_postgresq]/createdb.exe -U user_you_created -W -E UTF8 -O user_you_created "NameOfYourDatabase"

• Before I run the above command I showed a message to the user that he will see a prompt screen asking for a password, so He have to enter it and hit Enter. You can't add it as a parameter (PostgreSQL says it is for security measures)

• After the database creation it is time to import the dump that I've added on the installer so I used:

[path_for_postgresq]/pg_restore -U user_you_created -c -d NameOfYourDatabase DumpFileOfYourDatabase.backup

Once again, before this command I showed a message to the user asking to enter the password for the database restore command.

Here I will add the code I made for the NSIS installer. Just the PostgreSQL part of it. There are a lot more on my installer you may need something more.

;--------------------------------
;Include Modern UI
!include "MUI.nsh"
;Include Process checker
!include nsProcess.nsh
;Include ZipDLL
!include zipdll.nsh
; include for some of the windows messages defines
!define ALL_USERS
!include winmessages.nsh

;--------------------------------
;General Configuration
    Name "Name of your Installer"
    OutFile "fileNameOfYourInstaller.exe"

    ShowInstDetails "show"

    InstallDir $PROFILE\FolderWhereToInstallYourSystem

    ; Request application privileges for Windows Vista
    RequestExecutionLevel admin

    ; HKLM (all users) vs HKCU (current user) defines
    !define env_hklm 'HKLM "SYSTEM\CurrentControlSet\Control\Session Manager\Environment"'
    !define env_hkcu 'HKCU "Environment"'

;--------------------------------
;Interface Settings
    !define MUI_ABORTWARNING

;--------------------------------
;Pages
    !insertmacro MUI_PAGE_DIRECTORY
    !insertmacro MUI_PAGE_INSTFILES

;--------------------------------
;Languages
    !insertmacro MUI_LANGUAGE "English"

Function .onInit
    SetOutPath $TEMP
    # the plugins dir is automatically deleted when the installer exits
    InitPluginsDir
    File /oname=re.bmp re.bmp  #here is an image for a splash screen of the installer

    splash::show 2000 re

    Pop $0  ; $0 has '1' if the user closed the splash screen early,
            ; '0' if everything closed normally, and '-1' if some error occurred.
FunctionEnd

Function verifyInstallationDIR
    IfFileExists $INSTDIR PathGood
        MessageBox MB_OK "The chosen directory is not valid for installation.$\r$\nPlease start again and inform a valid path."
        Quit ;if $INSTDIR wasn't created the installer will close
    PathGood:
FunctionEnd

Section
    SetOutPath $TEMP
    CreateDirectory $INSTDIR

    #Message to the user to PAY ATTENTIOM
    MessageBox MB_OK "Please carefully read all messages through installation steps!"

    Call verifyInstallationDIR
    #Files needed to the installer
    File "resources\DatabaseDumpFile.backup"
    File "resources\postgresql-9.3.9-3-windows-x64.exe"

    #Call postgresql installation in an unattended mode
    ExecWait 'postgresqlInstallFile[withversion].exe --mode unattended --superaccount super_user_name --superpassword yourStrongPassowordHere'

    Postgresql_Running_Check: 
        ; is app.exe process running? result is stored in $R0 
        ${nsProcess::FindProcess} "postgres.exe" $R0
        ${If} $R0 > 0
            MessageBox MB_RETRYCANCEL|MB_ICONEXCLAMATION "PostgreSQL process is not running yet. Wait 30 seconds and click Retry or Cancel to cancel installation." /SD IDCANCEL IDRETRY Postgresql_Running_Check
            Quit
        ${EndIf}

    ;Create the database
    MessageBox MB_OK "The System DataBase will be created. A console window will appear asking for a password. Please when it happens type:$\r$\n$\r$\nyourStrongPassowordHere"
    ExecWait '$PROGRAMFILES64\PostgreSQL\9.3\bin\createdb.exe -U user_you_created -W -E UTF8 -O user_you_created "NameOfYourDatabase"'

    MessageBox MB_OK "The System Installer will import the configuration data to the database. It will ask again for the password. Please when it happens type:$\r$\n$\r$\nyourStrongPassowordHere"
    ExecWait '$PROGRAMFILES64\PostgreSQL\9.3\bin\pg_restore -U user_you_created -c -d NameOfYourDatabase DatabaseDumpFile.backup'

SectionEnd

Hope it helps.