如何通过Terraform在Azure虚拟机启动postgres.exe并修改Postgres账户

一、用Azure VM自定义脚本扩展替代Invoke-AzVMRunCommand启动postgres.exe #

你可以使用Azure的CustomScriptExtension（适用于Windows虚拟机）在部署完成后自动执行脚本启动PostgreSQL，这是替代Run Command的常用方案：

1. 编写PowerShell启动脚本（示例：start-postgres.ps1）
   注意：镜像中预装的PostgreSQL大概率已注册为Windows服务，优先用启动服务的方式更稳定，路径和服务名需根据镜像内实际配置调整：

   # 检查PostgreSQL服务是否存在并启动
   $serviceName = "postgresql-x64-15" # 替换为镜像中的实际服务名
   if (Get-Service -Name $serviceName -ErrorAction SilentlyContinue) {
       Start-Service $serviceName
       # 可选：设置开机自启
       Set-Service $serviceName -StartupType Automatic
   }
   # 如果需要直接启动postgres.exe（非服务形式）
   # $postgresExe = "C:\Program Files\PostgreSQL\15\bin\postgres.exe"
   # if (Test-Path $postgresExe) { Start-Process -FilePath $postgresExe -NoNewWindow }
   

2. 在Terraform中配置扩展资源
   你可以将脚本存储在Azure Blob（通过SAS链接），或者直接内嵌脚本内容：

   resource "azurerm_virtual_machine_extension" "start_postgres" {
     name                 = "StartPostgres"
     virtual_machine_id   = azurerm_windows_virtual_machine.your_vm.id
     publisher            = "Microsoft.Compute"
     type                 = "CustomScriptExtension"
     type_handler_version = "1.10"
   
     # 内嵌脚本方式（无需外部存储）
     settings = jsonencode({
       "commandToExecute" = "powershell -ExecutionPolicy Unrestricted -Command \"`$serviceName = 'postgresql-x64-15'; if (Get-Service -Name `$serviceName -ErrorAction SilentlyContinue) { Start-Service `$serviceName; Set-Service `$serviceName -StartupType Automatic }\""
     })
   
     # 如果用外部脚本文件，替换为下面的配置：
     # settings = jsonencode({
     #   "commandToExecute" = "powershell -ExecutionPolicy Unrestricted -File start-postgres.ps1"
     # })
     # protected_settings = jsonencode({
     #   "fileUris" = ["https://your-storage.blob.core.windows.net/scripts/start-postgres.ps1?<SAS_TOKEN>"]
     # })
   
     depends_on = [azurerm_windows_virtual_machine.your_vm]
   }
   

二、通过Terraform修改Postgres账户 #

修改Postgres账户（重置密码、创建新用户等）同样可以通过VM扩展或Run Command执行SQL命令，步骤如下：

1. 编写配置账户的PowerShell脚本（示例：configure-postgres-user.ps1）

   $psqlPath = "C:\Program Files\PostgreSQL\15\bin\psql.exe" # 替换为实际路径
   $superUser = "postgres"
   $newSuperPassword = "YourStrongPassword123!" # 替换为目标密码
   
   # 重置超级用户密码
   & $psqlPath -U $superUser -c "ALTER USER $superUser WITH PASSWORD '$newSuperPassword';"
   
   # 可选：创建新用户并授权
   # $newUser = "app_user"
   # $newUserPassword = "AppPass456!"
   # & $psqlPath -U $superUser -c "CREATE USER $newUser WITH PASSWORD '$newUserPassword';"
   # & $psqlPath -U $superUser -c "GRANT ALL PRIVILEGES ON DATABASE your_db TO $newUser;"
   

2. 在Terraform中配置扩展执行该脚本

   resource "azurerm_virtual_machine_extension" "configure_postgres_user" {
     name                 = "ConfigurePostgresUser"
     virtual_machine_id   = azurerm_windows_virtual_machine.your_vm.id
     publisher            = "Microsoft.Compute"
     type                 = "CustomScriptExtension"
     type_handler_version = "1.10"
   
     settings = jsonencode({
       "commandToExecute" = "powershell -ExecutionPolicy Unrestricted -File configure-postgres-user.ps1"
     })
   
     protected_settings = jsonencode({
       "fileUris" = ["https://your-storage.blob.core.windows.net/scripts/configure-postgres-user.ps1?<SAS_TOKEN>"]
     })
   
     # 确保在PostgreSQL启动后再执行
     depends_on = [azurerm_virtual_machine_extension.start_postgres]
   }
   

如果不想依赖外部存储，同样可以将脚本内容内嵌到commandToExecute中，注意转义双引号和特殊字符即可。

内容的提问来源于stack exchange，提问作者Nguyen Tung Son