PostgreSQL backup script generator for Windows

# Run in PowerShell. Then open the file and replace YOUR_PASSWORD with the real password.
New-Item -ItemType Directory -Force 'C:\ProgramData\pg-backup' | Out-Null
Set-Content -Path 'C:\ProgramData\pg-backup\pgpass.conf' -Encoding Ascii -Value 'localhost:5432:shop:postgres:YOUR_PASSWORD'

# Only SYSTEM, Administrators and you can read this folder (it holds a password).
icacls 'C:\ProgramData\pg-backup' /inheritance:r /grant:r '*S-1-5-18:(OI)(CI)F' '*S-1-5-32-544:(OI)(CI)F' "${env:USERNAME}:(OI)(CI)F"

# Backup-shop.ps1: back up PostgreSQL database shop with pg_dump,
# keep 14 days of backups, and log every run.
# Generated by https://postgresbackup.com/tools/backup-script on 2026-10-28.

$Database  = 'shop'
$PgHost    = 'localhost'
$Port      = 5432
$User      = 'postgres'
$BackupDir = 'D:\PostgresBackups'
$KeepDays  = 14

# Password and SSL come from the environment, never the command line.
$env:PGPASSFILE = 'C:\ProgramData\pg-backup\pgpass.conf'
$env:PGSSLMODE  = 'prefer'

$LogFile = Join-Path $BackupDir 'backup-log.txt'
function Write-Log([string]$Message) {
    $line = '{0:yyyy-MM-dd HH:mm:ss}  {1}' -f (Get-Date), $Message
    Add-Content -Path $LogFile -Value $line
    Write-Output $line
}

New-Item -ItemType Directory -Force -Path $BackupDir | Out-Null

# The newest pg_dump under Program Files\PostgreSQL, else the one on PATH.
$PgDump = Get-ChildItem "$env:ProgramFiles\PostgreSQL\*\bin\pg_dump.exe" -ErrorAction SilentlyContinue |
    Sort-Object { try { [version]$_.VersionInfo.ProductVersion } catch { [version]'0.0' } } -Descending |
    Select-Object -First 1 -ExpandProperty FullName
if (-not $PgDump) { $PgDump = (Get-Command pg_dump.exe -ErrorAction SilentlyContinue).Source }
if (-not $PgDump) { Write-Log 'FAILED: pg_dump.exe not found'; exit 2 }

$File = Join-Path $BackupDir ('{0}-{1:yyyyMMdd-HHmmss}.dump' -f $Database, (Get-Date))
Write-Log "Backing up $Database from ${PgHost}:$Port with $PgDump"
$output = & $PgDump -h $PgHost -p $Port -U $User -w -F c -f $File $Database 2>&1
$exitCode = $LASTEXITCODE
$output | ForEach-Object { Write-Log "  $_" }

if ($exitCode -ne 0) {
    Remove-Item $File -ErrorAction SilentlyContinue
    Write-Log "FAILED: pg_dump exited with code $exitCode"
    exit 1
}
Write-Log ('OK: {0} ({1:N1} MB)' -f $File, ((Get-Item $File).Length / 1MB))

# Delete this database's backups older than $KeepDays days (only files this script named).
$pattern = '^' + [regex]::Escape($Database) + '-\d{8}-\d{6}\.dump$'
Get-ChildItem $BackupDir -File |
    Where-Object { $_.Name -match $pattern -and $_.LastWriteTime -lt (Get-Date).AddDays(-$KeepDays) } |
    ForEach-Object { Remove-Item $_.FullName; Write-Log "Deleted old backup $($_.Name)" }
exit 0

# Run in PowerShell as Administrator.
$action    = New-ScheduledTaskAction -Execute 'powershell.exe' `
    -Argument '-NoProfile -ExecutionPolicy Bypass -File "C:\ProgramData\pg-backup\Backup-shop.ps1"'
$trigger   = New-ScheduledTaskTrigger -Daily -At '02:00'
$principal = New-ScheduledTaskPrincipal -UserId 'SYSTEM' -LogonType ServiceAccount -RunLevel Highest
$settings  = New-ScheduledTaskSettingsSet -StartWhenAvailable -ExecutionTimeLimit (New-TimeSpan -Hours 6)
Register-ScheduledTask -TaskName 'PostgreSQL backup (shop)' -Action $action -Trigger $trigger `
    -Principal $principal -Settings $settings -Force

# Test it now, then check the result (0 = success) and the log.
Start-ScheduledTask -TaskName 'PostgreSQL backup (shop)'
Start-Sleep -Seconds 20
Get-ScheduledTaskInfo -TaskName 'PostgreSQL backup (shop)' | Select-Object LastRunTime, LastTaskResult
Get-Content 'D:\PostgresBackups\backup-log.txt' -Tail 10

# Restore a backup into a new database, to check it (or to recover from it).
$env:PGSSLMODE = 'prefer'
$bin = Get-ChildItem "$env:ProgramFiles\PostgreSQL\*\bin\pg_restore.exe" |
    Sort-Object { try { [version]$_.VersionInfo.ProductVersion } catch { [version]'0.0' } } |
    Select-Object -Last 1 -ExpandProperty DirectoryName
$backup = Get-ChildItem 'D:\PostgresBackups' -Filter 'shop-*.dump' | Sort-Object Name | Select-Object -Last 1
& "$bin\createdb.exe" -h 'localhost' -p 5432 -U 'postgres' 'shop_restore_test'
& "$bin\pg_restore.exe" -h 'localhost' -p 5432 -U 'postgres' -d 'shop_restore_test' --no-owner --no-privileges $backup.FullName