Backup

This guide explains how to backup PCH-SIG data.

Backup strategy

Elements to backup

Element	Frequency	Retention
PostgreSQL database	Daily	30 days
Uploaded files	Daily	30 days
Configuration	Weekly	90 days
Logs	Weekly	14 days

Backup architecture

┌─────────────────┐     ┌─────────────────┐     ┌─────────────────┐
│   PostgreSQL    │────▶│   pg_dump       │────▶│  Local storage  │
│                 │     │                 │     │  C:\pch-sig\    │
│                 │     │                 │     │  backups\       │
└─────────────────┘     └─────────────────┘     └────────┬────────┘
                                                         │
                                                         ▼
                                                ┌─────────────────┐
                                                │ Remote storage  │
                                                │ (NAS, Cloud)    │
                                                └─────────────────┘

---

Database backup

Manual backup

# Full backup
docker exec pch_postgres pg_dump -U pch_admin -d pch_sig > backup_$(date +%Y%m%d_%H%M%S).sql

# Compressed backup
docker exec pch_postgres pg_dump -U pch_admin -d pch_sig | gzip > backup_$(date +%Y%m%d).sql.gz

Automatic backup script

File: C:\pch-sig\scripts\backup-db.ps1

# PostgreSQL backup script
$Date = Get-Date -Format "yyyyMMdd_HHmmss"
$BackupDir = "C:\pch-sig\backups\database"
$BackupFile = "$BackupDir\pch_sig_$Date.sql"

# Create directory if needed
New-Item -ItemType Directory -Force -Path $BackupDir | Out-Null

# Execute pg_dump
docker-machine ssh default "docker exec pch_postgres pg_dump -U pch_admin -d pch_sig" > $BackupFile

# Compress
Compress-Archive -Path $BackupFile -DestinationPath "$BackupFile.zip"
Remove-Item $BackupFile

# Clean old backups (> 30 days)
Get-ChildItem $BackupDir -Filter "*.zip" |
    Where-Object { $_.LastWriteTime -lt (Get-Date).AddDays(-30) } |
    Remove-Item

Write-Host "Backup complete: $BackupFile.zip"

pg_dump options

Option	Description
-F c	Custom format (selective restore)
-F p	Plain text SQL format (default)
--no-owner	Ignore owners
--schema-only	Structure only
--data-only	Data only

Custom format backup

# Custom format backup (recommended)
docker exec pch_postgres pg_dump -U pch_admin -d pch_sig -F c > backup.dump

# Selective restore possible
pg_restore -d pch_sig -t menages backup.dump

---

File backup

Uploaded documents

# Backup uploads
$Date = Get-Date -Format "yyyyMMdd"
$Source = "C:\pch-sig\backend\public\uploads"
$Dest = "C:\pch-sig\backups\uploads\uploads_$Date.zip"

Compress-Archive -Path $Source -DestinationPath $Dest

Configuration

# Backup configuration
$Date = Get-Date -Format "yyyyMMdd"
$Files = @(
    "C:\pch-sig\deploy\docker-compose.yml",
    "C:\pch-sig\backend\.env",
    "C:\pch-sig\backend\config\jwt\*"
)

Compress-Archive -Path $Files -DestinationPath "C:\pch-sig\backups\config\config_$Date.zip"

---

Full backup script

File: C:\pch-sig\scripts\backup-full.ps1

# PCH-SIG full backup
param(
    [string]$BackupRoot = "C:\pch-sig\backups"
)

$Date = Get-Date -Format "yyyyMMdd_HHmmss"
$BackupDir = "$BackupRoot\full_$Date"

Write-Host "=== PCH-SIG Backup ===" -ForegroundColor Cyan
Write-Host "Destination: $BackupDir"

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

# 1. Database
Write-Host "1. PostgreSQL backup..." -ForegroundColor Yellow
docker-machine ssh default "docker exec pch_postgres pg_dump -U pch_admin -d pch_sig" > "$BackupDir\database.sql"

# 2. Redis (optional)
Write-Host "2. Redis backup..." -ForegroundColor Yellow
docker-machine ssh default "docker exec pch_redis redis-cli -a redis_secure_2025 BGSAVE"
Start-Sleep -Seconds 5
docker-machine ssh default "docker cp pch_redis:/data/dump.rdb /c/pch-sig/backups/redis_$Date.rdb"

# 3. Uploaded files
Write-Host "3. Uploads backup..." -ForegroundColor Yellow
Copy-Item -Path "C:\pch-sig\backend\public\uploads" -Destination "$BackupDir\uploads" -Recurse

# 4. Configuration
Write-Host "4. Configuration backup..." -ForegroundColor Yellow
Copy-Item -Path "C:\pch-sig\deploy\docker-compose.yml" -Destination "$BackupDir\"
Copy-Item -Path "C:\pch-sig\backend\.env" -Destination "$BackupDir\backend.env"
Copy-Item -Path "C:\pch-sig\backend\config\jwt" -Destination "$BackupDir\jwt" -Recurse

# 5. Compression
Write-Host "5. Compression..." -ForegroundColor Yellow
Compress-Archive -Path $BackupDir -DestinationPath "$BackupDir.zip"
Remove-Item -Path $BackupDir -Recurse -Force

# 6. Cleanup old backups
Write-Host "6. Cleanup..." -ForegroundColor Yellow
Get-ChildItem "$BackupRoot\full_*.zip" |
    Where-Object { $_.LastWriteTime -lt (Get-Date).AddDays(-30) } |
    Remove-Item

Write-Host "=== Backup complete ===" -ForegroundColor Green
Write-Host "File: $BackupDir.zip"

---

Backup scheduling

Windows scheduled task

# Create daily backup task
$Action = New-ScheduledTaskAction -Execute "PowerShell.exe" -Argument "-ExecutionPolicy Bypass -File C:\pch-sig\scripts\backup-full.ps1"

$Trigger = New-ScheduledTaskTrigger -Daily -At "02:00"

$Principal = New-ScheduledTaskPrincipal -UserId "SYSTEM" -LogonType ServiceAccount -RunLevel Highest

$Settings = New-ScheduledTaskSettingsSet -AllowStartIfOnBatteries -DontStopIfGoingOnBatteries -StartWhenAvailable

Register-ScheduledTask -TaskName "PCH-SIG Backup" -Action $Action -Trigger $Trigger -Principal $Principal -Settings $Settings -Description "PCH-SIG daily backup"

Verify the task

Get-ScheduledTask -TaskName "PCH-SIG Backup"

---

External storage

Copy to NAS

# Copy to network share
$Date = Get-Date -Format "yyyyMMdd"
$Source = "C:\pch-sig\backups\full_$Date.zip"
$Dest = "\\nas\backups\pch-sig\"

Copy-Item -Path $Source -Destination $Dest

Sync with rclone

# Install rclone and configure
rclone sync C:\pch-sig\backups remote:pch-sig-backups --progress

---

Backup verification

Test integrity

# Verify ZIP file
Test-Path "C:\pch-sig\backups\full_20240115.zip"

# List contents
Expand-Archive -Path "C:\pch-sig\backups\full_20240115.zip" -DestinationPath "C:\temp\test-backup" -Force
Get-ChildItem "C:\temp\test-backup" -Recurse

Test restore

# Restore to test database
docker exec -i pch_postgres psql -U pch_admin -c "CREATE DATABASE pch_sig_test;"
docker exec -i pch_postgres psql -U pch_admin -d pch_sig_test < backup.sql

# Verify data
docker exec pch_postgres psql -U pch_admin -d pch_sig_test -c "SELECT count(*) FROM menages;"

---

Backup monitoring

Verification script

# check-backups.ps1
$BackupDir = "C:\pch-sig\backups"
$MaxAge = 2  # days

$LatestBackup = Get-ChildItem "$BackupDir\full_*.zip" |
    Sort-Object LastWriteTime -Descending |
    Select-Object -First 1

if (-not $LatestBackup) {
    Write-Host "ERROR: No backup found!" -ForegroundColor Red
    exit 1
}

$Age = (Get-Date) - $LatestBackup.LastWriteTime

if ($Age.TotalDays -gt $MaxAge) {
    Write-Host "WARNING: Last backup $($Age.Days) days ago" -ForegroundColor Yellow
    exit 1
}

Write-Host "OK: Latest backup: $($LatestBackup.Name)" -ForegroundColor Green
Write-Host "    Size: $([math]::Round($LatestBackup.Length/1MB, 2)) MB"
Write-Host "    Date: $($LatestBackup.LastWriteTime)"

---

Best practices

3-2-1 Rule

• 3 copies of data
• 2 different media (local disk + NAS)
• 1 offsite copy (cloud or remote location)

Security

• Encrypt sensitive backups
• Protect access to backup files
• Do not store passwords in plain text in scripts

Documentation

• Document the restore procedure
• Regularly test restores
• Keep a backup log

---

Next steps

• Restore
• Monitoring