Upgrade PostgreSQL to v14

PostgreSQL v14 again brings various performance improvements (in broader sense). It is successfully deployed in other projects for quite some time, so the upgrade seems feasible. In the meantime, minor version 14.1 was released. It is time to upgrade. The plan was already prepared and should be tested on mentat-alt. Then, the production should be upgraded.

assigned to @radko_krkos

By Radko Krkoš on 2024-12-26T14:07:30

added C-Dev-DB P4 S-New T-Support labels

changed milestone to %Backlog

Successful upgrade was performed on mentat-alt using (roughly, see below) these steps:

PostgreSQL 14 Upgrade
=====================

0. Activate the maintenance mode website
	# First update timestamps of maintenance start and maintenance end:
	$ sudo vim /etc/mentat/apache/maintenance/.htaccess
	# Now bring the Mentat system web interface down and the maintenance site up:
	$ sudo a2enmod substitute
	$ sudo a2dissite site_mentat-ng.conf
	$ sudo a2ensite site_maintenance.conf
	$ sudo systemctl restart apache2

1. Shut down the Mentat's import pipeline and stabilize the DB
	$ sudo systemctl stop warden_filer_cesnet_receiver.service
	$ sudo systemctl disable warden_filer_cesnet_receiver.service
	$ sudo mentat-controller.py --command stop
	$ sudo mentat-controller.py --command disable
	$ sudo systemctl restart postgresql

2. Vacuum the database
	# Typically, there is peer authentication for postgres user in DB
	$ sudo -u postgres vacuumdb -F -j 16 -v -a

3. Stop postgresql
	$ sudo systemctl stop postgresql

4. Install PostgreSQL 14
	$ sudo apt-get update
	$ sudo apt-get install postgresql-14 postgresql-14-ip4r postgresql-server-dev-14 postgresql-client-14

5. Back up the default PostgreSQL v14 configuration file
	# This is used later in step 8.
	$ cp /etc/postgresql/14/main/postgresql.conf ~/postgresql_14_default.conf

6. Migration
	$ sudo pg_lsclusters
	Ver Cluster Port Status Owner    Data directory              Log file
	13  main    5432 online postgres /var/lib/postgresql/13/main /var/log/postgresql/postgresql-13-main.log
	14  main    5433 online postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log

	$ sudo systemctl stop postgresql
	
	$ sudo pg_dropcluster 14 main

	$ sudo pg_lsclusters
	Ver Cluster Port Status Owner    Data directory              Log file
	13  main    5432 down   postgres /var/lib/postgresql/13/main /var/log/postgresql/postgresql-13-main.log

	# This will require *temporarily* setting wal_level to 'logical' (in postgresql.conf) - it is set to 'minimal' if you followed configuration advice from docs
	# Alternatively one can ommit the --link parameter, but that requires free space for a 1:1 copy and of course also takes much longer
	$ sudo sed -i -E 's/^(wal_level\s*=\s*)[a-z]+/\1logical/' /etc/postgresql/13/main/postgresql.conf
	$ sudo pg_upgradecluster --method=upgrade --link 13 main

	$ sudo pg_dropcluster 13 main

7. Remove PostgreSQL 13 and potential leftovers from previous versions
	$ sudo apt-get remove --purge postgresql-13 postgresql-client-13 postgresql-server-dev-13 postgresql-13-ip4r postgresql-13 postgresql-12 postgresql-client-12 postgresql-server-dev-12 postgresql-12-ip4r postgresql-12 postgresql-client-11 postgresql-server-dev-11 postgresql-11-ip4r postgresql-10 postgresql-9.4 postgresql-9.5 postgresql-9.6

8. Update the configuration file
	# Related to #6480. This is the most laborous step, which I have found no way of automating. Also, sometimes the options are just reordered, what complicates the merge process.
	$ sudo vimdiff /etc/postgresql/14/main/postgresql.conf ~/postgresql_14_default.conf

	# Change the setting for wal_level back to minimal if it was changed in step 6.
	$ sudo sed -i -E 's/^(wal_level\s*=\s*)[a-z]+/\1minimal/' /etc/postgresql/14/main/postgresql.conf

9a. Reboot the system
	!OPTIONAL: This is a good time to reboot the machine if desired (kernel update, long runtime). Optionally, just follow with 9b.
	$ sudo reboot

9b. Start PostgreSQL
	!Only if 9a was skipped.
	$ sudo systemctl start postgresql

10. Cleanup & optimization
	# If these steps are to be skipped on DBAs discretion, at least an ANALYZE run is required as the statistics are not carried over during upgrade.
	$ sudo -u postgres vacuumdb -F -j 16 -v -a -z
	$ sudo -u postgres reindexdb -j 16

11. Start Mentat
	$ sudo mentat-controller.py --command enable
	$ sudo mentat-controller.py --command start
	$ sudo systemctl enable warden_filer_cesnet_receiver.service
	$ sudo systemctl start warden_filer_cesnet_receiver.service

12. Deactivate the maintenance mode website
	$ sudo a2dismod substitute
	$ sudo a2dissite site_maintenance.conf
	$ sudo a2ensite site_mentat-ng.conf
	$ sudo systemctl restart apache2

The 9b path was taken (although 9a should have been). In section 10, VACUUM FULL was performed instead of REINDEX. The whole upgrade process, including configuration file merge, took under 90 minutes (including delays to notice long-running steps being done).

By Radko Krkoš on 2022-02-08T08:29:16

Performed on mentat-hub, with few complications. The takeaway:

• The plan is prepared to be run as general user, with sudo -i, most of the sudos are unnecessary and the rest trigger warning regarding not being able to change working directory to 'root'.
• The issue #7121 triggered on reboot with Mentat disabled.
• The (new) issue #7559 was identified.
• VACUUM FULL is fast enough to tolerate during upgrade (it adds less than 10 minutes to the total time), the upgrade is performed once a year and it is the best time window to perform the VACUUMing and the performance benefits are worth it. Step 10 should reflect this. Alternatively, the fast-route using pure ANALYZE should be explicitly described.
• The documentation includes DB upgrade plans since version 10 in one major version steps. The usefulness of these should be reviewed as we actually only support running Mentat on a specific major version of PostgreSQL, not a range. Everyone running current Mentat (v2.8.x) should be running latest PostgreSQL v13.
• The plan to upgrade to PostgreSQL v14 should be documented officially, the honor is bestowed on @radko_krkos.

By Radko Krkoš on 2024-12-30T22:30:48

Merged to devel.

By Radko Krkoš on 2022-03-01T14:49:20

added S-Resolved label and removed S-New label

Migration to PostgreSQL v14 is part of Mentat v2.9.

By Radko Krkoš on 2022-03-01T14:50:26

changed milestone to %2.9

added S-Closed label and removed S-Resolved label

closed