Skip to content

PostgreSQL Backup and Restore

Overview

Osprey uses PostgreSQL running in a Docker container to store metadata, lineage information, data quality metrics, and configuration data. This guide covers backup and restore procedures for the containerized PostgreSQL database.

Important: PostgreSQL runs as a Docker container named postgres as part of the Osprey deployment. All backup and restore operations must account for this containerized environment.

Database Information

  • Database Engine: PostgreSQL 13+
  • Container: Runs as part of docker-compose stack
  • Data Storage: Persistent volumes mounted to the container
  • Default Database Name: tychodata

Backup Procedures

This method creates logical backups that are portable across PostgreSQL versions.

Windows

# Create backup directory
New-Item -ItemType Directory -Path "C:\Osprey\backups" -Force

# Create backup using pg_dump from container
docker-compose exec postgres pg_dump -U osprey -d osprey -f /tmp/osprey_backup_$(Get-Date -Format "yyyyMMdd_HHmmss").sql

# Copy backup file from container to host
docker cp postgres:/tmp/osprey_backup_$(Get-Date -Format "yyyyMMdd_HHmmss").sql C:\Osprey\backups\

Linux

# Create backup directory
sudo mkdir -p /opt/osprey/backups

# Create backup using pg_dump from container
docker-compose exec postgres pg_dump -U osprey -d osprey -f /tmp/osprey_backup_$(date +%Y%m%d_%H%M%S).sql

# Copy backup file from container to host
docker cp postgres:/tmp/osprey_backup_$(date +%Y%m%d_%H%M%S).sql /opt/osprey/backups/

Method 2: Compressed Backup

For larger databases, use compressed backup format:

Windows

# Create compressed backup
docker-compose exec postgres pg_dump -U osprey -d osprey -Fc -f /tmp/osprey_backup_$(Get-Date -Format "yyyyMMdd_HHmmss").dump

# Copy to host
docker cp postgres:/tmp/osprey_backup_$(Get-Date -Format "yyyyMMdd_HHmmss").dump C:\Osprey\backups\

Linux

# Create compressed backup
docker-compose exec postgres pg_dump -U osprey -d osprey -Fc -f /tmp/osprey_backup_$(date +%Y%m%d_%H%M%S).dump

# Copy to host
docker cp postgres:/tmp/osprey_backup_$(date +%Y%m%d_%H%M%S).dump /opt/osprey/backups/

Automated Backup Scripts

Windows PowerShell Script

# backup-osprey.ps1
param(
    [string]$BackupPath = "C:\Osprey\backups",
    [int]$RetentionDays = 30
)

# Create backup directory if it doesn't exist
New-Item -ItemType Directory -Path $BackupPath -Force

# Generate timestamp
$timestamp = Get-Date -Format "yyyyMMdd_HHmmss"
$backupFile = "osprey_backup_$timestamp.sql"

# Create backup
Write-Host "Creating backup: $backupFile"
docker-compose exec -T postgres pg_dump -U osprey -d osprey > "$BackupPath\$backupFile"

if ($LASTEXITCODE -eq 0) {
    Write-Host "Backup completed successfully: $BackupPath\$backupFile"

    # Cleanup old backups
    Get-ChildItem -Path $BackupPath -Name "osprey_backup_*.sql" | 
        Where-Object { $_.CreationTime -lt (Get-Date).AddDays(-$RetentionDays) } |
        Remove-Item

    Write-Host "Old backups cleaned up (retention: $RetentionDays days)"
} else {
    Write-Error "Backup failed!"
    exit 1
}

Linux Bash Script

#!/bin/bash
# backup-osprey.sh

BACKUP_PATH="/opt/osprey/backups"
RETENTION_DAYS=30

# Create backup directory if it doesn't exist
mkdir -p "$BACKUP_PATH"

# Generate timestamp
timestamp=$(date +%Y%m%d_%H%M%S)
backup_file="osprey_backup_$timestamp.sql"

# Create backup
echo "Creating backup: $backup_file"
docker-compose exec -T postgres pg_dump -U osprey -d osprey > "$BACKUP_PATH/$backup_file"

if [ $? -eq 0 ]; then
    echo "Backup completed successfully: $BACKUP_PATH/$backup_file"

    # Cleanup old backups
    find "$BACKUP_PATH" -name "osprey_backup_*.sql" -type f -mtime +$RETENTION_DAYS -delete

    echo "Old backups cleaned up (retention: $RETENTION_DAYS days)"
else
    echo "Backup failed!" >&2
    exit 1
fi

Restore Procedures

Method 1: Restore from SQL Backup

Windows

# Stop Osprey application (keep database running)
docker-compose stop app

# Restore database
docker-compose exec postgres psql -U osprey -d osprey -c "DROP SCHEMA IF EXISTS public CASCADE; CREATE SCHEMA public;"
docker cp C:\Osprey\backups\osprey_backup_20241002_143000.sql postgres:/tmp/restore.sql
docker-compose exec postgres psql -U osprey -d osprey -f /tmp/restore.sql

# Restart all services
docker-compose up -d

Linux

# Stop Osprey application (keep database running)
docker-compose stop app

# Restore database
docker-compose exec postgres psql -U osprey -d osprey -c "DROP SCHEMA IF EXISTS public CASCADE; CREATE SCHEMA public;"
docker cp /opt/osprey/backups/osprey_backup_20241002_143000.sql postgres:/tmp/restore.sql
docker-compose exec postgres psql -U osprey -d osprey -f /tmp/restore.sql

# Restart all services
docker-compose up -d

Method 2: Restore from Compressed Backup

Windows

# Stop Osprey application
docker-compose stop app

# Restore from compressed backup
docker cp C:\Osprey\backups\osprey_backup_20241002_143000.dump postgres:/tmp/restore.dump
docker-compose exec postgres pg_restore -U osprey -d osprey --clean --if-exists /tmp/restore.dump

# Restart all services
docker-compose up -d

Linux

# Stop Osprey application
docker-compose stop app

# Restore from compressed backup
docker cp /opt/osprey/backups/osprey_backup_20241002_143000.dump postgres:/tmp/restore.dump
docker-compose exec postgres pg_restore -U osprey -d osprey --clean --if-exists /tmp/restore.dump

# Restart all services
docker-compose up -d

Backup Scheduling

Windows Task Scheduler

  1. Open Task Scheduler
  2. Create Basic Task
  3. Set trigger (e.g., daily at 2:00 AM)
  4. Set action to run PowerShell script: Program: powershell.exe Arguments: -File "C:\Osprey\backup-osprey.ps1" Start in: C:\Osprey

Linux Cron Job

# Edit crontab
crontab -e

# Add daily backup at 2:00 AM
0 2 * * * /opt/osprey/backup-osprey.sh >> /var/log/osprey-backup.log 2>&1

Monitoring and Verification

Verify Backup Integrity

Windows

# Test backup file integrity
docker-compose exec postgres pg_dump -U osprey -d osprey --schema-only | Out-Null
if ($LASTEXITCODE -eq 0) {
    Write-Host "Database schema is accessible"
} else {
    Write-Error "Database connection failed"
}

Linux

# Test backup file integrity
if docker-compose exec postgres pg_dump -U osprey -d osprey --schema-only > /dev/null 2>&1; then
    echo "Database schema is accessible"
else
    echo "Database connection failed" >&2
fi

Backup Size Monitoring

# Check backup file sizes
ls -lh /opt/osprey/backups/osprey_backup_*.sql

# Monitor database size
docker-compose exec postgres psql -U osprey -d osprey -c "SELECT pg_size_pretty(pg_database_size('osprey'));"