This article will show you how to safely upgrade the PostgreSQL database server to the latest version on your Linux system.
First, Back Up Your Databases!
Before making any changes to your system, you should perform a full backup – that way if something goes wrong, you can roll back to a working version quickly. At the very least, make sure that you’ve backed up your PostgreSQL Databases to protect from data loss.
What is a Major/Minor Release?
A major release is an update that greatly changes the functionality or compatibility of software. Take for example the following version number:
12.4.3
The 12 is the major release, while the 4 is the minor release, and the 3 is the current patch to the minor release.
When updating software through your package manager, usually the patch is updated. These updates will usually be compatible with the prior releases for that major/minor release, so you can just install the patch (after backing up) and carry on.
Major (and often minor) releases, however, often introduce incompatibilities with previous versions. They may change the API or the names of functions or parameters, or make changes to the data structure used when storing files, meaning that data stored or created on old versions needs to be migrated to work.
Why Upgrade to a New Major/Minor Releases?
It’s important to stay up-to-date. Older releases receive reduced support, and will usually stop receiving security patches after a period of time. New features that might help you build your projects are also included in new major releases, so you’re hindering your projects by not being on the latest version.
Updated software releases also keep it compatible with new OS releases, and some frameworks will also phase out their support for older versions of the software they integrate with.
Sometimes You Don’t Have a Choice
If you aren’t careful when installing OS updates and updating via your Linux systems package manager, you might not have much say on whether a new release is installed. For example, performing a full dist-upgrade to the latest version of Ubuntu might trigger a major release update of PostgreSQL if the version you currently have installed is not compatible with the newer OS.
For this reason it is important to read the prompts you receive when running package and OS updates to make sure that nothing will be upgraded that you are not yet prepared to upgrade. Otherwise, you may get a nasty surprise.
Are Databases Compatible Across PostgreSQL Versions?
Postgresql’s user data stays largely consistent across versions – your data is compatible across versions and can be imported and exported between major releases (unless they’re very distant in version number). The system tables are a different story, however. The layout of system tables between PostgreSQL major releases is likely to change as features are added and changes are made.
For this reason, PostgreSQL provides upgrade tools to let you safely migrate your entire Postgres database system (known as a cluster) to the latest version – this migrates both your user data and the system tables, rather than you having to export and re-import data.
Where Are My Databases Stored?
You databases should be stored in a folder named to the version of the PostgreSQL server they were created with, for example:
var/lib/postgresql/9.1/main
This prevents conflicting data and overwrites between releases, after you have installed a newer version of Postgres, you can migrate data from the old version’s storage location.
Make sure you confirm the location of these files by running:
sudo pg_lsclusters
To list the user data locations for all versions of PostgreSQL installed. The output should look something like this:
Ver Cluster Port Status Owner Data directory Log file 9.1 main 5433 online postgres /var/lib/postgresql/9.1/main /var/log/postgresql/postgresql-9.1-main.log 11 main 5432 online postgres /var/lib/postgresql/11/main /var/log/postgresql/postgresql-11-main.log
The Version Number Doesn’t Appear in the Path to My Data
If the version number of PostgreSQL is not included in the path to your data, you should move it out of the way before upgrading so it isn’t overwritten, for example:
mv /usr/local/pgsql /usr/local/pgsql.VERSIONNUMBER
Upgrading to the Latest Version of PostgreSQL
So the first step is to leave your existing Postgres installation where it is so that it’s data can be migrated – which is covered above – so that box is checked.
Stopping the Running PostgreSQL Server
Before upgrading you should stop all running PostgreSQL clusters:
sudo systemctl stop postgresql
The above command should work on all major Linux distributions.
Next, you will need to install the newer version of postgres. How you do this will depend on which Linux distribution you are running:
Upgrading Postgres on Ubuntu / Debian
When installing PostgresSQL initially, you probably ran:
sudo apt install postgresql
This transparently installed the latest available version of postgres on your system, the equivalent of running:
sudo apt install postgresql-12
…provided version 12 was the latest version at the time of installation.
So, to install a newer version than what is installed, you just need to run the command to install that version:
sudo apt install postgresql-13
If the newer version of PostgreSQL is not available in your systems default software repository, check out our guide to installing the latest version here.
Upgrading Postgres on Red Hat/Centos/Fedora
Head over to the PostgreSQL Red Hat download page to choose the version of PostgreSQL for your version of Red Hat, and follow the instructions here.
Upgrading Postgres on Arch Linux
Simply running:
pacman -S postgresql
Will update postgresql to whatever the latest version is, major or minor releases included, so be careful when running it! It is recommended that you configure Arch to skip updates for the postgresql package for this reason.
Upgrading Postgres From Source
If you are running a less-popular distribution of Linux that does not include PostgreSQL in its software repository, you can build the new version from source, as detailed here.
Note that this is probably not what you want to do if you’re a beginner – if there is a packaged version of Postgres available for you, it’s recommended to use it.
Migrating Your Data to the New Version Using pg_upgrade
Now that you have the new version of Postgres installed alongside the old, you can migrate the data from your old PostgreSQL installation.
The pg_upgrade command migrates your data between PostgreSQL clusters running on different versions of the software. This function ships with all recent releases of Postgres, and this method should work with any flavour of Linux.
sudo pg_upgrade --old-datadir /path/to/old/data \
--new-datadir /path/to/new/data \
--old-bindir /usr/lib/postgresql/OLDVERSION/bin" \
--new-bindir /usr/lib/postgresql/NEWVERSION/bin"
Above, replace OLDVERSION with the old version of Postgres on your system (which you discovered when you ran pg_lsclusters earlier) and NEWVERSION with the upgraded version.
The binddir parameters are the paths to the bin directories that contain the postgres executables. If they are not located at the above paths, you can find them using the which command.
Note the backslashes (\) above – these allow for new lines in the code snippet to make it more readable.
Deactivating or Uninstalling the Old Version
You may want to leave the old version of PostgreSQL installed for some period of time to ensure that everything is working properly before removing it. If you don’t want to leave it running, you can start and stop individual clusters using the following command:
sudo pg_ctlcluster VERSIONNUMBER stop sudo pg_ctlcluster VERSIONNUMBER start
When you’ve confirmed your data is where it should be, you can use your systems package manager to remove the old version.
A Tip When Working With Important Data
There are risks involved in all of the steps above. Make sure that you fully understand where your data is and what will happen to it during the upgrade process.
Every system winds up being slightly different over its lifespan – files wind up being stored in different places for different reasons, different software versions are installed for compatibility – so instructions you find on the internet shouldn’t always be followed verbatim as they may not match your reality.
When initially setting up your servers, you should document the entire installation process, noting what configuration files are changed from their defaults, where they are located, and the versions of all installed software. This will make maintaining the server much easier, especially in 6 months time when you can’t remember exactly why you did something the way you did.
If you’re working with production data, be extra careful – I’d even consider spinning up a whole new PostgreSQL server running the latest version, and then migrate data manually before deprovisioning the old one and storing an image of it for safe keeping until the stability of the new server has been established. Just in case you need to roll back to the old version while placating an angry client on the phone.
