PostgreSQL v10 End of Life: How to Upgrade to PostgreSQL 14

PostgreSQL Versions

This table represents currently supported PostgreSQL versions, their final release date (EOL), and major features.

Considerations Before Upgrading PostgreSQL 10

Before you decide to upgrade, you need to consider several factors and analyze the upgrade impact.

• Version compatible parameters
• PostgreSQL Extension
• Reg* data types
• Unknown data types

Postgres 10 Upgrade Methods

There are multiple options to upgrade your PostgreSQL 10 database. The right method depends on your available maintenance windows, the database’s size, available disk space, and compatibility. We recommend upgrading PostgreSQL 10 to PostgreSQL 14 to take advantage of the new features and functionalities shown in the table above.

Upgrade PostgreSQL 10 via pg_dumpall

Using the logical backup tool pg_dumpall, you can dump PostgreSQL 10 data to disk and then reload to the target PostgreSQL version, such as PostgreSQL 14. You will need to shut down the application or keep the database in single-user mode while performing the dump to avoid writes on the source database while the upgrade is being performed.
 

High-level pg_dumpball upgrade steps:

1. Install PostgreSQL 14 binaries
2. Install PostgreSQL extensions
3. Initialize PostgreSQL cluster
4. Perform pg_dumpall/pg_restore
5. Validation of data/objects

Advantages of a pg_dumpall PostgreSQL upgrade include:

• Preferred and quicker option for smaller database sizes.
• Removes fragmentation during the upgrade and shrinks table/index sizes.
• No impact on the source database cluster (easy backout)
• Source and target can be on different servers.

Disadvantages of a pg_dumpall PostgreSQL upgrade include:

• Requires more downtime for large database sizes.
• Additional disk space is required for the new cluster and pd_dumpall.

Upgrade Postgres 10 via pg_upgrade

pg_upgrade allows you to upgrade PostgreSQL 10 to newer versions using the in-place method. There are two options available: copy mode and link mode. When using link mode, databases TBs in size can be upgraded in a few seconds.

If you use copy mode, the upgrade timing will depend on the size of the database, since pg_upgrade creates a separate copy of PGDATA from source to target.
 

High-Level pg_upgrade upgrade steps include:

1. Install PostgreSQL 14 binaries
2. Initialize PostgreSQL cluster
3. Install extensions
4. Execute pg_upgrade with -c option (Consistency check)
5. Execute pg_upgrade and review logs
6. Validation of data/objects

Advantages of a pg_upgrade Postgres 10 upgrade include:

• Preferred and faster option for large database sizes.
• The same storage can be used if the upgrade is performed using –link option.

Disadvantages of a pg_upgrade Postgres 10 upgrade include:

• Needs better planning for backout.
• Source and target must be on the same server.

PostgreSQL community support, enhanced security, easier future upgrades, data protection, cloud migration, and performance improvements are ample reasons to begin planning a PostgreSQL 10 to PostgreSQL 14.

If you need help with the impact analysis, planning, and execution of the PostgreSQL version upgrade, please feel free to contact Datavail to discuss solutions designed for your enterprise. You can learn more about our remote database services and how our experts can help with your ongoing PostgreSQL operations and support.