End of Life for PostgreSQL v12: Strategies on How to Upgrade to PostgreSQL 14/15/16/17

On November 14, 2024, the PostgreSQL 12 major version reached end of life (EOL) after five years of availability. The PostgreSQL community will stop providing bug fixes, security updates, data patches, and maintenance for a version of PostgreSQL that has reached end of life. Any one of the higher PostgreSQL versions indicated in the table below can be upgraded to directly from PostgreSQL 12.  You will probably get the biggest gain for your effort if you upgrade to the highest version your environment will support.

PostgreSQL Features and Versions

The primary features, final release date (EOL), and supported PostgreSQL versions are shown in this table.

Things to Consider Before Upgrading PostgreSQL 12:

Before deciding to upgrade your PostgreSQL 12 database, you should assess the potential impact of the upgrade and take several factors into account.

• Extension Compatibility: Ensure that all installed extensions are compatible with the new PostgreSQL version.
• Configuration Changes: Check for deprecated or modified configuration settings between versions.
• SQL Syntax Changes: Ensure that none of your queries or functions use deprecated SQL features.
• Backup: Always take a full backup of the existing database before performing the upgrade.
• Test the Upgrade: Test the upgrade process on a non-production environment to ensure compatibility.
• Downtime Planning: Depending on the upgrade method, plan for downtime or minimal disruption.
• Application Compatibility: Ensure that applications using PostgreSQL are compatible with the new version.

When upgrading PostgreSQL from version 12 to versions 14, 15, or 16, there are several methods available. Each has its advantages and considerations depending on your environment, downtime tolerance, and complexity. Below are the main upgrade methods:

In-Place Upgrade Using pg_upgrade

This is the most common method for upgrading PostgreSQL and has minimal downtime.

High-Level PostgreSQL 12 pg_upgrade Upgrade Steps

1. Install the new Postgres version (PG14/PG15/PG16) on your system alongside the existing PostgreSQL 12 instance.
2. Stop the PG12 cluster.
3. Run pg_upgrade with the -c option, pointing to the old and new clusters.
4. Analyze the new cluster for performance improvements (ANALYZE on upgraded tables).
5. Start the new PostgreSQL version (PG14/PG15/PG16).

Advantages of pg_upgrade:

• Fast and efficient for upgrading large databases.
• No need to dump and restore data.
• If the upgrade is carried out with the –link option, the same storage can be utilized.

Disadvantages of pg_upgrade:

• The system will be down during the upgrade.
• Risk of issues if extensions or custom plugins are incompatible.
• This in-place upgrade process can take anywhere from a few minutes to potentially hours or more – it entirely depends on how much data needs to be updated between Postgres versions.

Logical Replication

Logical replication allows a near-zero downtime upgrade by replicating data between two different PostgreSQL versions.

Logical Replication offers more precise control over the data to be duplicated by enabling the replication of individual database objects, particular tables, or even filtered rows.

High-Level Postgres 12 Logical Replication Upgrade Steps:

1. Install the new PostgreSQL version (PG14/PG15/PG16) on the same or a different server.
2. Set up logical replication from the PostgreSQL 12 database to PG14/PG15/PG16.
3. Monitor replication to ensure all data is copied.
4. Switch clients to the new server after replication catches up.
5. Update the sequences.
6. Drop the old Postgres 12 instance once confirmed that the new version is functioning as expected.

Advantages of Logical Replication:

• Minimal downtime since data is replicated while the old instance is live.
• Can be done across different servers.
• No risk of data corruption, as the upgrade happens on a new instance.

Disadvantages of Logical Replication:

• Requires more configuration than pg_upgrade.
• Performance overhead while replicating.
• Either a duplication of the server or at least the disk space used by the database is required.
• Complex solution and needs expertise on setting and configuring replication.

Dump and Restore Using pg_dump

This method involves dumping all data from PostgreSQL 12 and restoring it to a fresh Postgres 14, 15, or 16 instance.

High-Level PostgreSQL 12 pg_dump Upgrade Steps:

1. Stop the Postgres 12 cluster to ensure data consistency.
2. Run pg_dumpall to create a complete backup of the PG 12 database.
3. Install PG14/PG15/PG16 on the new system.
4. Restore the dump into the new PG14/PG15/PG16 instance using psql.
5. Start the new cluster.

Advantages of pg_dump:

• Good option for moving to a new server if the database size is small.
• Ensures clean data in the new database with no old metadata.
• No impact on the source database cluster (easy to perform rollback).

Disadvantages of pg_dump:

• Longer downtime for large databases (can take hours for large datasets).
• Requires sufficient disk space for the dump file.

Final Checklist Before Cutting Over to PostgreSQL 14, 15, or 16:

Here are some of the items we evaluated before carrying out our final PostgreSQL cutover:

1. Is your new database scaled appropriately for your workloads?
2. Validation of all objects and data post upgrade.
3. Have you reindexed and performed basic VACUUM\Analyze maintenance on the new database to ensure that it is clean and suitable for production cutover?
4. Perform practice runs in a staging or test environment until you’ve completely tested the cutover procedure several times. Dry runs like these will help you identify flaws in your plan before going into production.
5. Run pg_bench against the database to verify the performance of the database.

There are several reasons to start planning a PostgreSQL 12 to PostgreSQL 14/15/16 upgrade, including the PostgreSQL community support, improved security, simpler future updates, data protection, cloud migration, and performance enhancements.

If you require assistance with the impact analysis, planning, and implementation of a PostgreSQL version upgrade, get in touch with our dedicated PostgreSQL expert team.