Step-by-Step Guide to Migrating from PostgreSQL 11 to 15 Using pg_upgrade

Introduction

To use pg_upgrade for migrating from PostgreSQL 11 to PostgreSQL 15, follow these steps:

Runbook to Migrate from PostgreSQL 11 to 15

1. Install PostgreSQL 15: Install the new PostgreSQL version alongside the old one without removing PostgreSQL 11.
2. Prepare the Environment:
   • Stop the PostgreSQL 11 server.
   • Ensure both versions have the same locale and encoding settings.
3. Run pg_upgrade:
   • Use the pg_upgrade tool provided with PostgreSQL 15.
   • Run it as the same user owning the database processes.
   • Specify the old and new data directories and server binaries.
4. Example Command:
   pg_upgrade \\\\ --old-datadir=/var/lib/pgsql/11/data \\\\ --new-datadir=/var/lib/pgsql/15/data \\\\ --old-bindir=/usr/lib/postgresql/11/bin \\\\ --new-bindir=/usr/lib/postgresql/15/bin

   1 2 3 4 5 6

   pg_upgrade \\\\   --old-datadir=/var/lib/pgsql/11/data \\\\   --new-datadir=/var/lib/pgsql/15/data \\\\   --old-bindir=/usr/lib/postgresql/11/bin \\\\   --new-bindir=/usr/lib/postgresql/15/bin

5. Post-Upgrade Steps:
   • Start the PostgreSQL 15 server.
   • Run ANALYZE to update statistics.
   • Check for any deprecated features or compatibility issues.
6. Testing:
   • Test your applications against the new server.
   • Monitor for performance and stability.

Conclusion

Make sure to backup your data before starting the upgrade process. Additionally, testing the upgrade in a non-production environment first is highly recommended.