PostgreSQL 16 Upgrade#
Please read carefully the whole section and the instructions for your Operating System.
Warning
The upgrade to PostgreSQL 16 is mandatory if you plan to upgrade Ubuntu 20.04 to 22.04, because in the newest release the support for PostgreSQL 12 has been dropped.
The upgrade to PostreSQL requires to execute a couple of CLI commands and should require little downtime. However, you should take into account that the time to migrate is proportional to all the data stored, so you should consider PostgreSQL to be offline for the time needed to carry out the migration.
The procedure differs slightly between Ubuntu 20.04, Ubuntu 22.04 (in the installation of the repository), while in RHEL 8 it is quite different. For this reason, we separate the directions of RHEL from Ubuntu.
In case after the upgrade you find in Postgres’ log files some error messages like the following one, please check the directions for a fix:
2024-03-19 12:28:14.209 UTC [909825] HINT: Rebuild all objects in this database that use the default collation and run ALTER DATABASE activesync REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.
2024-03-19 12:28:19.669 UTC [909915] WARNING: database "abq" has a collation version mismatch
Ubuntu 20.04 and 22.04#
The commands in this section must be executed as the root
user
and, except for the Repository Configuration, are the same on both
versions of Ubuntu.
Repository Configuration
The following two commands work on both versions of Ubuntu, but the second one is deprecated in Ubuntu 22.04 and will raise a warning. Hence, to make sure the process is flawless, refer to the Ubuntu 22.04 tab for the command if you are installing on that version.
# sh -c 'echo "deb https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
# wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
# sh -c 'echo "deb https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
# wget -O- "https://www.postgresql.org/media/keys/ACCC4CF8.asc" | \
gpg --dearmor | sudo tee /usr/share/keyrings/postgres.gpg > \
/dev/null
# chmod 644 /usr/share/keyrings/postgres.gpg
# sed -i 's/deb/deb [signed-by=\/usr\/share\/keyrings\/postgres.gpg] /' /etc/apt/sources.list.d/pgdg.list
Install Packages
# apt update
# apt -y install postgresql-16
Prepare for migration
In this step some checks and preparatory tasks are executed.
-
Check installed PG clusters and version
# pg_lsclusters
-
Stop the running service
# systemctl stop postgresql
-
Rename the clean 16 DB
# pg_renamecluster 16 main main_pristine
Upgrade and restart service
-
Upgrade the old DB to the new “main” DB
# pg_upgradecluster 12 main
-
Start the service
# systemctl start postgresql
-
Check that everything is working properly
# pg_lsclusters
(Optional) remove unused DB
At this point, all data have been migrated to DB 16, so, if you verify that Carbonio operates properly and all the data are present, you can remove the unused Databases.
Hint
You can keep the old Database, back it up, and remove it at a later point.
-
Drop the old DB
# pg_dropcluster 12 main --stop
-
Drop the pristine DB
#pg_dropcluster 16 main_pristine --stop
RHEL 8 and 9#
Repository Configuration
The PostgreSQL repositories are different in RHEL 8 and RHEL9; the rest of the procedure is exactly the same.
# dnf -y install https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
# dnf -y install https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm
Package installation and checks
-
Install PostgreSQL16 packages
# dnf install postgresql16 postgresql16-server
-
Init the DB
# /usr/pgsql-16/bin/postgresql-16-setup initdb
-
Check that everything is correct and clusters are compatible. This command does not alter any existing data.
# su - postgres -c '/usr/pgsql-16/bin/pg_upgrade -b \ /usr/pgsql-12/bin/ -B /usr/pgsql-16/bin/ -d \ /var/lib/pgsql/12/data/ -D /var/lib/pgsql/16/data/ -c'
Upgrade and migrate
-
Stop the PostgreSQL 12 service
# systemctl stop postgresql-12.service
-
Run the upgrade
# su - postgres -c '/usr/pgsql-16/bin/pg_upgrade -b \ /usr/pgsql-12/bin/ -B /usr/pgsql-16/bin/ -d \ /var/lib/pgsql/12/data/ -D /var/lib/pgsql/16/data/'
-
Migrate the pg_hba.conf file from the previous version
# mv /var/lib/pgsql/16/data/pg_hba.conf /var/lib/pgsql/16/data/pg_hba.conf_orig # cp /var/lib/pgsql/12/data/pg_hba.conf /var/lib/pgsql/16/data/pg_hba.conf # chown postgres:postgres /var/lib/pgsql/16/data/pg_hba.conf
-
Start the new DB
# systemctl start postgresql-16.service
-
Disable the old DB and enable the new one
# systemctl mask postgresql-12.service # systemctl enable postgresql-16.service
(Optional) remove unused DB
At this point, all data have been migrated to DB 16, so, if you verify that Carbonio operates properly and all the data are present, you can remove the unused Databases.
-
Check the new Database
# su - postgres -c '/usr/pgsql-16/bin/vacuumdb --all --analyze-in-stages'
-
Delete the old cluster’s data files
# /var/lib/pgsql/delete_old_cluster.sh
Update pgpool-II on RHEL 8 and RHEL 9
You need to update also the pgpool-II
package so it matches
PostgresQL’s correct version, 16.
-
First, remove the installed package (and repository definition if installed)
# dnf remove pgpool-II pgdg-redhat-repo
-
Install the correct pgpool version
# dnf install https://www.pgpool.net/yum/rpms/4.5/redhat/rhel-8-x86_64/pgpool-II-pg16-4.5.1-1pgdg.rhel8.x86_64.rpm
# dnf install https://www.pgpool.net/yum/rpms/4.5/redhat/rhel-9-x86_64/pgpool-II-pg16-4.5.1-1pgdg.rhel9.x86_64.rpm