使用Wix ToolSet创建PostgreSQL数据库失败求助

Hey there, let's dig into this frustrating issue you're having. First off, a key thing to note: WiX's built-in sql:SqlDatabase and related SQL extensions are designed specifically for Microsoft SQL Server, not PostgreSQL. That's likely why you're hitting those vague -2147467259 errors—WiX's SQL logic doesn't understand PostgreSQL's connection protocols or commands.

Below are practical, tested solutions to get your PostgreSQL database created during installation:

Solution 1: Use Custom Actions with PostgreSQL's psql Command-Line Tool #

PostgreSQL comes with psql.exe, a powerful command-line tool that can execute SQL commands and scripts. We'll leverage this in WiX via custom actions, which is far more reliable for PostgreSQL than WiX's native SQL components.

Step-by-Step Implementation: #

1. Ensure psql is accessible:

   • Either make sure PostgreSQL's bin directory (e.g., C:\Program Files\PostgreSQL\9.6\bin) is in the system PATH, or include psql.exe, libpq.dll, and other required dependencies in your installer package (check PostgreSQL's redistribution terms first).

2. Create WiX Custom Actions:
   Add these elements to your Product.wsx to run psql commands. We'll split this into two actions: one to create the database, another to run your CreateTable.sql script.

   <!-- Custom action to create the database -->
   <CustomAction
       Id="CreatePostgresDb"
       BinaryKey="WixCA"
       DllEntry="QuietExec"
       Execute="deferred"
       Return="check"
       Impersonate="no" />
   <Property Id="CreatePostgresDbCmd" Value='&quot;[ProgramFilesFolder]\PostgreSQL\9.6\bin\psql.exe&quot; -U [SQLUSERNAME] -p [SQLSERVERPORT] -h [SQLSERVER] -c &quot;CREATE DATABASE [DATABASE_NAME];&quot;' />
   
   <!-- Custom action to run the create table script -->
   <CustomAction
       Id="RunCreateTableScript"
       BinaryKey="WixCA"
       DllEntry="QuietExec"
       Execute="deferred"
       Return="check"
       Impersonate="no" />
   <Property Id="RunCreateTableScriptCmd" Value='&quot;[ProgramFilesFolder]\PostgreSQL\9.6\bin\psql.exe&quot; -U [SQLUSERNAME] -p [SQLSERVERPORT] -h [SQLSERVER] -d [DATABASE_NAME] -f &quot;[InstallDir]\CreateTable.sql&quot;' />
   
   <!-- Schedule the custom actions after files are installed -->
   <InstallExecuteSequence>
       <Custom Action="CreatePostgresDb" After="InstallFiles">NOT Installed</Custom>
       <Custom Action="RunCreateTableScript" After="CreatePostgresDb">NOT Installed</Custom>
   </InstallExecuteSequence>
   
   <!-- Don't forget to copy your CreateTable.sql to the install directory -->
   <Component Id="SqlScriptComponent" Guid="YOUR-GUID-HERE" KeyPath="yes">
       <File Id="CreateTableSql" SourceFile=".\CreateTable.sql" />
   </Component>
   <Feature Id='SqlFeature' Title='SqlFeature' Level='1'>
       <ComponentRef Id='SqlScriptComponent' />
       <!-- Keep your other component refs here -->
   </Feature>
   

   • Adjust the psql.exe path to match your PostgreSQL version.
   • The Impersonate="no" ensures the action runs with elevated privileges, which may be needed for database creation.

3. Handle Passwords Securely:
   To avoid exposing the password in command lines, set the PGPASSWORD environment variable via a custom action before running psql:

   <CustomAction Id="SetPgPassword" Property="CreatePostgresDbCmd" Value="set PGPASSWORD=[SQLPASSWORD] &amp; [CreatePostgresDbCmd]" />
   <InstallExecuteSequence>
       <Custom Action="SetPgPassword" Before="CreatePostgresDb">NOT Installed</Custom>
   </InstallExecuteSequence>
   

Solution 2: Use PowerShell Scripts for More Flexibility #

If you prefer a more readable script-based approach, create a PowerShell script to handle database setup, then call it from WiX.

Example PowerShell Script (SetupPostgresDb.ps1): #

param(
    [string]$Username,
    [string]$Password,
    [string]$Server,
    [int]$Port,
    [string]$DbName,
    [string]$ScriptPath
)

# Set password environment variable to avoid interactive prompt
$env:PGPASSWORD = $Password

try {
    # Create the database
    Write-Host "Creating database $DbName..."
    & psql -U $Username -p $Port -h $Server -c "CREATE DATABASE $DbName;" -q
    if ($LASTEXITCODE -ne 0) { throw "Failed to create database" }

    # Execute the create table script
    Write-Host "Running table creation script..."
    & psql -U $Username -p $Port -h $Server -d $DbName -f $ScriptPath -q
    if ($LASTEXITCODE -ne 0) { throw "Failed to run table script" }

    Write-Host "PostgreSQL setup completed successfully!"
}
catch {
    Write-Error "Error during PostgreSQL setup: $_"
    exit 1
}

WiX Configuration to Call the Script: #

<CustomAction
    Id="RunPostgresSetupScript"
    BinaryKey="WixCA"
    DllEntry="QuietExec"
    Execute="deferred"
    Return="check"
    Impersonate="no" />
<Property Id="RunPostgresSetupScriptCmd" Value='powershell.exe -ExecutionPolicy Bypass -File &quot;[InstallDir]\SetupPostgresDb.ps1&quot; -Username [SQLUSERNAME] -Password [SQLPASSWORD] -Server [SQLSERVER] -Port [SQLSERVERPORT] -DbName [DATABASE_NAME] -ScriptPath &quot;[InstallDir]\CreateTable.sql&quot;' />

<InstallExecuteSequence>
    <Custom Action="RunPostgresSetupScript" After="InstallFiles">NOT Installed</Custom>
</InstallExecuteSequence>

<!-- Include the PowerShell script in your installer -->
<Component Id="SetupScriptComponent" Guid="YOUR-GUID-HERE" KeyPath="yes">
    <File Id="SetupPostgresScript" SourceFile=".\SetupPostgresDb.ps1" />
</Component>

Why Your Original WiX Configuration Failed #

Just to clarify: WiX's sql:SqlDatabase uses SQL Server-specific APIs (like OLE DB for SQL Server) under the hood. PostgreSQL uses a different protocol, so WiX can't communicate with it properly using those native components. Even adjusting permissions or connection strings won't fix this fundamental mismatch.

内容的提问来源于stack exchange，提问作者Filipe Magnus