Introduction
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 -yStep 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 - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" | sudo tee /etc/apt/sources.list.d/postgresql-pgdg.list > /dev/null
sudo apt-get update -yStep 3: Install PostgreSQL Now, you can install the desired PostgreSQL version. In this example, we’ll install PostgreSQL 14.
sudo apt-get install postgresql-14Step 4: Stop the PostgreSQL Service Before you proceed with the upgrade, you must stop the PostgreSQL service.
bash
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
ok
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
ok
Copying user relation files
ok
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-stagesStep 9: Clean Up You can delete the old cluster's data files by running the provided script:
./delete_old_cluster.shConclusion
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.
Thanks for the great article. Can you please provide details of what is involved in running ‘consistency checks’ for Step 7.
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
WHat OS version was this performed on?
Ubuntu latest version