Upgrading PostgreSQL Databases Revisited

Once upon a time I had to upgrade a PostgreSQL database after a Fedora Linux upgrade. Today after I upgraded a Fedora Linux 28 host to a Fedora Linux 31, I found out I had to do it again because PostgreSQL services refuses to start due to database files of older format. I took a slightly simpler approach to upgrade the 9.6 PostgreSQL database to a 11.5 one.

1. Download and install a PostgresSQL 9.6 binary since I have upgraded the host to Fedora 31 and the old binary was gone. For this, instead of building it from source, I took advantage of the Interactive installer from EnterpriseDB. Download the installer and bring it to the Fedora Host:
   • Interactive PostgreSQL Database Installer Download
2. Running the installer. This is as simple as running the installer from the command line as,
   

   
   chmod +x ./postgresql-9.6.15-2-linux-x64.run
   sudo ./postgresql-9.6.15-2-linux-x64.run
   

   
   Following the prompts given by the installer, I installed the binary at /opt/PostgreSQL/9.6/.
3. Disable and stop the 9.6 version of the database service
   

   
    sudo systemctl disable postgresql-9.6.service
    sudo systemctl stop postgresql-9.6.service
   

   
   
4. The 11.5 version of the database service should not be running because this is why we are doing this. However, it does not hurt to make sure it is down:
   

   
   sudo systemctl stop postgresql.service
   

   
   
5. Switch to user postgres
   

   
   sudo su - postgres
   

   
   
6. Initialize a 11.5 database. Assuming we want the database to reside at directory /mnt/data/postgres/data11, we run version 11.5's initdb:
   

   
   mkdir /mnt/data/postgres/data11
   initdb /mnt/data/postgres/data11
   

   
   
7. Start the 11.5 service for the initialized 11.5 database using the installed 11.5's binary:
   

   
   postgres -D /mnt/data/postgres/data11
   

   
   
8. Open another terminal Window, and in the new terminal Window, start the upgrade process using the pg_upgrade . We take a two-step approach.
   1. First check whether everything is OK with the upgrade using the -c option:

      
      sudo pg_upgrade -b /opt/PostgreSQL/9.6/ -B /usr/bin \
        -d /mnt/data/postgres/data96/ -D /mnt/data/postgres/data11 \
        -c
      

      
      
   2. If pg_upgrade does not complain, we go ahead with the upgrade
      

      
      sudo pg_upgrade -b /opt/PostgreSQL/9.6/ -B /usr/bin \
        -d /mnt/data/postgres/data96/ -D /mnt/data/postgres/data11 \
      

      
      
9. Terminate the 11.5 service we ran manually. Just type CTRL-C in the terminal Windows.
10. In the new terminal Window, make sure the user postgres owns the the database files:
    

    
    sudo chown -R postgres:postgres /mnt/data/postgres/data11
    

    
    
11. We are now ready to start the 11.5 database service in the system

    
    sudo systemctl start postgresql.service
    

    
    
12. Finally, we may have to do some additional steps as instructed by the output of pg_upgrade, such as reindexing the database, uninstalling the 9.6 database server binaries, and removing the old database files, e.g.,

    
    sudo su - postgres
    psql < reindex_hash.sql
    exit
    sudo /opt/PostgreSQL/9.6/bin/uninstall-postgresql