Updating the Operating System

Updating the server operating system requires reindexing the PostgreSQL databases.

Problem: Updating the operating system can change the version of the PostgreSQL glibc system library, which handles Collation sorting rules. This can lead to duplicate values ​​of unique indexes. Duplicates violate data integrity and can cause errors. For example, the spooler may hang due to repeated errors caused by duplicate logs.

Solution: When upgrading the operating system, update the Collation for PostgreSQL 15 and re-index the database for all versions of PostgreSQL.

When updating the operating system:

1. Stop Staffcop.

sudo staffcop stop
sudo systemctl disable staffcop

2. Update the opearting system.

3. Only for PostgreSQL 15: update the Collation sorting settings.

echo "ALTER DATABASE postgres REFRESH COLLATION VERSION;" | sudo -u postgres psql -d postgres
echo "ALTER DATABASE template1 REFRESH COLLATION VERSION;" | sudo -u postgres psql -d template1
echo "ALTER DATABASE staffcop REFRESH COLLATION VERSION;" | sudo -u postgres psql -d staffcop

4. For all PostgreSQL versions: reindex the database.

echo "REINDEX (VERBOSE) DATABASE postgres" | sudo -u postgres psql -d postgres
echo "REINDEX (VERBOSE) DATABASE template1" | sudo -u postgres psql -d template1
echo "REINDEX (VERBOSE) DATABASE staffcop" | sudo -u postgres psql -d staffcop

5. Start Staffcop.

sudo systemctl enable staffcop
sudo staffcop start

6. All done!