PostgreSQL Database Upgrade Guide for Docker Environments

A comprehensive guide for safely upgrading PostgreSQL databases in containerized environments

Overview

This guide covers the essential principles and steps for upgrading PostgreSQL databases in Docker environments. The methodology applies to various scenarios including:

Authentik PostgreSQL upgrades
Custom application databases
Any PostgreSQL-based service in Docker
Kubernetes PostgreSQL deployments (with adaptations)

Core Principles

1. Always Backup First

SQL Dump: Create a complete database dump using pg_dump
Volume Backup: Backup the actual data directory/files
Multiple Backup Types: Use both logical (SQL) and physical (files) backups
Verify Backups: Always verify backup integrity before proceeding

2. Stop Services Gracefully

Stop all dependent services before database operations
Ensure no active connections to the database
Use docker compose down or equivalent

3. Upgrade in Isolation

Use network_mode: none during upgrade to prevent connections
Upgrade database container first, then restore data
Remove network isolation after successful restore

4. Test and Verify

Verify database connectivity after upgrade
Test application functionality
Monitor for any compatibility issues

Step-by-Step Process

Step 1: Pre-Upgrade Assessment

# Check current PostgreSQL version
docker compose exec postgresql psql -U your_user -d your_db -c "SELECT version();"

# Check database size
docker compose exec postgresql psql -U your_user -d your_db -c "SELECT pg_size_pretty(pg_database_size('your_db'));"

# List all databases
docker compose exec postgresql psql -U your_user -d your_db -c "\l"

Step 2: Create Database Dump

# Basic dump (includes data and schema)
docker compose exec postgresql pg_dump -U your_user -d your_db > backup_$(date +%Y%m%d_%H%M%S).sql

# Comprehensive dump (includes DROP/CREATE statements)
docker compose exec postgresql pg_dump -U your_user -d your_db -cC > backup_complete_$(date +%Y%m%d_%H%M%S).sql

# Custom format dump (compressed, faster restore)
docker compose exec postgresql pg_dump -U your_user -d your_db -Fc > backup_$(date +%Y%m%d_%H%M%S).dump

Verification:

# Check dump file size
ls -lh backup_*.sql

# Verify dump content
head -20 backup_*.sql

# Test dump integrity (for custom format)
docker compose exec postgresql pg_restore --list backup_*.dump

Step 3: Backup Data Directory

# For file-based volumes
sudo cp -a /path/to/database/data /path/to/database/data_backup_$(date +%Y%m%d)

# For Docker volumes
docker volume create your_database_backup
docker run --rm -v your_database:/from -v your_database_backup:/to alpine sh -c 'cd /from && cp -a . /to'

Step 4: Stop Services

# Stop all services
docker compose down

# Verify no containers are running
docker ps | grep your_service

Step 5: Update Docker Compose Configuration

# Example docker-compose.yml changes
services:
  postgresql:
    image: docker.io/library/postgres:16-alpine  # Upgrade version
    network_mode: none  # Temporarily isolate
    # ... rest of configuration

Step 6: Recreate Database Container

# Pull new image and recreate container
docker compose pull postgresql
docker compose up --force-recreate -d postgresql

# Wait for container to be healthy
docker compose ps postgresql

Step 7: Restore Database

# For SQL dumps
cat backup_*.sql | docker compose exec -T postgresql psql -U your_user

# For custom format dumps
docker compose exec -T postgresql pg_restore -U your_user -d your_db backup_*.dump

Step 8: Remove Network Isolation

# Remove network_mode: none from docker-compose.yml
services:
  postgresql:
    image: docker.io/library/postgres:16-alpine
    # network_mode: none  # Remove this line
    # ... rest of configuration

Step 9: Restart Full Stack

# Start all services
docker compose up -d

# Verify all services are healthy
docker compose ps

Version-Specific Considerations

PostgreSQL 12 → 16 Upgrade

Breaking Changes: Minimal breaking changes between 12 and 16
Performance: Significant performance improvements
Security: Enhanced security features
Compatibility: High compatibility with most applications

PostgreSQL 13 → 16 Upgrade

Smooth Upgrade: Very smooth upgrade path
Features: New features like logical replication improvements
Performance: Better query planning and execution

PostgreSQL 14 → 16 Upgrade

Recent Versions: Both are recent, stable versions
Features: Enhanced JSONB operations, better parallel queries
Monitoring: Improved monitoring and observability

Common Issues and Solutions

Issue: Permission Denied on Data Directory

# Solution: Use sudo for file operations
sudo cp -a /path/to/data /path/to/backup
sudo chown -R 999:999 /path/to/data  # Adjust UID/GID as needed

Issue: Database Connection Refused

# Check if PostgreSQL is running
docker compose ps postgresql

# Check logs
docker compose logs postgresql

# Verify network isolation is removed
docker compose exec postgresql psql -U your_user -d your_db

Issue: Application Compatibility

Check Application Logs: Look for PostgreSQL version-specific errors
Update Application: May need to update application dependencies
Test Thoroughly: Test all application functionality

Issue: Performance Degradation

# Check PostgreSQL configuration
docker compose exec postgresql psql -U your_user -d your_db -c "SHOW shared_buffers;"
docker compose exec postgresql psql -U your_user -d your_db -c "SHOW work_mem;"

# Monitor resource usage
docker stats

Rollback Procedures

Quick Rollback (Using File Backup)

# Stop services
docker compose down

# Restore from file backup
sudo rm -rf /path/to/data
sudo cp -a /path/to/backup /path/to/data

# Revert docker-compose.yml
git checkout HEAD -- docker-compose.yml

# Restart services
docker compose up -d

Rollback Using SQL Dump

# Stop services
docker compose down

# Revert to old PostgreSQL version in docker-compose.yml
# Start old version
docker compose up -d postgresql

# Restore from SQL dump
cat backup_*.sql | docker compose exec -T postgresql psql -U your_user

# Restart full stack
docker compose up -d

Best Practices

1. Always Test in Staging

Test the upgrade process in a staging environment first
Use production data (anonymized if needed) for testing
Verify all application functionality

2. Document Everything

Document current configuration
Take screenshots of application state
Record any custom settings or configurations

3. Monitor During Upgrade

Monitor system resources during upgrade
Watch for any error messages
Have monitoring tools ready

4. Plan for Downtime

Communicate downtime to users
Schedule upgrades during maintenance windows
Have a rollback plan ready

5. Verify After Upgrade

Check database connectivity
Test all application features
Monitor performance metrics
Verify data integrity

Automation Scripts

Basic Upgrade Script Template

#!/bin/bash
set -e

# Configuration
DB_USER="your_user"
DB_NAME="your_db"
BACKUP_DIR="/path/to/backups"
DATE=$(date +%Y%m%d_%H%M%S)

echo "Starting PostgreSQL upgrade process..."

# Step 1: Create backup
echo "Creating database dump..."
docker compose exec postgresql pg_dump -U $DB_USER -d $DB_NAME -cC > $BACKUP_DIR/backup_$DATE.sql

# Step 2: Stop services
echo "Stopping services..."
docker compose down

# Step 3: Backup data directory
echo "Backing up data directory..."
sudo cp -a /path/to/data /path/to/data_backup_$DATE

# Step 4: Update docker-compose.yml (manual step)
echo "Please update docker-compose.yml and press Enter to continue..."
read

# Step 5: Recreate database
echo "Recreating database container..."
docker compose pull postgresql
docker compose up --force-recreate -d postgresql

# Step 6: Restore data
echo "Restoring database..."
cat $BACKUP_DIR/backup_$DATE.sql | docker compose exec -T postgresql psql -U $DB_USER

# Step 7: Remove network isolation (manual step)
echo "Please remove network_mode: none from docker-compose.yml and press Enter..."
read

# Step 8: Start full stack
echo "Starting full stack..."
docker compose up -d

echo "Upgrade completed successfully!"

Conclusion

PostgreSQL upgrades in Docker environments follow consistent principles regardless of the specific application. The key is to:

Always backup first (multiple types)
Stop services gracefully
Upgrade in isolation
Test thoroughly
Have a rollback plan

This methodology applies to:

✅ Authentik PostgreSQL upgrades
✅ Custom application databases
✅ Any PostgreSQL service in Docker
✅ Kubernetes deployments (with container orchestration adaptations)

This guide is based on real-world PostgreSQL upgrade experiences and follows industry best practices for database migrations in containerized environments.