Upgrading PostgreSQL Version

In PostgreSQL, version upgrading is performed in different ways:

• Installing packages with a new version of PostgreSQL. It is suitable only for minor updates. Before performing the update, study the release notes;

• Using the standard pg_dumpall program. It is a reliable method, but there may be a long downtime.

• Using the standard pg_upgrade program. Another quick way to upgrade, but errors may occur.

• Updating via logical replication. This option has minimal downtime but is only suitable for PostgreSQL versions greater than 10.0. Earlier versions require extensions.

The choice depends on which release is used on the server and which version you want to upgrade to.

In this article, we will look at all the above upgrade methods.

Important notes before upgrading

The main thing is to understand the peculiarities of updates between different versions.

The numbers consist of two digits, for example, 10.1. The first digit is the major version number (10). The second digit is the minor release number (1).

Before PostgreSQL 10, the version numbers consisted of three digits. For example, in 9.6.3, 9.6 is the major release number and 3 is the minor version number.

You must understand this difference to choose the right upgrade method.

In minor versions, the data storage format does not change, so there are no compatibility problems. Therefore, the transition from PostgreSQL 10.1 to 10.6 can be carried very easily. To upgrade, turn off the server, replace the executable files, and start the server again.

However, the documentation notes that some releases may require manual changes. Therefore, always read the release notes before upgrading.

In major versions, the data format may change. This makes updating more difficult. You need to either unload the data and upload it again, use the pg_upgrade program, or use logical replication.

We'll talk about all these methods below.

Upgrading within one major version

Let's look at how to update Postgresql Version 14.1 to PostgreSQL 14.3 on an Ubuntu server.

PostgreSQL is available on Ubuntu by default. Start with the command:

sudo apt-get upgrade

Debian and Ubuntu only release one version of PostgreSQL per OS release. For example, Debian Squeeze/6.0 only has PostgreSQL 8.4. If you need a different version of PostgreSQL, use packages from PGDG.

If you want the latest version of PostgreSQL, you must first install the Postgres repository.

Add a repository:

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

Import the signing key:

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

Update the list of packages in the system:

sudo apt-get update

Install the required Postgres version:

sudo apt-get -y install postgresql-14.3

To view the list of installed DBMSs, run:

dpkg --get-selections | grep postgres

Also see the list of clusters:

pg_lsclusters

Stop PostgreSQL before making changes:

sudo service postgresql stop

When Postgres packages are installed, they create a default cluster for you. You should rename the new Postgres cluster so there are no conflicts with the old cluster name when upgrading.

sudo pg_renamecluster 14.3 main main_pristine

Upgrade the old cluster:

sudo pg_upgradecluster 14.1 main

Start the service:

sudo service postgresql start

Check the list of clusters again and make sure the new one is working:

pg_lsclusters

Get rid of the old cluster:

sudo pg_dropcluster 14.1 main

Upgrading via pg_dumpall

If you need to change the major version, use the pg_dumpall program. The essence of this method is to unload data from one main version and then load it into another.

1. Before unloading data, make sure that no changes are being made to the database right now. Otherwise, some of the changes may not be included in the final dump.
2. Get the dump and write it to a file:

pg_dumpall > output_file

3. Stop the server:

pg_ctl stop

4. Change the name of the old directory to eliminate name conflicts:

mv /usr/local/pgsql /usr/local/pgsql.old

The old directory can simply be deleted. But it would be wiser to rename it to leave room for restoring data. Instead of /usr/local/pgsql, specify the path to your directory.

5. Install the new version from source. There is a detailed guide in the documentation. 
6. Form a new cluster:

/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data

7. Transfer all changes to the pg_hba.conf and postgresql.conf files.
8. Start the database server:

/usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data

9. Restore data from backup:

/usr/local/pgsql/bin/psql -d postgres -f output_file

The disadvantage of this method is that the server will be turned off for a long time. To reduce downtime, you can install the new server in a different directory and then run the old and new servers on different ports. To transfer data, use the command:

pg_dumpall -p 5432 | psql -d postgres -p 5433

Instead of 5432 and 5433, specify the port numbers on which you ran the servers.

Upgrading via pg_upgrade

To avoid unloading and uploading data, use the pg_upgrade program. It helps you update faster. The pg_upgrade program creates system tables anew, taking into account changes in the PostgreSQL latest versions. In this case, the old data files are retained. In addition, pg_upgrade verifies that the clusters are compatible.

The pg_upgrade program helps you upgrade from PostgreSQL 8.4.X to the current DBMS release.

Let's look at a general upgrade plan using pg_upgrade.

1. Move the old cluster. This must be done if the directory was not linked to the old release. For example, it is located at /usr/local/pgsql. If you do not rename it, a conflict will occur during the update.

Renaming is performed when the server is turned off with the command:

mv /usr/local/pgsql /usr/local/pgsql.old

2. Build a new version from source. Adjust configure so that the flags are compatible with the old cluster configuration. Before starting the upgrade, pg_upgrade will check the compatibility of the flags.
3. Install new executable files. To place the server in a non-standard directory, use prefix:

make prefix=/usr/local/pgsql.new install

4. Use initdb to initialize a new cluster. Check that the flags are compatible with the flags of the old cluster.
5. Install extension shared object files. If updates are available, pg_upgrade will automatically create a script for their subsequent installation.
6. Transfer full text search files.
7. Configure peer authentication in pg_hba.conf. This needs to be done because pg_upgrade will connect to the old and new hosts multiple times.
8. Check that the old and new servers are stopped:

pg_ctl -D /opt/PostgreSQL/9.6 stop
pg_ctl -D /opt/PostgreSQL/14 stop

9. Run pg_upgrade from the new server. First, call the program in check mode by adding the flag:

pg_upgrade --check

In response, you will receive a list of adjustments that need to be made manually after the upgrade.

10. If you made changes to the pg_hba.conf file, restore it to its previou state. Sometimes, other configurations on the new cluster need to be changed to match the settings of the old cluster.
11. Start the server.

If you are satisfied with the result, delete the old cluster.

For details and features of using pg_upgrade, see the documentation.

Using Logical Replication

Starting from the 10th version of PostgreSQL, we have a method for logical replication of data objects and changes in them. It is based on the use of replication identifiers; usually, they are primary keys.

Logical replication uses the publish-subscribe model. The user creates a snapshot of the published database and copies it to the subscriber. In the PostgreSQL documentation, one of the typical scenarios for using logical replication is precisely the replication between different major versions of PostgreSQL.

The backup server can be located on the same or different host. After synchronization is complete, various options are available. For example, you can make the new server the main one and turn off the old one.

The main advantage of logical replication is minimal downtime. Server interruption, in some cases, is limited to a few seconds.

Conclusion

We looked at universal methods for upgrading PostgreSQL to a new major version and updating within one major version.

Using this guide, you can select the method that works best for your particular case and update your current version of PostgreSQL.