Timestamp Mismatch Alerts in Greenplum Command Center in China Standard Timezone

Products

• VMware Tanzu Greenplum

Issue/Introduction

Symptoms:

The Dashboard's alert panel shows timestamps in the future and mismatches with the same entries on the pg_log.

 

Environment

Cause

There are two time zones with the same abbreviation:

 - CST, China Standard Time (+8:00)

 - CST, Central Standard Time (-6:00)

 

For this issue, the time zone on the OS of a user from China is 'PRC', the abbreviation for which is 'CST'. Therefore, his pg_log reads '2017-xx-xx xx:xx:xx.xxxxxx CST.' When GPCC queries log_alter_now, PostgreSQL converts the string back to timestamptz. This is because PostgreSQL regards 'CST' as -6:00, leading to the result of the query being time-stamped as 14 hours ahead of the expected time.

 

Resolution

Follow the steps below to resolve this issue:

1. Reset the timezone for gppermon and gpmon. GPCC requires them to use the default timezone as that of the OS being utilized:

psql gpperfmon
alter user gpmon reset timezone;
alter database gpperfmon reset timezone;
restart gpcc

2. Change postgresql.conf (Requires a Greenplum restart):

gpconfig -s log_timezone
gpconfig -c log_timezone -v 'Hongkong'
gpconfig -c timezone -v 'PRC'
gpstop
gpstart

Your log file will look like '2017-xx-xx xx:xx:xx.xxxxxx HKT.'

HKT is not as ambiguous as 'CST' and is in the +8:00 timezone. This is a workaround that can be used to resolve the confusion between the two similarly abbreviated timezones.