Select Page

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

Author: Shailesh Rangani | | December 7, 2022

PostgreSQL 10 major version reached end of life (EOL) on November 10, 2022. When a PostgreSQL version reaches end of life, the PostgreSQL community will not include this version in bug fixing, security patches, data patches, and maintenance activities. PostgreSQL 10 was released at the beginning of 2017, so it makes sense to upgrade now. You can upgrade PostgreSQL 10 directly to one of higher PostgreSQL version shown in the below table.

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.

How to Solve the Oracle Error ORA-12154: TNS:could not resolve the connect identifier specified

The “ORA-12154: TNS Oracle error message is very common for database administrators. Learn how to diagnose & resolve this common issue here today.

Vijay Muthu | February 4, 2021

How to Recover a Table from an Oracle 12c RMAN Backup

Our database experts explain how to recover and restore a table from an Oracle 12c RMAN Backup with this step-by-step blog. Read more.

Megan Elphingstone | February 2, 2017

Data Types: The Importance of Choosing the Correct Data Type

Most DBAs have struggled with the pros and cons of choosing one data type over another. This blog post discusses different situations.

Craig Mullins | October 11, 2017

Subscribe to Our Blog

Never miss a post! Stay up to date with the latest database, application and analytics tips and news. Delivered in a handy bi-weekly update straight to your inbox. You can unsubscribe at any time.

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.

CONTACT US

Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.

EXPLORE JOBS