vStats service fails to start in vCenter Server Appliance 7.0 Update 2

Products

VMware vCenter Server

Issue/Introduction

Symptoms:

vStats service is stopped.
VAMI based backup and restore fails.

Reviewing the vstats service logs, you find the following error entries

in /var/log/vmware/vstats/vstats-stderr.log:

<timestamp>   ERROR   mixedmode/mm_endpoint.go:164    Failed to sync DB and cache - Get EndpointSpecs from DB failed  {"vStats": "MVP", "error": "DbpqError: Could not exec/query: pq.ErrorCode=42P01 pq.ErrMsg=pq: relation \"endpoints\" does not exist"}
vstats/dataaccessmgr/mixedmode.(*mixedMode).syncEndpointSpecsFromDBToCache
    /build/mts/release/bora-17694852/BOD/vcenter-vstats/linx64/release/54015e4/build/bora/vpx/vstats/vstats-svc/src/vstats/dataaccessmgr/mixedmode/mm_endpoint.go:164
vstats/dataaccessmgr/mixedmode.(*mixedMode).syncAcqSpecsEndpointsFromDBToCache
    /build/mts/release/bora-17694852/BOD/vcenter-vstats/linx64/release/54015e4/build/bora/vpx/vstats/vstats-svc/src/vstats/dataaccessmgr/mixedmode/mm_init.go:89
vstats/dataaccessmgr/mixedmode.(*mixedMode).syncAllTablesFromDbToCache
    /build/mts/release/bora-17694852/BOD/vcenter-vstats/linx64/release/54015e4/build/bora/vpx/vstats/vstats-svc/src/vstats/dataaccessmgr/mixedmode/mm_init.go:81
vstats/dataaccessmgr/mixedmode.Start

in /var/log/vmware/vstats/vstats-runtime.log.stderr:

Starting service process with pid: 10282.
panic: DbpqError: Could not exec/query: pq.ErrorCode=42P01 pq.ErrMsg=pq: relation "endpoints" does not exist
goroutine 1 [running]:
vstats/dataaccessmgr/mixedmode.(*mixedMode).syncEndpointSpecsFromDBToCache(0xc000998160)
    /build/mts/release/bora-17694852/BOD/vcenter-vstats/linx64/release/54015e4/build/bora/vpx/vstats/vstats-svc/src/vstats/dataaccessmgr/mixedmode/mm_endpoint.go:165 +0xab5
vstats/dataaccessmgr/mixedmode.(*mixedMode).syncAcqSpecsEndpointsFromDBToCache(0xc000998160)
    /build/mts/release/bora-17694852/BOD/vcenter-vstats/linx64/release/54015e4/build/bora/vpx/vstats/vstats-svc/src/vstats/dataaccessmgr/mixedmode/mm_init.go:89 +0x48
vstats/dataaccessmgr/mixedmode.(*mixedMode).syncAllTablesFromDbToCache(0xc000998160)
    /build/mts/release/bora-17694852/BOD/vcenter-vstats/linx64/release/54015e4/build/bora/vpx/vstats/vstats-svc/src/vstats/dataaccessmgr/mixedmode/mm_init.go:81 +0x2b
vstats/dataaccessmgr/mixedmode.Start(0x1a78d80, 0xc0004bad40, 0x18e2840, 0x0, 0x0, 0x0, 0x0, 0xc00003e1c0, 0x6f, 0xc00003e230, ...)
    /build/mts/release/bora-17694852/BOD/vcenter-vstats/linx64/release/54015e4/build/bora/vpx/vstats/vstats-svc/src/vstats/dataaccessmgr/mixedmode/mm_init.go:74 +0x276
vstats/dataaccessmgr.Start(0x1a78d80, 0xc0004bad40, 0x0, 0x0, 0x18e2840, 0x0, 0x0, 0x0, 0x0, 0xc00003e1c0, ...)
    /build/mts/release/bora-17694852/BOD/vcenter-vstats/linx64/release/54015e4/build/bora/vpx/vstats/vstats-svc/src/vstats/dataaccessmgr/implementation.go:59 +0x2b8
main.setUpDataAccessManager(0xc000a92280, 0x0, 0x0, 0x1, 0x0, 0x1, 0x187a32d)
    /build/mts/release/bora-17694852/BOD/vcenter-vstats/linx64/release/54015e4/build/bora/vpx/vstats/vstats-svc/src/vstats/main.go:459 +0x285
main.main()
    /build/mts/release/bora-17694852/BOD/vcenter-vstats/linx64/release/54015e4/build/bora/vpx/vstats/vstats-svc/src/vstats/main.go:218 +0xfeb

or:

Starting service process with pid: 16464.
panic: DbpqError: Could not exec/query: pq.ErrorCode=42703 pq.ErrMsg=pq: column acqs.userid does not exist

Environment

VMware vCenter Server 7.0.2

Cause

This occurs when there is a missing relations/columns in the database required for vstats service.

Resolution

Before attempting to apply the steps below, please make sure to create a new offline snapshot of the vCenter Server Appliance (VCSA) in powered off state. If the VCSA is part of an Enhanced Linked Mode replication configuration, please be aware that you need to create offline snapshots of all ELM replication nodes.

To fix the issue, please follow the steps below:

Open an SSH connection to the affected VCSA
Connect to the vPostgres database using the "statsuser" account:
```
# /opt/vmware/vpostgres/current/bin/psql -d VCDB -U vstatsuser
```

To fix the [relation \"endpoints\" does not exist] error, run the following query:

CREATE TABLE endpoints (
    id BIGSERIAL PRIMARY KEY,
    config_spec JSONB NOT NULL,
    userid TEXT NOT NULL,
    UNIQUE(config_spec, userid)
);

Verify that the table was successfully created:

select * from endpoints;
 id | config_spec | userid
----+-------------+--------
(0 rows)

To fix the [column acqs.userid does not exist] fault, run these queries:

ALTER TABLE acq_spec ADD COLUMN userid TEXT NOT NULL DEFAULT '';
ALTER TABLE acq_spec ADD COLUMN status_reason TEXT;
ALTER TABLE acq_spec RENAME COLUMN vs_status TO status;
ALTER TABLE acq_spec RENAME COLUMN vs_status_ts TO status_ts;

Confirm that the missing column was added to the acq_spec table:

select * from acq_spec;
id | counters | resources | interval_millis | expiration_ts | memo | priority | status | status_ts | created_ts | modified_ts | json_etc | devnull | userid | status_reason
----+----------+-----------+-----------------+---------------+------+----------+--------+-----------+------------+-------------+----------+---------+--------+---------------
(0 rows)

Start the vstats service