Upgrade PostgreSQL

#Requires -Version 5.1
<#
.SYNOPSIS
Migrates BloodHound Community Edition PostgreSQL from 16 to 18.
.DESCRIPTION
Walks through dumping the PG 16 database, backing up the Docker volume,
upgrading to PG 18, and restoring the data. Requires Docker with the
Compose V2 plugin.
#>

$ErrorActionPreference = "Stop"

function Write-Step  { param([string]$Msg) Write-Host "`n=== $Msg ===" -ForegroundColor Cyan }
function Write-Ok    { param([string]$Msg) Write-Host $Msg -ForegroundColor Green }
function Write-Warn  { param([string]$Msg) Write-Host $Msg -ForegroundColor Yellow }

# ── 1. Gather inputs ────────────────────────────────────────────────────────
Write-Step "BloodHound PostgreSQL 16 -> 18 Migration"

$defaultDir = Join-Path $HOME ".config" "bloodhound"
$composeDir = Read-Host "Enter the directory containing docker-compose.yml (default: $defaultDir)"
if ([string]::IsNullOrWhiteSpace($composeDir)) { $composeDir = $defaultDir }
$composeDir = [System.IO.Path]::GetFullPath($composeDir.Replace("~", $HOME))
$composeFile = Join-Path $composeDir "docker-compose.yml"

if (-not (Test-Path $composeFile)) {
Write-Error "docker-compose.yml not found at $composeFile"; exit 1
}

$defaultDump = Join-Path $composeDir "pg16_backup.dump"
$dumpPath = Read-Host "Enter path for the database dump file (default: $defaultDump)"
if ([string]::IsNullOrWhiteSpace($dumpPath)) { $dumpPath = $defaultDump }
$dumpPath = [System.IO.Path]::GetFullPath($dumpPath.Replace("~", $HOME))

# ── 2. Read credentials (.env then defaults) ────────────────────────────────
$pgUser = "bloodhound"; $pgDb = "bloodhound"

$envFile = Join-Path $composeDir ".env"
if (Test-Path $envFile) {
Write-Host "Reading overrides from $envFile ..."
Get-Content $envFile | ForEach-Object {
    if ($_ -match '^\s*POSTGRES_USER\s*=\s*(.+)$')     { $pgUser = $Matches[1].Trim() }
    if ($_ -match '^\s*POSTGRES_DB\s*=\s*(.+)$')        { $pgDb   = $Matches[1].Trim() }
}
}
Write-Host "Credentials: user=$pgUser  db=$pgDb"

# ── 3. Resolve project and volume names via Docker Compose ───────────────────
$dcBase = @("-f", $composeFile, "--project-directory", $composeDir)

$projectName = (docker compose @dcBase config --format json | ConvertFrom-Json).name
if ([string]::IsNullOrWhiteSpace($projectName)) {
Write-Error "Could not determine Compose project name."; exit 1
}

$declaredVols = @(docker compose @dcBase config --volumes)
$pgVolDecl = $declaredVols | Where-Object { $_ -match 'postgres-data' } | Select-Object -First 1
if (-not $pgVolDecl) {
Write-Error "No 'postgres-data' volume declared in compose config."; exit 1
}
$volumeName = "${projectName}_${pgVolDecl}"

$volExists = docker volume ls --format "{{.Name}}" | Where-Object { $_ -eq $volumeName }
if (-not $volExists) {
Write-Error "Docker volume '$volumeName' not found. Is BloodHound installed?"
exit 1
}
Write-Host "Project: $projectName   Volume: $volumeName"

# ── 4. Confirm ──────────────────────────────────────────────────────────────
Write-Warn "`nThis script will:"
Write-Host "  1. Stop the BloodHound app (keep PG 16 running)"
Write-Host "  2. Dump the PG 16 database to: $dumpPath"
Write-Host "  3. Stop all containers"
Write-Host "  4. Create a backup copy of the PostgreSQL data volume"
Write-Host "  5. Update docker-compose.yml to use PostgreSQL 18"
Write-Host "  6. Start PG 18 and restore the database"
Write-Host "  7. Start the full BloodHound stack"

$confirm = Read-Host "`nProceed? (y/N)"
if ($confirm -notin @('y','Y')) { Write-Host "Cancelled."; exit 0 }

# ── Helper: run docker compose with project context ──────────────────────────
function Invoke-DC {
$dcArgs = @("-f", $composeFile, "--project-directory", $composeDir) + $args
& docker compose @dcArgs
if ($LASTEXITCODE -ne 0) { throw "docker compose failed (exit $LASTEXITCODE)" }
}

# ── 5. Stop app, keep PG 16 alive ───────────────────────────────────────────
Write-Step "Stopping BloodHound application container"
Invoke-DC stop bloodhound
Write-Ok "BloodHound app stopped."

Write-Step "Ensuring PostgreSQL 16 is running"
Invoke-DC start app-db
Start-Sleep -Seconds 5

# ── 6. Dump database ────────────────────────────────────────────────────────
Write-Step "Dumping PostgreSQL 16 database"
Invoke-DC exec app-db pg_dump -U $pgUser -d $pgDb -Fc -Z 9 -f /tmp/pg16_backup.dump
Invoke-DC cp app-db:/tmp/pg16_backup.dump $dumpPath

$dumpSize = (Get-Item $dumpPath).Length
Write-Ok "Database dumped ($dumpSize bytes) -> $dumpPath"

# ── 7. Stop everything ──────────────────────────────────────────────────────
Write-Step "Stopping all containers"
Invoke-DC down
Write-Ok "All containers stopped."

# ── 8. Copy the volume ──────────────────────────────────────────────────────
Write-Step "Backing up PostgreSQL data volume"
$backupVol = "${volumeName}-pg16-backup"
docker volume create $backupVol | Out-Null
docker run --rm -v "${volumeName}:/source:ro" -v "${backupVol}:/backup" `
alpine sh -c "cp -a /source/. /backup/"
Write-Ok "Volume copied to: $backupVol"

# ── 9. Update docker-compose.yml ────────────────────────────────────────────
Write-Step "Updating docker-compose.yml to PostgreSQL 18"
$bakFile = Join-Path $composeDir "docker-compose.yml.pg16.bak"
Copy-Item $composeFile $bakFile
Write-Host "Compose file backed up to: $bakFile"

$content = Get-Content $composeFile -Raw
$updated = $content -replace 'image:\s*docker\.io/library/postgres:16', 'image: docker.io/library/postgres:18'
if ($updated -eq $content) { Write-Error "Could not find postgres:16 image reference in compose file"; exit 1 }
# PG 18+ expects the mount at /var/lib/postgresql (not /var/lib/postgresql/data).
# It manages version-specific subdirectories under that path automatically.
$beforeMount = $updated
$updated = $updated -replace 'postgres-data:/var/lib/postgresql/data', 'postgres-data:/var/lib/postgresql'
if ($updated -eq $beforeMount) { Write-Error "Could not find postgres-data:/var/lib/postgresql/data volume mount in compose file"; exit 1 }
[System.IO.File]::WriteAllText($composeFile, $updated)
Write-Ok "docker-compose.yml updated (image + volume mount path)."

# ── 10. Remove old volume, start PG 18 ──────────────────────────────────────
Write-Step "Removing old PostgreSQL data volume"
docker volume rm $volumeName | Out-Null
Write-Ok "Old volume removed (backup preserved at $backupVol)."

Write-Step "Starting PostgreSQL 18"
Invoke-DC up -d app-db

# Resolve the actual container name for app-db from Compose
$appDbContainer = (docker compose @dcBase ps --format "{{.Name}}" app-db).Trim()
if ([string]::IsNullOrWhiteSpace($appDbContainer)) {
Write-Error "Could not determine container name for app-db service."; exit 1
}

Write-Host "Waiting for PostgreSQL 18 to become healthy ($appDbContainer)..."
for ($i = 0; $i -lt 30; $i++) {
Start-Sleep -Seconds 2
$health = docker inspect --format "{{.State.Health.Status}}" $appDbContainer 2>$null
if ($health -eq "healthy") { break }
}
if ($health -ne "healthy") { Write-Error "Container '$appDbContainer' is not healthy (status: $health). Aborting migration."; exit 1 }

# ── 11. Restore database ────────────────────────────────────────────────────
Write-Step "Restoring database into PostgreSQL 18"
Invoke-DC cp $dumpPath app-db:/tmp/pg16_backup.dump
Invoke-DC exec app-db pg_restore -U $pgUser -d $pgDb --clean --if-exists /tmp/pg16_backup.dump
Write-Ok "Database restored."

# ── 12. Start full stack ─────────────────────────────────────────────────────
Write-Step "Starting full BloodHound stack"
Invoke-DC up -d
Write-Ok "BloodHound stack is starting."

# ── Done ─────────────────────────────────────────────────────────────────────
Write-Step "Migration Complete"
Write-Ok "PostgreSQL upgraded from 16 to 18."
Write-Host "  Database dump : $dumpPath"
Write-Host "  Volume backup : $backupVol"
Write-Host "  Compose backup: $bakFile"
Write-Warn "`nOnce verified, you can clean up with:"
Write-Host "  docker volume rm $backupVol"
Write-Host "  Remove-Item '$bakFile'"
Write-Host "  Remove-Item '$dumpPath'"

docker volume rm <backup-volume-name>
Remove-Item 'C:\path\to\docker-compose.yml.pg16.bak'
Remove-Item 'C:\path\to\pg16_backup.dump'