How to Upgrade PostgreSQL Version

Upgrading PostgreSQL to a newer version is crucial for ensuring optimal performance, security, and access to the latest features. In this guide, we will walk you through the process of upgrading PostgreSQL from version 11.4 to version 14.13. This includes backing up your data, preparing for the upgrade, executing the upgrade, and verifying the results.

1. Backup Your Data

Before making any changes, it’s essential to back up your existing PostgreSQL data. This step ensures that you have a safety net in case something goes wrong during the upgrade process.

Using pg_dumpall:

pg_dumpall > all_databases_backup.sql

If pgBackRest is available:

pgbackrest --stanza=main --type=full backup

These commands create a comprehensive backup of your current databases, which will be helpful if you need to restore your data later.

2. Verify Current PostgreSQL Version

Check the version of your current PostgreSQL installation to confirm it’s version 11.4. You can use the following command:

select version();

Example output:

PostgreSQL 11.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.4.1 20230605 (Red Hat 11.4.1-3), 64-bit

If your database is empty or you want to test the upgrade process, you can create sample databases and tables:

create database oz;

\c oz --connect database with psql

create schema company;

create table company.customer (name varchar(10), surname varchar(10), number integer);

insert into company.customer (name, surname, number) values ('kemal', 'oz', 12), ('ali', 'oz', 13);

select * from company.customer;

/* OUTPUT 
name  | surname | number
-------+---------+--------
 kemal | oz      |     12
 ali   | oz      |     13
(2 rows)
*/

3. Download and Install the Updated PostgreSQL Package

To upgrade to PostgreSQL 14, you need to download and install the new version. For binary installations, use the following commands:

sudo dnf install -y postgresql14-server postgresql14-contrib

For source code installations, follow the instructions from the official PostgreSQL website to build PostgreSQL from source . Also, you may read Installing Postgres from Source Code

After installing, create a new data directory:

mkdir /pg_data/DATA_NEW/

Initialize the new PostgreSQL data directory:

/usr/pgsql-14/bin/initdb -D /pg_data/DATA_NEW/

Start the new PostgreSQL instance:

/usr/pgsql-14/bin/pg_ctl -D /pg_data/DATA_NEW/ start

4. Stop the PostgreSQL Service

To perform the upgrade, stop both the old and new PostgreSQL services:

/usr/pgsql-14/bin/pg_ctl -D /pg_data/DATA_NEW/ stop
/pg_data/11.4/bin/pg_ctl -D /pg_data/DATA stop

5. Perform the Upgrade Using pg_upgrade

PostgreSQL provides the pg_upgrade tool for upgrading databases from an old version to a new one. This tool is used to make your old data compatible with the new PostgreSQL version.

Run pg_upgrade with the following command:

sudo -u postgres pg_upgrade -b /pg_data/11.4/bin/ -B /usr/pgsql-14/bin/ -d /pg_data/DATA -D /pg_data/DATA_NEW/

Alternatively, set environment variables for the old and new PostgreSQL binaries and data directories, and then execute pg_upgrade:

export PGBINOLD=/pg_data/11.4/bin/
export PGBINNEW=/usr/pgsql-14/bin/
export PGDATAOLD=/pg_data/DATA
export PGDATANEW=/pg_data/DATA_NEW/

/usr/pgsql-14/bin/pg_upgrade

6. Restart the PostgreSQL Service

After the upgrade is complete, restart the PostgreSQL service with the new version:

/usr/pgsql-14/bin/pg_ctl -D /pg_data/DATA_NEW/ start

7. Verify and Clean Up

Check the version of PostgreSQL to ensure the upgrade was successful:

postgres=# select version();

Version output:

PostgreSQL 14.13 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.4.1 20231218 (Red Hat 11.4.1-3), 64-bit

Verify that your databases and tables are intact:

\c oz --connect oz database with psql
select * from company.customer;

 name  | surname | number
-------+---------+--------
 kemal | oz      |     12
 ali   | oz      |     13
(2 rows)

If everything is correct, you can now remove the old PostgreSQL installation and data directory if they are no longer needed.

8. Perform Post-Upgrade Maintenance

Run a vacuum on all databases to optimize performance and analyze statistics:

/usr/pgsql-14/bin/vacuumdb --all --analyze-in-stages

9. Post-Upgrade Testing

Finally, thoroughly test your databases to ensure that all functions and performance metrics are as expected. Check for any compatibility issues or performance regressions that may have arisen due to the upgrade. By following these steps, you can successfully upgrade PostgreSQL from version 11.4 to 14, ensuring that your database environment remains secure, efficient, and up-to-date with the latest features. For more detailed and technical articles like this, keep following our blog on Medium. If you have any questions or need further assistance, feel free to reach out in the comments below and directly.