Upgrading PostgreSQL to a newer version is essential for maintaining optimal performance, enhancing security, and accessing the latest features. This guide provides a detailed walkthrough for upgrading PostgreSQL from version 11.4 to version 14.9, covering the necessary steps such as data backup, preparation, execution of the upgrade, and verification of results.
1. Backup Your Data
Before initiating any changes, it is crucial to back up your existing PostgreSQL data. This precaution ensures that you have a safety net in case any issues arise during the upgrade process.
- Using
pg_dumpall:
pg_dumpall > all_databases_backup.sql
pgBackRest is available:pgbackrest --stanza=main --type=full backup
These commands create a comprehensive backup of your current databases, which will be invaluable should you need to restore your data later.
2. Verify Current PostgreSQL Version
Check the version of your current PostgreSQL installation to confirm it is version 11.4. 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 wish to test the upgrade process, consider creating 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;
3. Download and Install the Updated PostgreSQL Package
To upgrade to PostgreSQL 14, 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, refer to the official PostgreSQL website for instructions on building PostgreSQL from source. You may also find it helpful to read Installing Postgres from Source Code.
After installation, 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 offers the pg_upgrade tool for upgrading databases from an older version to a newer one. This tool ensures your old data is compatible with the new PostgreSQL version. Execute pg_upgrade with the following command:
pgbackrest --stanza=main --type=full backup
0
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:
select version();
Verify that your databases and tables are intact:
pgbackrest --stanza=main --type=full backup
3
If everything appears correct, you may 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:
pgbackrest --stanza=main --type=full backup
4
9. Post-Upgrade Testing
Finally, conduct thorough tests on your databases to ensure all functions and performance metrics meet expectations. Check for any compatibility issues or performance regressions that may have arisen due to the upgrade. By adhering to these steps, you can successfully upgrade PostgreSQL from version 11.4 to 14, ensuring your database environment remains secure, efficient, and equipped with the latest features.
