Backup / pg_dump in ADS Postgres plugin still not working due to pg_dump version mismatch (at least under Windows as a client-only workstation)

Question

Backup / pg_dump in ADS Postgres plugin still not working due to pg_dump version mismatch (at least under Windows as a client-only workstation)

FrySlim opened this issue a year ago · 2 comments

ADS (v1.47.0) with Postgres extension v0.6.0 has still problems finding the right pg_dump (at least under Windows in case, there is no fully installed Postgres server, but only PgAdmin (which also comes with pg_dump.exe etc., but does not require any server, because it is usually on a dedicated client)).

I am refering to following previously reported issued:

#115
#223

and the previously intended fix:

microsoft/pgtoolsservice#273

F.e. my pg_dump.exe locations are here:

C:>dir pg_dump.exe /B /S 2>NUL
C:\Program Files\pgAdmin 4\runtime\pg_dump.exe
C:\Program Files\PostgreSQL\12\bin\pg_dump.exe
C:\Program Files\PostgreSQL\bin_11_8\pg_dump.exe
C:\Program Files (x86)\pgAdmin 4\v4\runtime\pg_dump.exe
C:\Program Files (x86)\SQL Maestro Group\PostgreSQL Maestro\pg_dump.exe
C:\Program Files (x86)\SQL Maestro Group\PostgreSQL Maestro\x64\pg_dump.exe
C:\Users_.azuredatastudio\extensions\microsoft.azuredatastudio-postgresql-0.6.0\out\ossdbtoolsservice\Windows\v1.10.0\pgsqltoolsservice\pg_exes\win\pg_dump.exe
C:\Users_.azuredatastudio\extensions\microsoft.azuredatastudio-postgresql-0.6.0\out\ossdbtoolsservice\Windows\v1.10.0\pgsqltoolsservice\pg_exes\win\10\pg_dump.exe

Also, my Windows PATH variable includes "C:\Program Files\PostgreSQL\12\bin" and command "where pg_dump.exe" natively finds "C:\Program Files\PostgreSQL\12\bin\pg_dump.exe".

But, a simple "Backup" trial within the Azure Data Studio Postgres plugin of a connected pg database results in error:

Backup: Exe folder c:\Users__.azuredatastudio\extensions\microsoft.azuredatastudio-postgresql-0.6.0\out\ossdbtoolsservice\Windows\v1.10.0\pgsqltoolsservice\pg_exes\win does not contain pg_dump for version 12.0.15

+++ EDIT:
Postgres extension 0.5.0 with "[..]\ossdbtoolsservice\Windows\v1.9.0\pgsqltoolsservice\pg_exes\win" seems to work fine. A colleague of mine has some subdirs "10","11","12" within. I have only installed the extension 0.6.0 and see only the subdir "10" which obviously results in this version mismatch issue. The workaround from #115 (comment) is not fully adoptable any more. I will try to modify and experiment with this workaround.
EDIT +++

Regards
FrySlim

Answer 1 · 2023-12-12T13:44:37.000Z

I have now built a more generic powershell script as a WORKAROUND to avoid the problem, until it is fixed.

It searches pg_dump.exe through the PATH variable locations (Plan A) and all occurences on the system drive (Plan B).
The content of the first directory where pg_dump.exe version is greater or equal the complained version (see error message in first post) is then copied into the subdir, where it is missing. Note: A symbolic link like in the older workaround #115 (comment) does not work any more for the current Postgres extension version 0.6.0. Therefore, the binaries are copied in my workaround script . Newer pg_dump version also work, but the subdir must match the main version of the complained pg_dump version:

"# Generic workaround for Azure Data Studio Postgres Extension 'microsoft.azuredatastudio-postgresql' on Windows, " | Write-Host -f DarkGreen
"# when 'Backup' complains 'Exe folder XXX does not contain pg_dump for version YYY' (~ pg_dump version mismatch)" | Write-Host -f DarkGreen

"`r`n# HINT: execute once for each user of the ADS plugin Postgres`r`n." | Write-Host -f DarkGreen

#region Settings
$PgDump_MainVersionComplained = "12.15"
#endregion

#region functions and includes
function Normalize-VersionString([string]$VersionString,[int]$PartWidth=2,[char]$Separator='.') {
    if ($VersionString -notlike "*$Separator*") { $VersionString += $Separator }
    return ((($VersionString.Split($Separator)) | Foreach { $_.PadLeft($PartWidth,'0') }) -join $Separator)
}

function Get-PgDumpVersion([string]$PgDumpExe) {
    if (Test-Path $PgDumpExe) {return (. "$PgDumpExe" --version).Replace("pg_dump (PostgreSQL) ","") }
}

function Get-FirstPgDumpGreaterOrEqual([string[]]$PgDumpFiles,[string]$MinVersion="12") {
    return @( @($PgDumpFiles) | where { (Normalize-VersionString(Get-PgDumpVersion -PgDumpExe "$_")) -ge (Normalize-VersionString($MinVersion)) })[0] 
}
#endregion

#region main work
"`r`nremember current and set new workdir location..."
Push-Location "$((gci "$((gci "~\.azuredatastudio\extensions\microsoft.azuredatastudio-postgresql-*").FullName)\out\ossdbtoolsservice\Windows\v*").FullName)\pgsqltoolsservice\pg_exes\win\"

"`r`n** choose the first pg_dump.exe full path in list greater or equal complained version '$PgDump_MainVersionComplained' **"
"- Plan A: prefer default found through PATH variable"
" -Plan B: if any found on system drive"

"`r`nPlan A: find all pg_dump.exe locations found through system environment variable PATH (full exe path):"
$pgdumpexedefault = where.exe pg_dump.exe 2>$null
$pgdumpexedefault | Write-Host -f Cyan

if ($pgdumpexedefault) 
{ 
    "`r`nSelect the first pg_dump version found on above PATH list greater or equal the complained version '$PgDump_MainVersionComplained'..."
    $pgdumpselected = Get-FirstPgDumpGreaterOrEqual -PgDumpFiles $pgdumpexedefault -MinVersion $PgDump_MainVersionComplained
    $pgdumpselected | Write-Host -f Green
}

if (-Not $pgdumpselected)  {
    "No recent pg_dump.exe found through PATH variable." | Write-Host -f Yellow
    "`r`nPlan B Fallback: find all pg_dump.exe locations on system drive (full exe path)`r`nthis may take a while ....:"
    $pgdumpexeall = Get-ChildItem -Path "$env:SystemDrive\" -Filter "pg_dump.exe" -Recurse -Name 2>$null | Foreach { "$env:SystemDrive\$_" }
    $pgdumpexeall  | Write-Host -f Cyan
    "`r`nSelect first pg_dump version found on above system drive list greater or equal the complained version '$PgDump_MainVersionComplained'..."
    $pgdumpselected = Get-FirstPgDumpGreaterOrEqual -PgDumpFiles $pgdumpexeall -MinVersion $PgDump_MainVersionComplained
    $pgdumpselected | Write-Host -f Green
}

if ($pgdumpselected -ne $null) { 
    "Version: $(Get-PgDumpVersion($pgdumpselected))" | Write-Host -f green
    $SubDir = [string][int]$PgDump_MainVersionComplained
    
    If (Test-Path "$SubDir") { "`r`nRename existing subdir '$SubDir'..."; Rename-Item "$SubDir" "$SubDir.unused" 2>$null }
    "`r`nGet folder of selected pg_dump..."
    $selectedPgPath = Split-Path $pgdumpselected -Parent
    
    "`r`nCreate new subdir '$LinkDir' for User '$env:USERNAME'..."
    New-Item -Path "$SubDir" -ItemType Directory 
    "`r`nCopy from the found pg_dump source dir ecvery file into subdir '$SubDir'..."
    Copy-Item -Path "$selectedPgPath\*" -Destination "$SubDir\" -PassThru | Out-String -Stream | Write-Host -f Green
}

"`r`n# If something went wrong, just go to '$(Get-Location)' and delete the sub folder '$LinkDir' if it exists." | Write-Host -f DarkGreen
"# Then retry script, install a current 'PgAdmin' client (which comes with pg_dump.exe etc.)" | Write-Host -f DarkGreen
"# or play with the complained version in the settings region of this script." | Write-Host -f DarkGreen

"`r`nrestore previous workdir location..."
Pop-Location
#endregion 

"`r`nEND OF SCRIPT."