db-backup.ps1

#!/usr/bin/env pwsh
<#
.SYNOPSIS
    Chateando - PostgreSQL backup helper (Windows / PowerShell).

.DESCRIPTION
    Creates a compressed pg_dump of the chateando database from the running
    `postgres` service in docker compose and stores it in a host-side directory.
    Old backups beyond -RetainDays are pruned.

.PARAMETER BackupDir
    Output directory (default: ./backups).

.PARAMETER RetainDays
    Days to keep backups (default: 14).

.PARAMETER ComposeFile
    Docker compose file (default: docker-compose.yml).

.PARAMETER DbService
    Compose service running Postgres (default: postgres).

.PARAMETER DbName
    Database name (default: chateando).

.PARAMETER DbUser
    Postgres role (default: chateando).

.EXAMPLE
    ./ops/db-backup.ps1
    ./ops/db-backup.ps1 -BackupDir D:\backups\chateando -RetainDays 30
#>

[CmdletBinding()]
param(
    [string]$BackupDir = './backups',
    [int]$RetainDays = 14,
    [string]$ComposeFile = 'docker-compose.yml',
    [string]$DbService = 'postgres',
    [string]$DbName = 'chateando',
    [string]$DbUser = 'chateando'
)

$ErrorActionPreference = 'Stop'

if (-not (Test-Path -LiteralPath $BackupDir)) {
    New-Item -ItemType Directory -Force -Path $BackupDir | Out-Null
}

$timestamp = (Get-Date).ToUniversalTime().ToString("yyyyMMddTHHmmss") + 'Z'
$target = Join-Path $BackupDir ("chateando_${timestamp}.dump")

Write-Host "[backup] dumping $DbName via service '$DbService' -> $target"

# Use docker compose exec -T with redirected stdout to avoid TTY issues on Windows.
$dumpArgs = @(
    'compose','-f',$ComposeFile,'exec','-T',$DbService,
    'pg_dump','--format=custom','--no-owner','--no-privileges',
    "--username=$DbUser",$DbName
)

# Stream pg_dump output directly into the target file.
& docker @dumpArgs | Set-Content -LiteralPath $target -AsByteStream

$item = Get-Item -LiteralPath $target
if ($item.Length -lt 1024) {
    Remove-Item -LiteralPath $target -Force
    throw "Dump file is suspiciously small ($($item.Length) bytes); aborted."
}

Write-Host "[backup] wrote $($item.Length) bytes."

Write-Host "[backup] pruning files older than $RetainDays day(s) from $BackupDir."
$cutoff = (Get-Date).AddDays(-1 * $RetainDays)
Get-ChildItem -Path $BackupDir -Filter 'chateando_*.dump' -File |
    Where-Object { $_.LastWriteTime -lt $cutoff } |
    ForEach-Object {
        Write-Host "  - removing $($_.FullName)"
        Remove-Item -LiteralPath $_.FullName -Force
    }

Write-Host "[backup] done."