PostgreSQL 14 to 16 Migration Guide with `pg_dump` and `pg_restore`

Upgrading your PostgreSQL database from version 14 to 16 is a crucial step to benefit from performance improvements, new features, and long-term support. This guide walks you through a safe, step-by-step migration process using pg_dump and pg_restore—ensuring data integrity while minimizing downtime.

🚀 Why Upgrade to PostgreSQL 16?

PostgreSQL 16 brings significant enhancements in query optimization, parallelism, and memory management. Upgrading helps you:

• Improve query performance and scalability.
• Maintain compatibility with the latest frameworks and applications.
• Strengthen security with updated features and patches.

🛠 Preparation Before Migration

1. Check Compatibility – Verify that all applications, drivers, and extensions you use are compatible with PostgreSQL 16.
2. Full Backup First – Never skip a complete backup before starting.
3. Install PostgreSQL 16 – Set up the new version on the same server, a new container, or a dedicated machine.

📦 Step 1 – Export Data from PostgreSQL 14

If you’re running PostgreSQL in Docker:

docker exec -it sonarqube-db-1 bash
pg_dump -U sonar -d sonarqube -F c -f /tmp/sonarqube_backup.dump

If running on a host machine:

pg_dump -U username -h localhost -p 5432 -F c -b -v -f backup.dump dbname

Key options explained:

• -F c – Creates a custom-format backup suitable for pg_restore.
• -b – Includes large objects (BLOBs).
• -v – Enables verbose mode for more detailed output.

🛑 Step 2 – Stop PostgreSQL 14

In Docker:

docker-compose down

On a host machine, stop the PostgreSQL 14 service:

sudo systemctl stop postgresql@14-main

📥 Step 3 – Deploy PostgreSQL 16

Docker Compose example:

db:
  image: postgres:16
  restart: unless-stopped
  environment:
    - POSTGRES_USER=sonar
    - POSTGRES_PASSWORD=PASSWORD
    - POSTGRES_DB=sonarqube
  volumes:
    - sonarqube_db:/var/lib/postgresql
    - postgresql_data:/var/lib/postgresql/data

Start the new container:

docker-compose up -d

🔄 Step 4 – Restore Data to PostgreSQL 16

In Docker:

docker exec -it sonarqube-db-1 bash
pg_restore -U sonar -d sonarqube /tmp/sonarqube_backup.dump

On a host machine:

pg_restore -U username -h localhost -p 5433 -d dbname -v backup.dump

Use the correct port if your PostgreSQL 16 instance runs on a non-default port (e.g., -p 5433).

🔍 Step 5 – Verify the Migration

1. Connect to the new database:

   psql -U username -h localhost -p 5433 dbname
   

2. Check your tables, data integrity, and user permissions.

⚡ Step 6 – Optimize PostgreSQL Memory

Edit postgresql.conf (usually in /var/lib/postgresql/data):

shared_buffers = 512MB
work_mem = 4MB
maintenance_work_mem = 64MB

Restart the service or container to apply changes:

docker restart sonarqube-db-1

🧹 Step 7 – Clean and Reindex the Database

Connect to PostgreSQL:

docker exec -it sonarqube-db-1 bash
psql -U postgres

Run maintenance commands:

VACUUM;              -- Basic cleanup
VACUUM ANALYZE;      -- Cleanup + statistics update
VACUUM FULL;         -- Deep reorganization
REINDEX DATABASE sonarqube;  -- Rebuild all indexes

For specific tables or indexes:

REINDEX TABLE your_table_name;
REINDEX INDEX your_index_name;

Exit PostgreSQL:

\q

Exit the container:

exit

🔄 Step 8 – Restart Dependent Services

If your database is used by applications like SonarQube:

docker restart sonarqube-db-1

✅ Summary

By following this backup-and-restore approach with pg_dump and pg_restore, you:

• Ensure a safe and controlled upgrade from PostgreSQL 14 to 16.
• Maintain data integrity during migration.
• Improve performance with memory optimization and database cleanup.

Pro Tip: Always test the migration process in a staging environment before applying it to production.

🚀 Explore more guides on our blog 👉 blog.1it.pro
📧 Contact us: admin@1it.pro for expert IT guidance.
🌐 Explore more: Visit 1it.pro for top-tier IT solutions.