Restore

This guide explains how to restore PCH-SIG data from a backup.

Prerequisites

Before restoring:

• Access to backup files
• Administrator access to server
• Docker running
• Backup of current state (just in case)

Warning

Restoration overwrites existing data. Make sure you have a backup of the current state before proceeding.

---

Database restoration

Full restoration

# Stop application (avoid writes)
docker stop pch_backend

# Restore database
docker exec -i pch_postgres psql -U pch_admin -d pch_sig < backup.sql

# Restart application
docker start pch_backend

Restore from compressed archive

# Decompress
gunzip backup_20240115.sql.gz

# Restore
docker exec -i pch_postgres psql -U pch_admin -d pch_sig < backup_20240115.sql

Custom format restore (pg_dump -Fc)

# Full restore
docker exec -i pch_postgres pg_restore -U pch_admin -d pch_sig -c < backup.dump

# Restore specific table
docker exec -i pch_postgres pg_restore -U pch_admin -d pch_sig -t menages < backup.dump

---

Selective restoration

Restore a single table

# Extract a table from dump
pg_restore -t menages backup.dump > menages_only.sql

# Restore the table
docker exec -i pch_postgres psql -U pch_admin -d pch_sig < menages_only.sql

Restore multiple tables

pg_restore -t menages -t beneficiaires -t paiements backup.dump > tables.sql
docker exec -i pch_postgres psql -U pch_admin -d pch_sig < tables.sql

Restore without constraints

# Disable constraints
docker exec pch_postgres psql -U pch_admin -d pch_sig -c "SET session_replication_role = 'replica';"

# Restore
docker exec -i pch_postgres psql -U pch_admin -d pch_sig < backup.sql

# Re-enable constraints
docker exec pch_postgres psql -U pch_admin -d pch_sig -c "SET session_replication_role = 'origin';"

---

Restore to a new database

Create a restore database

# Create the new database
docker exec pch_postgres psql -U pch_admin -c "CREATE DATABASE pch_sig_restore;"

# Restore to the new database
docker exec -i pch_postgres psql -U pch_admin -d pch_sig_restore < backup.sql

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

Replace the main database

# Rename current database
docker exec pch_postgres psql -U pch_admin -c "ALTER DATABASE pch_sig RENAME TO pch_sig_old;"

# Rename restored database
docker exec pch_postgres psql -U pch_admin -c "ALTER DATABASE pch_sig_restore RENAME TO pch_sig;"

# Delete old database (after verification)
docker exec pch_postgres psql -U pch_admin -c "DROP DATABASE pch_sig_old;"

---

File restoration

Uploaded documents

# Decompress archive
Expand-Archive -Path "C:\pch-sig\backups\uploads_20240115.zip" -DestinationPath "C:\temp\uploads-restore"

# Copy to uploads directory
Copy-Item -Path "C:\temp\uploads-restore\*" -Destination "C:\pch-sig\backend\public\uploads" -Recurse -Force

Configuration

# Restore docker-compose.yml
Copy-Item -Path "C:\pch-sig\backups\config\docker-compose.yml" -Destination "C:\pch-sig\deploy\"

# Restore backend .env
Copy-Item -Path "C:\pch-sig\backups\config\backend.env" -Destination "C:\pch-sig\backend\.env"

# Restore JWT keys
Copy-Item -Path "C:\pch-sig\backups\config\jwt\*" -Destination "C:\pch-sig\backend\config\jwt\" -Recurse

---

Full restoration

Restoration script

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

# PCH-SIG full restore
param(
    [Parameter(Mandatory=$true)]
    [string]$BackupFile
)

Write-Host "=== PCH-SIG Restore ===" -ForegroundColor Cyan
Write-Host "Archive: $BackupFile"

# Verify archive
if (-not (Test-Path $BackupFile)) {
    Write-Host "ERROR: Archive not found!" -ForegroundColor Red
    exit 1
}

# Create temporary directory
$TempDir = "C:\temp\pch-restore-$(Get-Date -Format 'yyyyMMdd_HHmmss')"
New-Item -ItemType Directory -Force -Path $TempDir | Out-Null

# Decompress
Write-Host "1. Extracting archive..." -ForegroundColor Yellow
Expand-Archive -Path $BackupFile -DestinationPath $TempDir

# Save current state
Write-Host "2. Backing up current state..." -ForegroundColor Yellow
docker-machine ssh default "docker exec pch_postgres pg_dump -U pch_admin -d pch_sig" > "$TempDir\current_backup.sql"

# Stop backend
Write-Host "3. Stopping backend..." -ForegroundColor Yellow
docker-machine ssh default "docker stop pch_backend"

# Restore database
Write-Host "4. Restoring PostgreSQL..." -ForegroundColor Yellow
Get-Content "$TempDir\database.sql" | docker-machine ssh default "docker exec -i pch_postgres psql -U pch_admin -d pch_sig"

# Restore uploads
Write-Host "5. Restoring uploads..." -ForegroundColor Yellow
if (Test-Path "$TempDir\uploads") {
    Copy-Item -Path "$TempDir\uploads\*" -Destination "C:\pch-sig\backend\public\uploads" -Recurse -Force
}

# Restore configuration
Write-Host "6. Restoring configuration..." -ForegroundColor Yellow
if (Test-Path "$TempDir\jwt") {
    Copy-Item -Path "$TempDir\jwt\*" -Destination "C:\pch-sig\backend\config\jwt\" -Recurse -Force
}

# Restart backend
Write-Host "7. Restarting backend..." -ForegroundColor Yellow
docker-machine ssh default "docker start pch_backend"

# Clear cache
Write-Host "8. Clearing cache..." -ForegroundColor Yellow
docker-machine ssh default "docker exec pch_backend php bin/console cache:clear"

# Cleanup
Write-Host "9. Cleanup..." -ForegroundColor Yellow
Remove-Item -Path $TempDir -Recurse -Force

Write-Host "=== Restore complete ===" -ForegroundColor Green

---

Redis restoration

# Stop Redis
docker stop pch_redis

# Copy dump.rdb file
docker cp dump.rdb pch_redis:/data/dump.rdb

# Restart Redis
docker start pch_redis

# Verify
docker exec pch_redis redis-cli -a redis_secure_2025 DBSIZE

---

Post-restore verification

Verify database

# Number of households
docker exec pch_postgres psql -U pch_admin -d pch_sig -c "SELECT count(*) FROM menages;"

# Number of beneficiaries
docker exec pch_postgres psql -U pch_admin -d pch_sig -c "SELECT count(*) FROM beneficiaires;"

# Number of payments
docker exec pch_postgres psql -U pch_admin -d pch_sig -c "SELECT count(*) FROM paiements;"

Verify integrity

# Check foreign key constraints
docker exec pch_postgres psql -U pch_admin -d pch_sig -c "
SELECT count(*) FROM menages m
LEFT JOIN regions r ON m.region_id = r.id
WHERE m.region_id IS NOT NULL AND r.id IS NULL;"

Test application

# API test
curl http://localhost:8000/api/health

# Authentication test
curl -X POST http://localhost:8000/api/login_check \
  -H "Content-Type: application/json" \
  -d '{"email":"admin@pch-sig.sn","password":"Admin123!"}'

---

Troubleshooting

"Database does not exist" error

# Create database
docker exec pch_postgres psql -U pch_admin -c "CREATE DATABASE pch_sig;"

# Then restore
docker exec -i pch_postgres psql -U pch_admin -d pch_sig < backup.sql

Permission errors

# Fix file permissions
docker exec pch_backend chown -R www-data:www-data /app/var
docker exec pch_backend chown -R www-data:www-data /app/public/uploads

PostgreSQL version error

If backup comes from a different PostgreSQL version:

# Use pg_restore with --no-owner --no-acl
docker exec -i pch_postgres pg_restore --no-owner --no-acl -U pch_admin -d pch_sig < backup.dump

Data conflicts

# Clear database before restore
docker exec pch_postgres psql -U pch_admin -d pch_sig -c "
DROP SCHEMA public CASCADE;
CREATE SCHEMA public;
GRANT ALL ON SCHEMA public TO pch_admin;"

# Then restore
docker exec -i pch_postgres psql -U pch_admin -d pch_sig < backup.sql

---

Restore point

Create a restore point before modification

# Before a risky operation
docker exec pch_postgres pg_dump -U pch_admin -d pch_sig -F c > /tmp/before_operation.dump

# If problem, restore
docker exec -i pch_postgres pg_restore -U pch_admin -d pch_sig -c < /tmp/before_operation.dump

---

Next steps

• Backup
• Troubleshooting