Pivotal Knowledge Base

Follow

How to - Change the timezone on the Pivotal Greenplum(GPDB)

Problem

How to change the timezone on the Pivotal Greenplum(GPDB) and also on the database logs (under $MASTER_DATA_DIRECTORY/pg_log)?

Solution

The two database parameters (GUC's) is used to control the behavior of the time in the database

  • timezone - Reflects the time on the database.
  • log_timezone - Reflects the time on the database logs.

The location (indicated below) lists all the timezone that can be used

/usr/local/greeplum-db/share/postgresql/timezone

Example

For the database side

  • Current time.
flightdata=# select now();
              now
-------------------------------
 2014-11-21 07:16:03.187754-08
(1 row)

flightdata=# set timezone='Europe/Vienna';
SET
flightdata=# select now();
			now
-------------------------------
 2014-11-21 16:16:07.883515+01
(1 row)
  • So if i need to change to a timezone in the continent Africa, under Africa folder at "/usr/local/greeplum-db/share/postgresql/timezone" we have all the timezone available, consider in this example we need to change the time to "Windhoek"
[gpadmin@mdw timezone]$ cd Africa/
[gpadmin@mdw Africa]$ ls -ltr | grep Windhoek
total 220
-rwxr-xr-x 1 gpadmin gpadmin 1556 Aug 26 16:49 Windhoek
  • so to set on the database, we use.
flightdata=# set timezone='Africa/Windhoek' ;
SET

flightdata=# select now();
              now
-------------------------------
 2014-11-21 17:19:57.438173+02
(1 row)
  • To set the changes permanently you need to use
gpconfig --skipvalidation -c timezone -v '<Africa/Windhoek>' -m '<Africa/Windhoek>'

and reload the configuration.

  • Similarly to reflect the timezone for the logs entries, you will need to tweak the parameter "log_timezone".

Comments

  • Avatar
    Joe Manning

    The gpconfig command should be:
    gpconfig --skipvalidation -c timezone -v \'Africa/Windhoek\' -m \'Africa/Windhoek\'

    The '>' and '<' should not be specified and the single quotes should be "escaped" so that the string is quoted in the postgresql.conf file.

    Edited by Joe Manning
Powered by Zendesk