Support #7555
closedUpgrade PostgreSQL to v14
Added by Radko Krkoš over 2 years ago. Updated over 2 years ago.
0%
Description
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.
Updated by Radko Krkoš over 2 years ago
- To be discussed changed from No to Yes
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).
Updated by Radko Krkoš over 2 years ago
- To be discussed changed from Yes to No
mentat-hub
, with few complications. The takeaway:
- The plan is prepared to be run as general user, with
sudo -i
, most of thesudo@s 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 theVACUUM@ing 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 Krkoš.
Updated by Radko Krkoš over 2 years ago
- Target version changed from Backlog to 2.9
Migration to PostgreSQL v14 is part of Mentat v2.9.