Postgres DB v14 → v18 Migration Guideline

This article provides a comprehensive, step-by-step plan for upgrading your PostgreSQL database from version 14 to 18. It addresses the specifics of your environment, including Docker on a GCP VM and the critical Debezium CDC pipeline.

Phases Timeline

---

Phase 1: Pre-Upgrade Preparation & Analysis

Success hinges on meticulous preparation. This phase involves a thorough analysis of your current environment, ensuring all components are compatible with PostgreSQL 18, and establishing a robust backup and recovery strategy.

Preparation Checklist

• Analyze & Document Current Environment
• Verify Debezium & Connector Compatibility
• Perform Full Logical Backup (pg_dumpall)
• Create GCP VM Snapshot
• Setup Isolated Staging Environment

Cross-Version Compatibility Matrix

Review the breaking changes for each major version between 14 and 18.

Status Key:

• ✅ Confirmed Compatible
• ❓ Needs Verification
• ❌ Known Incompatibility

PostgreSQL Version Highlights

PostgreSQL 15

• Adds the
  MERGE
  SQL command
• Selective logical replication
• Improved performance for in-memory and on-disk sorting

PostgreSQL 16

• Significant parallelism improvements for queries
• pg_stat_io
  for detailed I/O statistics
• Logical replication from standby servers

PostgreSQL 17 (Anticipated)

• Incremental backup capabilities
• Further JSON performance enhancements
• New system views for monitoring

PostgreSQL 18 (Anticipated)

• Potential for built-in connection pooling
• Advanced query optimization features
• ⚠️ Risk: As a new version, Debezium and extension compatibility may not be immediately available. Thorough testing is paramount.

---

Phase 2: Staging Environment Rehearsal

This is where the upgrade is performed in an isolated staging environment. The goal is to validate the entire process, including the

utility within Docker, and to conduct thorough testing of the application and the Debezium CDC pipeline before touching production.

In-Place Upgrade Process using

pg_upgrade

in Docker

Step 1: Prepare Staging Environment

Stop application and Debezium. Ensure the PG14 container is stopped cleanly. Create final backups of the PG14 data volume.

docker stop my-app-container debezium-connector-container
docker stop postgres14-container
cp -a /path/to/pg14/data /path/to/pg14/data-backup

Step 2: Initialize New PG18 Data Directory

Start a temporary PG18 container to initialize a new, empty data directory. Then stop it.

mkdir /path/to/pg18/data
docker run --rm -v /path/to/pg18/data:/var/lib/postgresql/data postgres:18 initdb
# The container will initialize and exit.

Step 3: Run

pg_upgrade

Run a PG18 container with both old and new data volumes mounted to execute the upgrade.

docker run --rm \
	-v /path/to/pg14/data:/var/lib/postgresql/14/data \
	-v /path/to/pg18/data:/var/lib/postgresql/18/data \
	postgres:18 \
	bash -c "pg_upgrade -b /usr/lib/postgresql/14/bin -B /usr/lib/postgresql/18/bin -d /var/lib/postgresql/14/data -D /var/lib/postgresql/18/data --check"

# If check succeeds, run again without --check flag

Step 4: Post-Upgrade & Validation

Start the new PG18 container. Run

and perform full regression and CDC testing.

docker run -d --name postgres18-container -v /path/to/pg18/data:/var/lib/postgresql/data postgres:18
# Wait for startup, then run analyze script.# Start application & Debezium against new DB.# THOROUGHLY test all functionality.

---

Phase 3: Production Go-Live Execution

This is the execution of the upgrade in the production environment. Schedule a maintenance window, communicate with stakeholders, and follow the exact, validated procedure from the staging rehearsal. Monitor key metrics closely during and after the upgrade.

⚠️ Important Warning

Only proceed if the staging rehearsal was 100% successful with no outstanding issues. Have the rollback plan readily available.

Go-Live Checklist

1. Announce maintenance window start
2. Stop all application traffic and inbound data flows
3. Stop Debezium connector and application services
4. Perform final
   pg_dumpall
   backup and GCP snapshot
5. Execute the validated upgrade script from Phase 2
6. Perform smoke tests: connect to DB, check key tables
7. Restart application services and perform critical path testing
8. Restart Debezium connector and verify CDC data flow to Kafka
9. Gradually re-enable application traffic while monitoring closely
10. Announce maintenance window end

---

Phase 4: Post-Upgrade Monitoring & Cleanup

After the successful upgrade, a period of heightened monitoring is crucial to catch any performance regressions or subtle issues. Once the new system is deemed stable, you can proceed with cleaning up old artifacts.

Monitoring Period (72 hours)

Monitor the following metrics:

• Database CPU, Memory, and I/O utilization
• Query performance and latency
• Application error rates and logs
• Debezium connector health and CDC lag
• Kafka topic message counts

Cleanup Activities

After stability is confirmed (e.g., 1 week):

• Delete the pre-upgrade GCP VM snapshot
• Archive the final logical backup (
  pg_dumpall
  )
• Delete the old PostgreSQL 14 data volume from the VM
• Update all infrastructure documentation to reflect the new PostgreSQL 18 environment

---

Contingency: Rollback Plan

A rollback is triggered if a critical failure occurs during the production upgrade that cannot be resolved quickly. The primary method is to restore the logical backup into a new PG14 instance.

Rollback Trigger Conditions

• pg_upgrade
  process fails and cannot be fixed within 30 minutes
• Critical application functionality is broken after the upgrade
• Debezium CDC pipeline fails to connect or process data from PG18
• Data corruption is detected during post-upgrade checks

Rollback Procedure

1. Trigger Condition Met
              ↓
2. Communicate Rollback Decision
              ↓
3. Stop & Remove Failed PG18 Container
              ↓
4. Deploy New PG14 Container Instance
              ↓
5. Restore Data from Pre-Upgrade Logical Backup
              ↓
6. Point Apps & Debezium to Restored PG14 DB
              ↓
7. Verify System Functionality & End Maintenance

Detailed Rollback Steps

# 1. Stop the failed PG18 containerdocker stop postgres18-container
docker rm postgres18-container
# 2. Deploy new PG14 container
docker run -d --name postgres14-container \  -v /path/to/new/pg14/data:/var/lib/postgresql/data \  postgres:14
# 3. Restore from backup
docker exec -i postgres14-container psql -U postgres < /path/to/backup/pg_dumpall_backup.sql
# 4. Verify restoration
docker exec postgres14-container psql -U postgres -c "SELECT version();"
# 5. Restart application and Debezium servicesdocker start my-app-container
docker start debezium-connector-container
# 6. Perform validation tests
# - Check database connectivity
# - Verify critical data
# - Test CDC pipeline

---

Summary

This comprehensive upgrade plan provides a structured approach to migrating from PostgreSQL 14 to PostgreSQL 18. The phased approach ensures:

• Thorough preparation with compatibility checks and backups
• Risk mitigation through staging environment testing
• Controlled execution with clear go-live procedures
• Safety nets with detailed rollback plans

Key Success Factors:

• Complete compatibility verification before proceeding
• Full staging rehearsal with 100% success rate
• Comprehensive backup strategy
• Clear communication with stakeholders
• Detailed monitoring post-upgrade

Timeline: Approximately 16 days from preparation to post-upgrade stability confirmation.