bluegreencity-ki

PostGIS Backup & Restore Guide

Overview

This guide describes how to create and restore backups of the BlueGreenCity KI PostGIS database using the automated backup script.

Backup Script

The scripts/backup_postgis.ps1 script creates automated backups in two formats:

Usage

Basic Backup

Create both backup formats (default):

.\scripts\backup_postgis.ps1

Create Only Custom Format

.\scripts\backup_postgis.ps1 -Format custom

Create Only SQL Format

.\scripts\backup_postgis.ps1 -Format sql

With Automatic Cleanup

Keep only the last 5 backups of each format:

.\scripts\backup_postgis.ps1 -Format both -KeepLast 5 -CleanupOld

Create both formats, keep last 3, and cleanup old files:

.\scripts\backup_postgis.ps1 -Format both -KeepLast 3 -CleanupOld

Parameters

Parameter Type Default Description
-Format String both Backup format: both, custom, or sql
-KeepLast Integer 5 Number of backups to keep per format
-CleanupOld Switch false Enable automatic cleanup of old backups

Backup Formats Comparison

Feature Custom (.backup) Plain SQL (.sql)
File Size ~45 MB (compressed) ~157 MB (uncompressed)
Restore Speed Fast Slower
Human Readable No (binary) Yes (text)
Selective Restore Yes (tables/schemas) No
Compatibility PostgreSQL only Universal
Best For Regular backups, quick restore Archival, version control

Restore Instructions

Restore from Custom Format (.backup)

Full database restore (run from the repo root; replace <yyyy-MM> and the timestamp):

docker exec -i bgcki-postgis pg_restore -U bgcki_admin -d bgcki -c < data\backups\postgis\yyyy-MM\bgcki_backup_TIMESTAMP.backup

Restore specific schema:

docker exec -i bgcki-postgis pg_restore -U bgcki_admin -d bgcki -n bgcki < data\backups\postgis\yyyy-MM\bgcki_backup_TIMESTAMP.backup

Restore specific table:

docker exec -i bgcki-postgis pg_restore -U bgcki_admin -d bgcki -t bgcki.trees_inventory < data\backups\postgis\yyyy-MM\bgcki_backup_TIMESTAMP.backup

Restore from Plain SQL (.sql)

Full database restore:

docker exec -i bgcki-postgis psql -U bgcki_admin -d bgcki < data\backups\postgis\yyyy-MM\bgcki_backup_TIMESTAMP.sql

Note: SQL format does not support selective restore. For selective restore, use the Custom format.

Backup Location

All backups are stored under data/backups/postgis/<yyyy-MM>/ (the script writes relative to the repo root, grouped by year-month):

bluegreencity-ki/
└── data/backups/postgis/
    └── 2025-10/
        ├── bgcki_backup_20251014_010159.backup  (Custom format)
        ├── bgcki_backup_20251014_010159.sql     (SQL format)
        └── ...

Retention Policy

Implementation Example

Daily backup (automated via Task Scheduler):

.\scripts\backup_postgis.ps1 -Format both -KeepLast 7 -CleanupOld

Weekly backup (manual or scheduled):

# Create backup with descriptive name
$timestamp = Get-Date -Format "yyyyMMdd"
.\scripts\backup_postgis.ps1 -Format custom
Rename-Item "data\backups\postgis\<yyyy-MM>\bgcki_backup_*.backup" -NewName "bgcki_weekly_$timestamp.backup"

Backup Verification

After creating a backup, verify its integrity:

# Check backup file size
Get-Item data\backups\postgis\yyyy-MM\bgcki_backup_TIMESTAMP.backup | Select-Object Name, Length, LastWriteTime

# Verify Custom format backup
docker exec bgcki-postgis pg_restore -U bgcki_admin -l backups/bgcki_backup_TIMESTAMP.backup

Troubleshooting

Container Not Running

Error: Container 'bgcki-postgis' is not running!

Solution:

docker compose up -d

Backup File Empty

Error: Custom backup file is empty!

Possible causes:

Solution:

# Check Docker logs
docker logs bgcki-postgis

# Check disk space
Get-PSDrive C

# Restart container
docker compose restart postgis

Restore Fails

Error during restore: ERROR: database "bgcki" already exists

Solution: Drop and recreate database before restore:

# Connect to postgres database
docker exec -it bgcki-postgis psql -U bgcki_admin -d postgres

# Drop and recreate database
DROP DATABASE IF EXISTS bgcki;
CREATE DATABASE bgcki;
\q

# Now restore
docker exec -i bgcki-postgis pg_restore -U bgcki_admin -d bgcki < data\backups\postgis\yyyy-MM\bgcki_backup_TIMESTAMP.backup

Automated Backups (Task Scheduler)

Windows Task Scheduler Setup

  1. Open Task Scheduler (taskschd.msc)
  2. Create Basic Task:
    • Name: BlueGreenCity PostGIS Backup
    • Trigger: Daily at 2:00 AM
    • Action: Start a program
    • Program: powershell.exe
    • Arguments: -ExecutionPolicy Bypass -File "C:\Path\To\bluegreencity-ki\scripts\backup_postgis.ps1" -Format both -KeepLast 7 -CleanupOld
    • Start in: C:\Path\To\bluegreencity-ki
  3. Advanced Settings:
    • ☑ Run whether user is logged on or not
    • ☑ Run with highest privileges
    • ☑ Wake the computer to run this task

Verify Task

Test the scheduled task manually:

# Run task
schtasks /run /tn "BlueGreenCity PostGIS Backup"

# Check task status
schtasks /query /tn "BlueGreenCity PostGIS Backup"

Best Practices

  1. Regular Backups: Schedule daily backups via Task Scheduler
  2. Multiple Formats: Keep both Custom and SQL formats for flexibility
  3. Off-site Storage: Copy critical backups to external storage (e.g., cloud, NAS)
  4. Test Restores: Regularly test restore procedures to ensure backups are valid
  5. Monitor Disk Space: Ensure sufficient space for backups (each backup ~45-160 MB)
  6. Version Control: For schema changes, commit SQL backups to Git (compressed)

Security Notes

Database Size Reference

Current database size (as of October 2025):

Change Log

Date Version Changes
2025-10-14 1.0 Initial backup script and documentation