PostgreSQL is a powerful, open-source relational database management system that continues to evolve with each new release. Upgrading to a newer version of PostgreSQL can provide improved performance, security enhancements, and access to new features. In this guide, we will walk you through the process of upgrading PostgreSQL on an Ubuntu-based system.

Step 1: Prepare Your System Before you can begin the PostgreSQL upgrade process, it’s essential to prepare your system. Ensure that you have administrative privileges and take a backup of your data to prevent any potential data loss.

sudo apt-get update -y && sudo apt-get upgrade -y

Step 2: Add PostgreSQL Repository You need to add the PostgreSQL repository to access the desired version. This allows you to install the desired PostgreSQL version (in this example, PostgreSQL 14).

 wget --quiet -O - | sudo apt-key add -
 echo "deb $(lsb_release -cs)-pgdg main" | sudo tee /etc/apt/sources.list.d/postgresql-pgdg.list > /dev/null
 sudo apt-get update -y

Step 3: Install PostgreSQL Now, you can install the desired PostgreSQL version. In this example, we’ll install PostgreSQL 14.

sudo apt-get install postgresql-14

Step 4: Stop the PostgreSQL Service Before you proceed with the upgrade, you must stop the PostgreSQL service.

sudo systemctl stop postgresql.service

Step 5: Upgrade PostgreSQL

Upgrade from PostgreSQL 9.6

If you’re upgrading from PostgreSQL 9.6, use the following commands:

Upgrade from PostgreSQL 9.6

/usr/lib/postgresql/14/bin/pg_upgrade \
--old-datadir=/var/lib/postgresql/9.6/main \
--new-datadir=/var/lib/postgresql/14/main \
--old-bindir=/usr/lib/postgresql/9.6/bin \
--new-bindir=/usr/lib/postgresql/14/bin \
--old-options '-c config_file=/etc/postgresql/9.6/main/postgresql.conf' \
--new-options '-c config_file=/etc/postgresql/14/main/postgresql.conf'

Upgrade from PostgreSQL 10

/usr/lib/postgresql/14/bin/pg_upgrade \
--old-datadir=/var/lib/postgresql/10/main \
--new-datadir=/var/lib/postgresql/14/main \
--old-bindir=/usr/lib/postgresql/10/bin \
--new-bindir=/usr/lib/postgresql/14/bin \
--old-options '-c config_file=/etc/postgresql/10/main/postgresql.conf' \
--new-options '-c config_file=/etc/postgresql/14/main/postgresql.conf'

Step 6: Start the New PostgreSQL Cluster After successfully upgrading PostgreSQL, start the new cluster.

/usr/lib/postgresql/14/bin/pg_ctl start  -D "/var/lib/postgresql/14/main" -o "-c config_file=/etc/postgresql/14/main/postgresql.conf"

Step 7: Perform Consistency Checks Perform consistency checks to ensure everything is in order.

Performing Consistency Checks
Checking cluster versions                                   ok
Checking database user is the install user                  ok
Checking database connection settings                       ok
Checking for prepared transactions                          ok
Checking for system-defined composite types in user tables  ok
Checking for reg* data types in user tables                 ok
Checking for contrib/isn with bigint-passing mismatch       ok
Checking for user-defined encoding conversions              ok
Checking for user-defined postfix operators                 ok
Checking for incompatible polymorphic functions             ok
Checking for tables WITH OIDS                               ok
Checking for invalid "sql_identifier" user columns          ok
Checking for invalid "unknown" user columns                 ok
Creating dump of global objects                             ok
Creating dump of database schemas
Checking for presence of required libraries                 ok
Checking database user is the install user                  ok
Checking for prepared transactions                          ok
Checking for new cluster tablespace directories             ok

If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.

Performing Upgrade
Analyzing all rows in the new cluster                       ok
Freezing all rows in the new cluster                        ok
Deleting files from new pg_xact                             ok
Copying old pg_clog to new server                           ok
Setting oldest XID for new cluster                          ok
Setting next transaction ID and epoch for new cluster       ok
Deleting files from new pg_multixact/offsets                ok
Copying old pg_multixact/offsets to new server              ok
Deleting files from new pg_multixact/members                ok
Copying old pg_multixact/members to new server              ok
Setting next multixact ID and offset for new cluster        ok
Resetting WAL archives                                      ok
Setting frozenxid and minmxid counters in new cluster       ok
Restoring global objects in the new cluster                 ok
Restoring database schemas in the new cluster
Copying user relation files
Setting next OID for new cluster                            ok
Sync data directory to disk                                 ok
Creating script to delete old cluster                       ok
Checking for hash indexes                                   ok
Checking for extension updates                              ok

Upgrade Complete

Step 8: Complete the Upgrade If the consistency checks pass, your upgrade is complete. Keep in mind that optimizer statistics are not transferred by pg_upgrade. Consider running the following command to optimize your database:

/usr/lib/postgresql/14/bin/vacuumdb --all --analyze-in-stages
Step 9: Clean Up You can delete the old cluster's data files by running the provided script:



Upgrading PostgreSQL on Ubuntu can be a complex process, but by following this guide, you can safely migrate to a newer version and enjoy the benefits of enhanced performance and security. Always remember to backup your data and test the upgrade process in a non-production environment before applying it to your live system to minimize any potential risks.

By Ray Lee (System Analyst)

iDempeire ERP Contributor, 經濟部中小企業處財務管理顧問 李寶瑞

4 thoughts on “PostgreSQL Time Travel: Upgrading from Version 9.6 to 14”
  1. Thanks for the great article. Can you please provide details of what is involved in running ‘consistency checks’ for Step 7.

  2. FYI – I have found an answer to my earlier question. You run the upgrade with check option.
    /usr/pgsql-14/bin/pg_upgrade -d /var/lib/pgsql/9.6/data/ -D /var/lib/pgsql/14/data/ -b /usr/pgsql-9.6/bin/ -B /usr/pgsql-14/bin/ -c

Leave a Reply

Your email address will not be published. Required fields are marked *