Created
April 2, 2019 14:40
-
-
Save BenRhouma/f81f972665080c6feb71e26fab9f9e2f to your computer and use it in GitHub Desktop.
call postgres from powershell
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
$path="path/to/pgsql/folder" | |
$ConnectionString = "Server=127.0.0.1;Port=5432;Database=monolith_farm;Uid=postgres;Pwd=root;" | |
[Reflection.Assembly]::LoadFrom("$path/Npgsql.dll") > $null | |
function Get-CommandObject { | |
param ( | |
[Parameter(Mandatory=$true)] | |
[string] $CommandText, | |
[hashtable] $Parameters, | |
[Npgsql.NpgsqlConnection] $Connection = $( | |
if ($myConnection -ne $null) { $myConnection } | |
else { throw New-Object InvalidOperationException }), | |
[Npgsql.NpgsqlTransaction] $Transaction = $( | |
if ($myTransaction -ne $null) { $myTransaction } | |
else { throw New-Object InvalidOperationException }) | |
) | |
$cmd = New-Object Npgsql.NpgsqlCommand ($CommandText, $Connection, $Transaction) | |
try { | |
if ($Parameters -ne $null -and $Parameters.Count -gt 0) { | |
$Parameters.GetEnumerator() | foreach { $key = $_.Key; $value = $_.Value | |
if ($value.GetType() -eq @().GetType() -and $value.Length -eq 2 -and $value[1] -is [System.Data.DbType]) { | |
Get-ParameterObject $key $value[0] $value[1] | |
} else { | |
Get-ParameterObject $key $value | |
} | |
} | foreach { $cmd.Parameters.Add($_) > $null } | |
} | |
} catch { | |
debug-Command $cmd $Parameters | |
throw $_.Exception | |
} | |
$cmd | |
} | |
function Invoke-Query { | |
param ( | |
[Parameter(Mandatory=$true)] | |
[string] $Query, | |
[hashtable] $Parameters, | |
[Npgsql.NpgsqlConnection] $Connection = $( | |
if ($myConnection -ne $null) { $myConnection } | |
else { throw New-Object InvalidOperationException }), | |
[Npgsql.NpgsqlTransaction] $Transaction = $( | |
if ($myTransaction -ne $null) { $myTransaction } | |
else { throw New-Object InvalidOperationException }), | |
[switch] $Void, | |
[switch] $Scalar, | |
[string] $ReturnId | |
) | |
$cmd = Get-CommandObject -Connection $Connection ` | |
-Transaction $Transaction ` | |
-CommandText $Query ` | |
-Parameters $Parameters | |
if ($Debug) { | |
write-debug "$(debug-Command $cmd $Parameters)" | |
} | |
try { | |
if ($Void.isPresent) { | |
$cmd.ExecuteNonQuery() > $null | |
if (![string]::IsNullOrEmpty($ReturnId)) { | |
Get-CurrVal $ReturnId $Connection $Transaction | |
} | |
} elseif ($Scalar.isPresent) { | |
$cmd.ExecuteScalar() | |
} else { | |
$dr = $cmd.ExecuteReader() | |
$out = @() | |
$fc = $dr.FieldCount | |
$fields = 0..($fc - 1) | foreach { $dr.GetName($_) } | |
while ($dr.Read()) { | |
$row = @{} | |
$fields | foreach { | |
$row[$_] = $dr[$_] | |
} | |
$out += $row | |
} | |
$out | |
} | |
} catch { | |
Write-Host "Could not invoke command:" | |
Write-Host (debug-Command $cmd $Parameters) | |
throw | |
} | |
} | |
$myConnection = new-object Npgsql.NpgsqlConnection $ConnectionString | |
$myConnection.Open() | |
$myTransaction =$myConnection.BeginTransaction() | |
$cnx = Invoke-Query " | |
select * | |
from virtual_machine | |
" @{ | |
} $myConnection $myTransaction -ErrorAction stop | |
$password = 'pwd' | |
$userid = 'domain/account' | |
$secstr = New-Object -TypeName System.Security.SecureString | |
$password.ToCharArray() | ForEach-Object {$secstr.AppendChar($_)} | |
$vcenterCred = new-object -typename System.Management.Automation.PSCredential -argumentlist $userid, $secstr | |
$vcenter = "vcenter001" | |
Connect-VIServer -Server $vcenter -Credential $vcenterCred > $null | |
$err = @() | |
Foreach ($vm in $cnx) | |
{ | |
Write-Host "Calling VM:" $vm['vm_name'] | |
$cmd = @' | |
curl x | |
'@ | |
$x = invoke-vmscript -VM $vm['vm_name'] -ScriptText $cmd -GuestUser vcenteruser -GuestPassword $password -ScriptType PowerShell -ErrorAction SilentlyContinue | |
if( ($x -eq $null) -or ($x.ExitCode -ne 0) ) | |
{ | |
Write-Host "cannot contact " $vm['vm_name'] | |
$err += $vm['vm_name'] | |
} | |
$x = $null | |
} | |
Write-Host "cannot contact these vms" | |
$myConnection.Close() | |
Disconnect-VIServer -Server $vcenter | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment