Upgrade Docker Postgres Version

Step-by-Step Upgrade Workflow

### STEP 1: BACKUP CURRENT DATABASE

# Connect to existing container

docker compose exec plausible_db bash

# Internal dump to container volume

pg_dumpall -U postgres > /var/lib/postgresql/backup.sql

# Exit container

exit

### STEP 2: EXTRACT BACKUP FROM VOLUME

# Copy SQL backup to host via docker compose cp

docker compose cp db:/var/lib/postgresql/backup.sql ./backup_$(date +%F).sql

### STEP 3: MIGRATE TO NEW CONTAINER

# Verify backup integrity before migration

head -n 20 backup_*.sql  # Check header

# Update compose.yaml image tag

version: '3.8'
services:
plausible_db:
image: postgres:16-version  # Updated here assume you upgrade to pg16

# Launch new container

docker compose up -d db

# Insert backup into new container

docker compose cp backup_date.sql db:/restore.sql

# Execute under db

docker compose exec -it db

cat restore.sql | psql -U postgres

exit

docker compose down && docker compose up

🔧 Best Practices

graph TD
    A[Stop Services] --> B[pg_dumpall Backup]
    B --> C[Spin up New Container]
    C --> D[Restore Backup]
    D --> E[Test Compatibility]
    E --> F[Update compose.yaml]
    F --> G[Restart Stack]

🔍 Pro Tips

Use ephemeral containers for testing migrations:

docker run --rm -v $(pwd)/backup.sql:/backup.sql postgres:15 \
   bash -c "psql -U postgres -f /backup.sql"

After migrate, make sure delete old volumes incase redundated file!

💡 Key Resources:
Official PostgreSQL Upgrade Guide
Docker Postgres Image Docs
pg_upgrade Deep Dive

Test your upgrade in staging first!