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 - 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 -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 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-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.