Hey Barman Users! Upgrading to PG15 using pg_upgrade??

Read this before, if you’re using Barman

Introduction:

If you are using a Barman setup for backups in PostgreSQL and encounter errors while performing an upgrade from a lower version to PostgreSQL 15, there are several potential reasons and solutions to consider.

Here are one of the most common issue and troubleshooting steps with an example:

We’ll explain the missing function `pg_stop_backup` and ACL that comes with pgBarman installation. Which causes the pg_upgrade to fail.

Normally, during backup operations, Barman uses PostgreSQL native functions pg_start_backup and pg_stop_backup for concurrent backup.This is the recommended way of taking backups for PostgreSQL 9.6 and above (though note the functions have been renamed to pg_backup_start and pg_backup_stop in the PostgreSQL 15 beta).

pgBarman is a popular backup and recovery tool for PostgreSQL databases. It integrates with the PostgreSQL streaming replication feature and provides additional functionality for managing backups. During a PostgreSQL upgrade process, it is common to perform a backup using pgBarman before proceeding with the upgrade. This ensures that a reliable restore point is available in case any issues arise during the upgrade.

When using pgBarman, the backup process involves two steps: starting the backup and stopping the backup. 

The pg_start_backup function is used to initiate the backup, and the pg_stop_backup function is used to finalize it. 

The pg_stop_backup function is crucial as it performs necessary operations to ensure data consistency and completeness in the backup.

Example:

In this case I have set up one primary node and one standby node running on PostgreSQL 11 version with streaming replication and also a barman setup with another node.

Performing upgradation pg 11 to pg 15 version using following command:

postgres@sudheer-vm:$/usr/lib/postgresql/15/bin/pg_upgrade \ 
     --old-bindir /usr/lib/postgresql/11/bin/ \
     --new-bindir /usr/lib/postgresql/15/bin/ \
     --old-datadir /var/lib/postgresql/11/main \
     --new-datadir /var/lib/postgresql/15/main \
     --old-options '-c config_file=/etc/postgresql/11/main/postgresql.conf' \ 
     --new-options '-c config_file=/etc/postgresql/15/main/postgresql.conf' \ 
     --link \
     -–check

If we encounter a success message similar to “Clusters are compatible,” it indicates that the clusters being referenced or compared are compatible with each other. Now that we have verified that the clusters are compatible, we can proceed with the pg_upgrade command and exclude the “–check” option.

postgres@sudheer-vm:$/usr/lib/postgresql/15/bin/pg_upgrade \ 
     --old-bindir /usr/lib/postgresql/11/bin/ \
     --new-bindir /usr/lib/postgresql/15/bin/ \
     --old-datadir /var/lib/postgresql/11/main \
     --new-datadir /var/lib/postgresql/15/main \
     --old-options '-c config_file=/etc/postgresql/11/main/postgresql.conf' \ 
     --new-options '-c config_file=/etc/postgresql/15/main/postgresql.conf' \ 
     --link

You may encounter an error similar to the one below.   

*failure*
Consult the last few lines of "/var/lib/postgresql/15/main/pg_upgrade_output.d/20230719T234750.599/log/pg_upgrade_dump_13190.log" for
the probable cause of the failure.
Failure, exiting

We can identify the error in the as displayed in the following screenshot

postgres@sudheer-vm:$ vi /var/lib/postgresql/15/main/pg_upgrade_output.d/20230719T234750.599/log/pg_upgrade_dump_13190.log

We can identify the error in the pg_upgrade error log, as displayed in the following screenshot.

Resolution:

Step1 : We need to  set up the replication from Primary to the Standby

Step2 : Install the PostgreSQL 15 version in the standby server

Step3 : Stop the replication between Primary and Standby 

Step4 : Promote the standby server PostgreSQL 11

Step5 : Drop the user BARMAN in PostgreSQL 11 with previous privillages

Step6 : Stop both the clusters PostgreSQL 11 and PostgreSQL 15

Now we can perform the pg_upgrade command with the –check option to test whether the clusters are compatible or not. For command please refer above pg_upgrade command .

If we encounter a success message similar to “Clusters are compatible,” it indicates that the clusters being referenced or compared are compatible with each other.

Now that we have verified that the clusters are compatible, we can proceed with the pg_upgrade command and exclude the “–check” option .

The upgrade process is completed, and now we need to collect all the statistics of the old cluster data files using the following command. Before running this command, make sure to start the new cluster pg15.

postgres@sudheer-vm:$ systemctl start postgresql@15-main.service

postgres@sudheer-vm:/usr/lib/postgresql/15$ /usr/lib/postgresql/15/bin/vacuumdb -U postgres --all --analyze-in-stages

As displayed in the following screenshot

Create BARMAN user as following

postgres@sudheer-vm:/usr/lib/postgresql/15$ createuser -P barman
Enter password for new role:
Enter it again:

login to psql and grant all the privileges on the required functions to barman user as following

postgres@sudheer-vm:/usr/lib/postgresql/15$ psql
psql (15.3 (Ubuntu 15.3-1.pgdg22.04+1))
postgres=# GRANT EXECUTE ON FUNCTION pg_backup_start(text, boolean) to barman;
GRANT EXECUTE ON FUNCTION pg_backup_stop(boolean) to barman;
GRANT EXECUTE ON FUNCTION pg_switch_wal() to barman;
GRANT EXECUTE ON FUNCTION pg_create_restore_point(text) to barman;
GRANT pg_read_all_settings TO barman;
GRANT pg_read_all_stats TO barman;

As displayed in the following screenshot :       

Now we can check all the data files available in the upgraded version cluster.

Conclusion:

pg_start_backup (and pg_backup_start in PostgreSQL 15 ): This function is used to start a base backup in PostgreSQL. When invoked, it puts the database into backup mode, allowing backup tools like Barman to take a consistent backup of the database files while the server continues to handle normal operations. Any changes made during the backup process are recorded separately so that the backup is transitionally consistent.

pg_stop_backup (and pg_backup_stop in PostgreSQL 15 ): This function is used to finish the base backup started with pg_start_backup. Once called, it takes the database out of backup mode and completes the backup process.

Using these functions in Barman ensures that the backups are taken in a safe and concurrent manner without causing disruptions to the production PostgreSQL database.