Follow below steps to resolve the issue:
- Login to VCSA using SSH.
- Change shell to bash if you are on appliance shell.
* List APIs: "help api list"
* List Plugins: "help pi list"
* Launch BASH: "shell"
Command> shell
Shell access is granted to root
root@vcsa1 [ ~ ]#
- Take backup of /usr/lib/vmware-vpx/sql/postgres.sql file before editing.
cp /usr/lib/vmware-vpx/sql/postgres.sql /usr/lib/vmware-vpx/sql/postgres.sql_backup
- Stop VPXD Service.
service-control --stop vmware-vpxd
Note: vCenter Server will be down after stopping VPXD service, stop the service and proceed with the steps if you have downtime.
- Edit /usr/lib/vmware-vpx/sql/postgres.sql
vi /usr/lib/vmware-vpx/sql/postgres.sql
- Search for strings "query:kQueryStatsStatIdNullDevice1Part" , "query:kQueryStatsStatIdNullDevice2", "query:kQueryStatsStatIdNullDevice3" and "query:kQueryStatsStatIdNullDevice4" in the postgres.sql file
7. Connect to Postgres using command:
/opt/vmware/vpostgres/current/bin/psql -d VCDB -U postgres
8. Download the attached scripts and copy to VCSA (/tmp location) using WinScp, follow
Error when uploading files to vCenter Server Appliance using WinSCP if you face
difficulty to connect to VCSA. 9. Change ownership of copied files using below command:
chown vpostgres:users /tmp/sql1
chown vpostgres:users /tmp/sql2
chown vpostgres:users /tmp/sql3 10. Recreate the Stats Rollup Procedures by importing the downloaded scripts.
VCDB=# \i /tmp/sql2
VCDB=# \i /tmp/sql1
VCDB=# \i /tmp/sql3
Example:
VCDB=# \i /tmp/SQL2
CREATE FUNCTION
VCDB=# \i /tmp/SQL1
CREATE FUNCTION
VCDB=# \i /tmp/SQL3
CREATE FUNCTION
11. Start VPXD service and refresh the UI Client.
service-control --start vpxd
Note: It will take some time to populate the historical data post recreating the jobs. You may verify the contents using Postgres query select count( * ) from VPX_HIST_STAT2; This will show the Stats available for Past Week data.