Yves' Blog

Upgrade PostgreSQL database (from 9.x to 10) and vCloud Director to 9.5

vcd

If you are still using PostgreSQL 9.x with vCloud Director you will receive a big stop sign if you try to upgrade to vCloud Director 9.5, as this version requires PostgreSQL to be on v10 as a minimum. In this blog post I will walk you step by step on how to upgrade PostgreSQL on a CentOS system, as well as how to apply the vCloud Director 9.5 upgrade. I am pretty sure the process on RedHat is similar, but i had no system to validate at hand.

Prerequisites:

Backup: Please ensure you have not only a VM snapshot, but also a backup of your PostgreSQL database VM(s) as well as your vCloud Director cells before the upgrade.

Test procedure: You should have a validated and approved test procedure for every upgrade so you can be sure your day to day business cases still work afterwards.

Test run: You should run a test run on your test infrastructure, for those unsure: That is the separate infrastructure you have at hand to test new versions! Do not run upgrades untested on a production infrastructure, as the way back is not necessarily easy!

Procedure:

  1. We covered backup already above, but let me repeat it: "Ensure you have a validated backup". 
  2. Read the Release Notes for any known issues that might exist
  3. Shutdown all your active vCloud Director cells.
    It is not a good idea to upgrade the database while active transactions are occurring. The hole procedure can be done below one hour if well planned and tested.
  4. PostgreSQL database update:
    1. Install the 10.x release direct via yum install (Attention this is a parallel install!):

      [[email protected] ~]# sudo yum install https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-centos10-10-2.noarch.rpm
      Loaded plugins: fastestmirror
      pgdg-centos10-10-2.noarch.rpm                                                                                                                                                                                                                   | 4.6 kB  00:00:00     
      Examining /var/tmp/yum-root-uUHT0D/pgdg-centos10-10-2.noarch.rpm: pgdg-centos10-10-2.noarch
      Marking /var/tmp/yum-root-uUHT0D/pgdg-centos10-10-2.noarch.rpm to be installed
      Resolving Dependencies
      --> Running transaction check
      ---> Package pgdg-centos10.noarch 0:10-2 will be installed
      --> Finished Dependency Resolution
      
      Dependencies Resolved
      
      =======================================================================================================================================================================================================================================================================
       Package                                                         Arch                                                     Version                                                   Repository                                                                    Size
      =======================================================================================================================================================================================================================================================================
      Installing:
       pgdg-centos10                                                   noarch                                                   10-2                                                      /pgdg-centos10-10-2.noarch                                                   2.7 k
      
      Transaction Summary
      =======================================================================================================================================================================================================================================================================
      Install  1 Package
      
      Total size: 2.7 k
      Installed size: 2.7 k
      Is this ok [y/d/N]: y
      Downloading packages:
      Running transaction check
      Running transaction test
      Transaction test succeeded
      Running transaction
      Warning: RPMDB altered outside of yum.
        Installing : pgdg-centos10-10-2.noarch                                                                                                                                                                                                                           1/1 
        Verifying  : pgdg-centos10-10-2.noarch                                                                                                                                                                                                                           1/1 
      
      Installed:
        pgdg-centos10.noarch 0:10-2                                                                                                                                                                                                                                          
      
      Complete!
      
      
      
      
      
      
      [[email protected] ~]# sudo yum install postgresql10-server                                                                                                                                                                                                             
      Loaded plugins: fastestmirror
      Determining fastest mirrors
       * base: ftp.plusline.net
       * extras: mirror.fra10.de.leaseweb.net
       * updates: centos.mirror.iphh.net
      base                                                                                                                                                                                                                                            | 3.6 kB  00:00:00     
      extras                                                                                                                                                                                                                                          | 3.4 kB  00:00:00     
      pgdg10                                                                                                                                                                                                                                          | 4.1 kB  00:00:00     
      pgdg95                                                                                                                                                                                                                                          | 4.1 kB  00:00:00     
      updates                                                                                                                                                                                                                                         | 3.4 kB  00:00:00     
      (1/7): extras/7/x86_64/primary_db                                                                                                                                                                                                               | 156 kB  00:00:00     
      (2/7): pgdg10/7/x86_64/group_gz                                                                                                                                                                                                                 |  245 B  00:00:00     
      (3/7): updates/7/x86_64/primary_db                                                                                                                                                                                                              | 1.3 MB  00:00:00     
      (4/7): base/7/x86_64/group_gz                                                                                                                                                                                                                   | 166 kB  00:00:05     
      (5/7): pgdg95/7/x86_64/primary_db                                                                                                                                                                                                               | 212 kB  00:00:06     
      (6/7): pgdg10/7/x86_64/primary_db                                                                                                                                                                                                               | 183 kB  00:00:06     
      (7/7): base/7/x86_64/primary_db                                                                                                                                                                                                                 | 6.0 MB  00:00:08     
      Resolving Dependencies
      --> Running transaction check
      ---> Package postgresql10-server.x86_64 0:10.6-1PGDG.rhel7 will be installed
      --> Processing Dependency: postgresql10-libs(x86-64) = 10.6-1PGDG.rhel7 for package: postgresql10-server-10.6-1PGDG.rhel7.x86_64
      --> Processing Dependency: postgresql10(x86-64) = 10.6-1PGDG.rhel7 for package: postgresql10-server-10.6-1PGDG.rhel7.x86_64
      --> Running transaction check
      ---> Package postgresql10.x86_64 0:10.6-1PGDG.rhel7 will be installed
      ---> Package postgresql10-libs.x86_64 0:10.6-1PGDG.rhel7 will be installed
      --> Finished Dependency Resolution
      
      Dependencies Resolved
      
      =======================================================================================================================================================================================================================================================================
       Package                                                                Arch                                                      Version                                                              Repository                                                 Size
      =======================================================================================================================================================================================================================================================================
      Installing:
       postgresql10-server                                                    x86_64                                                    10.6-1PGDG.rhel7                                                     pgdg10                                                    4.6 M
      Installing for dependencies:
       postgresql10                                                           x86_64                                                    10.6-1PGDG.rhel7                                                     pgdg10                                                    1.6 M
       postgresql10-libs                                                      x86_64                                                    10.6-1PGDG.rhel7                                                     pgdg10                                                    354 k
      
      Transaction Summary
      =======================================================================================================================================================================================================================================================================
      Install  1 Package (+2 Dependent packages)
      
      Total download size: 6.5 M
      Installed size: 28 M
      Is this ok [y/d/N]: y
      Downloading packages:
      (1/3): postgresql10-libs-10.6-1PGDG.rhel7.x86_64.rpm                                                                                                                                                                                                          | 354 kB  00:00:00     
      (2/3): postgresql10-server-10.6-1PGDG.rhel7.x86_64.rpm                                                                                                                                                                                                        | 4.6 MB  00:00:01     
      (3/3): postgresql10-10.6-1PGDG.rhel7.x86_64.rpm                                                                                                                                                                                                               | 1.6 MB  00:00:07     
      -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      Total                                                                                                                                                                                                                                                903 kB/s | 6.5 MB  00:00:07     
      Running transaction check
      Running transaction test
      Transaction test succeeded
      Running transaction
        Installing : postgresql10-libs-10.6-1PGDG.rhel7.x86_64                                                                                                                                                                                                                         1/3 
        Installing : postgresql10-10.6-1PGDG.rhel7.x86_64                                                                                                                                                                                                                              2/3 
        Installing : postgresql10-server-10.6-1PGDG.rhel7.x86_64                                                                                                                                                                                                                       3/3 
        Verifying  : postgresql10-libs-10.6-1PGDG.rhel7.x86_64                                                                                                                                                                                                                         1/3 
        Verifying  : postgresql10-server-10.6-1PGDG.rhel7.x86_64                                                                                                                                                                                                                       2/3 
        Verifying  : postgresql10-10.6-1PGDG.rhel7.x86_64                                                                                                                                                                                                                              3/3 
      
      Installed:
        postgresql10-server.x86_64 0:10.6-1PGDG.rhel7                                                                                                                                                                                                                                      
      
      Dependency Installed:
        postgresql10.x86_64 0:10.6-1PGDG.rhel7                                                                                                 postgresql10-libs.x86_64 0:10.6-1PGDG.rhel7                                                                                                
      
      Complete!
      
      
      
    2. Stop the existing 9.x and the 10.x database server:

      sudo systemctl stop postgresql-9.5.service && sudo systemctl stop postgresql-10.service
    3. Change into the postgreSQL service user:

      sudo su postgres
    4. Initialize the new v10 database:

      bash-4.2$ cd ~/
      bash-4.2$ /usr/pgsql-10/bin/initdb -D /var/lib/pgsql/10/data/
      The files belonging to this database system will be owned by user "postgres".
      This user must also own the server process.
      
      The database cluster will be initialized with locale "en_US.UTF-8".
      The default database encoding has accordingly been set to "UTF8".
      The default text search configuration will be set to "english".
      
      Data page checksums are disabled.
      
      fixing permissions on existing directory /var/lib/pgsql/10/data ... ok
      creating subdirectories ... ok
      selecting default max_connections ... 100
      selecting default shared_buffers ... 128MB
      selecting dynamic shared memory implementation ... posix
      creating configuration files ... ok
      running bootstrap script ... ok
      performing post-bootstrap initialization ... ok
      syncing data to disk ... ok
      
      WARNING: enabling "trust" authentication for local connections
      You can change this by editing pg_hba.conf or using the option -A, or
      --auth-local and --auth-host, the next time you run initdb.
      
      Success. You can now start the database server using:
      
          /usr/pgsql-10/bin/pg_ctl -D /var/lib/pgsql/10/data/ -l logfile start
      bash-4.2$ /usr/pgsql-10/bin/initdb -D /var/lib/pgsql/10/data/
      The files belonging to this database system will be owned by user "postgres".
      This user must also own the server process.
      
      The database cluster will be initialized with locale "en_US.UTF-8".
      The default database encoding has accordingly been set to "UTF8".
      The default text search configuration will be set to "english".
      
      Data page checksums are disabled.
      
      fixing permissions on existing directory /var/lib/pgsql/10/data ... ok
      creating subdirectories ... ok
      selecting default max_connections ... 100
      selecting default shared_buffers ... 128MB
      selecting dynamic shared memory implementation ... posix
      creating configuration files ... ok
      running bootstrap script ... ok
      performing post-bootstrap initialization ... ok
      syncing data to disk ... ok
      
      WARNING: enabling "trust" authentication for local connections
      You can change this by editing pg_hba.conf or using the option -A, or
      --auth-local and --auth-host, the next time you run initdb.
      
      Success. You can now start the database server using:
      
          /usr/pgsql-10/bin/pg_ctl -D /var/lib/pgsql/10/data/ -l logfile start
    5. Upgrade the existing database into the new database:

      bash-4.2$ /usr/pgsql-10/bin/pg_upgrade --old-datadir /var/lib/pgsql/9.5/data/ --new-datadir /var/lib/pgsql/10/data/ --old-bindir /usr/pgsql-9.5/bin/ --new-bindir /usr/pgsql-10/bin/
      Performing Consistency Checks
      -----------------------------
      Checking cluster versions                                   ok
      Checking database user is the install user                  ok
      Checking database connection settings                       ok
      Checking for prepared transactions                          ok
      Checking for reg* data types in user tables                 ok
      Checking for contrib/isn with bigint-passing mismatch       ok
      Checking for invalid "unknown" user columns                 ok
      Checking for roles starting with "pg_"                      ok
      Creating dump of global objects                             ok
      Creating dump of database schemas
                                                                  ok
      Checking for presence of required libraries                 ok
      Checking database user is the install user                  ok
      Checking for prepared transactions                          ok
      
      If pg_upgrade fails after this point, you must re-initdb the
      new cluster before continuing.
      
      Performing Upgrade
      ------------------
      Analyzing all rows in the new cluster                       ok
      Freezing all rows in the new cluster                        ok
      Deleting files from new pg_xact                             ok
      Copying old pg_clog to new server                           ok
      Setting next transaction ID and epoch for new cluster       ok
      Deleting files from new pg_multixact/offsets                ok
      Copying old pg_multixact/offsets to new server              ok
      Deleting files from new pg_multixact/members                ok
      Copying old pg_multixact/members to new server              ok
      Setting next multixact ID and offset for new cluster        ok
      Resetting WAL archives                                      ok
      Setting frozenxid and minmxid counters in new cluster       ok
      Restoring global objects in the new cluster                 ok
      Restoring database schemas in the new cluster
                                                                  ok
      Copying user relation files
                                                                  ok
      Setting next OID for new cluster                            ok
      Sync data directory to disk                                 ok
      Creating script to analyze new cluster                      ok
      Creating script to delete old cluster                       ok
      Checking for hash indexes                                   ok
      
      Upgrade Complete
      ----------------
      Optimizer statistics are not transferred by pg_upgrade so,
      once you start the new server, consider running:
          ./analyze_new_cluster.sh
      
      Running this script will delete the old cluster's data files:
          ./delete_old_cluster.sh
      
      
      
    6. Update the new postgresql.conf and pg_hba.conf to match your settings from the old one. Ensure all adjustments you made for v9 are transfered.
    7. Disable the v9.x service, activate v10 service and restart the database:

      [[email protected] ~]# systemctl disable postgresql-9.5.service
      Removed symlink /etc/systemd/system/multi-user.target.wants/postgresql-9.5.service.
      [[email protected] ~]# systemctl enable postgresql-10.service    
      Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql-10.service to /usr/lib/systemd/system/postgresql-10.service.
      [[email protected] ~]# systemctl start postgresql-10.service                                                                                                                                                                                                                               
      
      
      
    8. Cleanup database:

      -bash-4.2$ ./analyze_new_cluster.sh 
      This script will generate minimal optimizer statistics rapidly
      so your system is usable, and then gather statistics twice more
      with increasing accuracy.  When it is done, your system will
      have the default level of optimizer statistics.
      
      If you have used ALTER TABLE to modify the statistics target for
      any tables, you might want to remove them and restore them after
      running this script because they will delay fast statistics generation.
      
      If you would like default statistics as quickly as possible, cancel
      this script and run:
          "/usr/pgsql-10/bin/vacuumdb" --all --analyze-only
      
      vacuumdb: processing database "postgres": Generating minimal optimizer statistics (1 target)
      vacuumdb: processing database "template1": Generating minimal optimizer statistics (1 target)
      vacuumdb: processing database "vcloud": Generating minimal optimizer statistics (1 target)
      vacuumdb: processing database "postgres": Generating medium optimizer statistics (10 targets)
      vacuumdb: processing database "template1": Generating medium optimizer statistics (10 targets)
      vacuumdb: processing database "vcloud": Generating medium optimizer statistics (10 targets)
      vacuumdb: processing database "postgres": Generating default (full) optimizer statistics
      vacuumdb: processing database "template1": Generating default (full) optimizer statistics
      vacuumdb: processing database "vcloud": Generating default (full) optimizer statistics
      
      Done
      -bash-4.2$ ./delete_old_cluster.sh 
      
      
      
    9. Uninstall old PostgreSQL server:

      [[email protected]st-01 ~]# sudo yum remove postgresql95-server
      Loaded plugins: fastestmirror
      Resolving Dependencies
      --> Running transaction check
      ---> Package postgresql95-server.x86_64 0:9.5.14-1PGDG.rhel7 will be erased
      --> Finished Dependency Resolution
      
      Dependencies Resolved
      
      ==========================================================================================================================================================================================================================================================================================
       Package                                                                    Arch                                                          Version                                                                    Repository                                                      Size
      ==========================================================================================================================================================================================================================================================================================
      Removing:
       postgresql95-server                                                        x86_64                                                        9.5.14-1PGDG.rhel7                                                         @pgdg95                                                         17 M
      
      Transaction Summary
      ==========================================================================================================================================================================================================================================================================================
      Remove  1 Package
      
      Installed size: 17 M
      Is this ok [y/N]: y
      Downloading packages:
      Running transaction check
      Running transaction test
      Transaction test succeeded
      Running transaction
        Erasing    : postgresql95-server-9.5.14-1PGDG.rhel7.x86_64                                                                                                                                                                                                                          1/1 
      warning: file /var/lib/pgsql/9.5/data: remove failed: No such file or directory
        Verifying  : postgresql95-server-9.5.14-1PGDG.rhel7.x86_64                                                                                                                                                                                                                          1/1 
      
      Removed:
        postgresql95-server.x86_64 0:9.5.14-1PGDG.rhel7                                                                                                                                                                                                                                         
      
      Complete!
      [[email protected] ~]# sydo yum remove pgdg-centos95                                                                                                                                                                                                                                       
      -bash: sydo: command not found
      [[email protected] ~]# sudo yum remove pgdg-centos95 
      Loaded plugins: fastestmirror
      Resolving Dependencies
      --> Running transaction check
      ---> Package pgdg-centos95.noarch 0:9.5-2 will be erased
      --> Finished Dependency Resolution
      
      Dependencies Resolved
      
      ==========================================================================================================================================================================================================================================================================================
       Package                                                                  Arch                                                              Version                                                            Repository                                                            Size
      ==========================================================================================================================================================================================================================================================================================
      Removing:
       pgdg-centos95                                                            noarch                                                            9.5-2                                                              installed                                                            2.2 k
      
      Transaction Summary
      ==========================================================================================================================================================================================================================================================================================
      Remove  1 Package
      
      Installed size: 2.2 k
      Is this ok [y/N]: y
      Downloading packages:
      Running transaction check
      Running transaction test
      Transaction test succeeded
      Running transaction
        Erasing    : pgdg-centos95-9.5-2.noarch                                                                                                                                                                                                                                             1/1 
        Verifying  : pgdg-centos95-9.5-2.noarch                                                                                                                                                                                                                                             1/1 
      
      Removed:
        pgdg-centos95.noarch 0:9.5-2                                                                                                                                                                                                                                                            
      
      Complete!
      
      
  5. Upgrade vCloud Director to 9.5 on the vCloud Director cells
    1. Upload binary to each cell
    2. Make binary file executable:

      [[email protected] ~]# chmod a+x vmware-vcloud-director-distribution-9.5.0-11038216.bin                                                                                                                                                                                                    
    3. Execute binary on first cell:

      [[email protected] ~]# ./vmware-vcloud-director-distribution-9.5.0-11038216.bin 
      Checking free disk space...done
      Checking for a supported Linux distribution...Detected CentOS7 system
      done
      Checking for necessary RPM prerequisites...done
      Extracting VMware vCloud Director. Please wait, this could take a few minutes...
      vmware-vcloud-director-23.2018.11.27-11038232.x86_64.rpm
      vmware-vcloud-director-rhel-23.2018.11.27-11038232.x86_64.rpm
      vmware-vcloud-director-h5ui-23.2018.11.27-11038232.x86_64.rpm
      vmware-phonehome-1.0.0-9490868.noarch.rpm
      done
      Verifying RPM signatures...done
      
      An older version of VMware vCloud Director has been detected and will be
      upgraded to 9.5.0.
      
      If you choose to proceed, the installer will stop the vmware-vcd service,
      back up any configuration files from the previous release and migrate the
      product configuration as necessary.
      
      Would you like to upgrade now? (y/n)? y
      Upgrading VMware vCloud Director...
      
      Waiting indefinitely for all active jobs on this cell to complete, if you
      would like to limit how long this process will wait you can cancel this at
      any time via CTRL+C and re-run providing the --abort-tasks-after-minutes
      flag indicating the maximum number of minutes to wait for jobs to complete.
      
      Successfully entered maintenance mode.
      Stopping vmware-vcd-watchdog:                              [  OK  ]
      Stopping vmware-vcd-cell:                                  [  OK  ]
      Installing the VMware vCloud Director 9.5.0 RPM...
      warning: vmware-vcloud-director-23.2018.11.27-11038232.x86_64.rpm: Header V3 RSA/SHA1 Signature, key ID 66fd4949: NOKEY
      Preparing...                          ################################# [100%]
      Updating / installing...
         1:vmware-vcloud-director-rhel-23.20################################# [ 13%]
         2:vmware-vcloud-director-23.2018.11warning: /opt/vmware/vcloud-director/etc/global.properties created as /opt/vmware/vcloud-director/etc/global.properties.rpmnew
      ################################# [ 25%]
         3:vmware-vcloud-director-h5ui-23.20################################# [ 38%]
         4:vmware-phonehome-1.0.0-9490868   ################################# [ 50%]
      Cleaning up / removing...
         5:vmware-phonehome-1.0.0-7772955   ################################# [ 63%]
         6:vmware-vcloud-director-h5ui-22.20################################# [ 75%]
         7:vmware-vcloud-director-22.2018.06################################# [ 88%]
      Update completed.
         8:vmware-vcloud-director-rhel-22.20################################# [100%]
      done
      No DSA certificates found; disabling DSA ciphers for SSL/TLS connections. See KB 2056026 for details
      
      
      Upgrade installation complete.
      Next steps:
      
      You will need to upgrade the database schema before starting the
      vmware-vcd service.  The product upgrade tool should be run only once per
      vCloud Director group. The tool may be run with the following command:
      /opt/vmware/vcloud-director/bin/upgrade
    4. Upgrade vCloud Director database (only necessary on one of the cells):

      [[email protected] ~]# /opt/vmware/vcloud-director/bin/upgrade 
      Welcome to the vCloud Director upgrade utility
      
      Verify that you have a valid license key to use the version of the
      vCloud Director software to which you are upgrading.
      
      This utility will apply several updates to the database. Please
      ensure you have created a backup of your database prior to continuing.
      
      
      Do you wish to upgrade the product now? [Y/N] Y
      Examining database at URL: jdbc:postgresql://10.200.117.51:5432/vcloud?socketTimeout=90
      Disabled Host Found: Identifier: "72ea951e-f11e-484c-9c9e-80e3a283d7f3", Name: esx-203.ger.cdip.net
      Found one or more disabled ESX/ESXi hosts, upgrading will automatically enable all disabled hosts after the upgrade. Do you wish to continue with upgrading the database? [Y/N]  Y
      It may not be safe to perform the upgrade on this cell. Name: "vcd-mgmt-01.ger.cdip.net", IP Address: 10.200.117.51, Identifier: 42063d34-f7cc-426a-8914-d856d8b0dfa3
      The upgrade may not be safe for all cells - some cells may attempt to access tables while the schema is being transformed. Do you wish to upgrade the database anyway? [Y/N]  Y
      The next step in the upgrade process will change the vCloud Director database schema.
      Backup your database now using the tools provided by your database vendor.
      Enter [Y] after the backup is complete. Y
      Running 5 upgrade tasks
      Executing upgrade task:
      Successfully ran upgrade task
      Executing upgrade task:
      Successfully ran upgrade task
      Executing upgrade task:
      Successfully ran upgrade task
      Executing upgrade task:
      .......\Successfully ran upgrade task
      Executing upgrade task:
      ...............[15]
      Successfully ran upgrade task
      Database upgrade complete
      Upgrade complete
      
      Would you like to start the vCloud Director service now? If you choose not
      to start it now, you can manually start it at any time using this command:
      service vmware-vcd start
      
      Start it now? [y/n] y
      
      Starting vmware-vcd-watchdog:                              [  OK  ]
      Starting vmware-vcd-cell                                   [  OK  ]
      
      
      
      
  6. Test your newly updated vCloud Director instance.