PostgreSQL Upgrade - Fedora 43

NOTE: Fedora 42 shipped PostgreSQL 16 and Fedora 43 jumps to PostgreSQL 18.
postgresql-upgrade only provides PG17 binaries, so the PG16 toolchain needed for pg_upgrade is not present. 

Until this is fixed in Fedora, see Bug 2411778 for the workaround.

Fedora 43 will upgrade your PostgreSQL database to 18.0-1.

Since this is a major upgrade it will require that you upgrade your database.

Here is how to do it:

1. Install the upgrade utilities:  sudo dnf5 install postgresql-upgrade 
2. Become user postgres:  sudo su - postgres
1. Rename the data directory:  mv /var/lib/pgsql/data/ /var/lib/pgsql/data_16/
3. As user root, initialize your 18.0 database:  sudo postgresql-setup --initdb --unit postgresql 
4. As user postgres, Copy your pg_hba.conf:  cp /var/lib/pgsql/data_16/pg_hba.conf /var/lib/pgsql/data/pg_hba.conf
5. To avoid any potential password issues, temporarily change "md5" to "trust" in both pg_hba.conf files
6. Run the upgrade process:  pg_upgrade -b /usr/lib64/pgsql/postgresql-16/bin/ -B /usr/bin/ -d data_16/ -D data
7. Change "trust" to md5" if you changed in step 6 
   
8. Enable your postgresql.service:  sudo systemctl enable postgresql.service
9. Start your postgresql.service:  sudo systemctl start postgresql.service
10. Migration from 16 to 18 will cause a collation version mismatch, to fix sudo su - postgres and: 
1. psql -U postgres -d postgres -c "REINDEX DATABASE CONCURRENTLY postgres;" 
2. psql -U postgres -d postgres -c "ALTER DATABASE postgres REFRESH COLLATION VERSION;" 
11.  Now fix for each of your databases:
1.  psql -U postgres -d database_name -c "REINDEX DATABASE CONCURRENTLY database_name;" 
2. psql -U postgres -d database_name -c "ALTER DATABASE database_name REFRESH COLLATION VERSION;" 
12. Run as suggested: /usr/bin/vacuumdb --all --analyze-in-stages --missing-stats-only
13. Run as suggested:  /usr/bin/vacuumdb --all --analyze-only
    
14. Run as suggested: ./delete_old_cluster.sh script

Note:  When running commands as the postgres user, it is assumed you are running them from the postgres user $home directory.  This will be your location after you enter the command in step 2.  If you for some reason change to another directory from this userid, just remember to enter:  cd $home before entering any postgresql commands - otherwise you may not be able to execute the desired command, or may receive a permission error.  In steps 4, 9 and 10 you must issue the commands as root.  If you don't have sudoers setup for the postgres user, it will fail.  In this case just issue the "su" command to enter root without "sudo".  Remember to issue "exit" to return to the user "postgres".