Since vCloud Director 9.7 has dropped support for Postgres SQL9.5 , so i had to upgrade my postgres to 10 , then i have updated my vCloud Director to versions 9.7 , i followed below steps to upgrade the DB , basically at High level steps are as below:

• You need to backup the existing database and data directory.
• Uninstall old version of Postgres SQL.
• Install Postgres10
• Restore Backup

Procedure

• Create database backup using:
  • su – postgres
  • pg_dumpall > /tmp/pg9dbbackup
  • exit
  • 
• Check and Stop the service using
  • #chkconfig
  • #service postgresql-9.5 stop
  • 
• Move current data file as .old to /tmp directory using below command.
  • #mv /var/lib/pgsql/9.5/data/ /tmp/data.old
• Uninstall 9.5 version of Postgres SQL using :
  • yum remove postgresql*
• Install  PostgreSQL v10:
  • #yum -y install https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-centos10-10-2.noarch.rpm
  • #yum -y install postgresql10-server
  • enable service autostart using
    • #chkconfig postgresql-10 on
• Initialise the database
  • service postgresql-10 initdb
  • as suggested by my friend miguel if above step is not working then use this (/usr/pgsql-10/bin/postgresql-10-setup initdb)
• Copy the pg_hba.conf and postgresql.conf from old backed up directory to new directory , this will save some time or you can go ahead and edit existing files with required settings.
  • cp /data.old/pg_hba.conf /var/lib/pgsql/10/data/
  • cp /data.old/postgresql.conf /var/lib/pgsql/10/data/
  • service postgresql-10 start
• Restore backup using below commands:
  • su – postgres
  • psql -d postgres -f /tmp/pg9dbbackup

you can run the reconfigure-database command and that’s it. (change your environment variable accordingly)

This will complete the database upgrade and database migration procedure.