By using AWS re:Post, you agree to the AWS re:Post Terms of Use
AWS re:Postre:Post

How to switch an Aurora Postgres cluster and all databases therein from UTC to a different timezone

0

I am trying to switch an existing cluster from UTC to timezone "America/Los_Angeles" for a legacy service that requires this tz.

  • I tried setting the timezone parameter in a custom parameter group for the cluster.
  • I tried using alter database myDatabase set timezone to 'America/Los_Angeles'
  • I tried rebooting the cluster writer (and only) instance after each change.
  • I created a new cluster just for this testing, a clean slate.

Regardless, when I reconnect I still see 'Etc/UTC' in response to...

show timezone;

I don't need advice on best practices as to why I should leave the tz setting as UTC. Legacy software does not care about best practices.

I really need to understand what will make the changes take effect. Any help would be appreciated.

Topics
Database
Tags
Aurora PostgreSQL
Language
English
jprice
asked a year ago371 views
2 Answers
1
Accepted Answer

Please run the below query to check of the timezone option is being picked up from the parameter group. Check for source column and its value for each database.

select * FROM pg_settings WHERE name = 'TimeZone';

If source = configuration file, which is showing that the timezone value is being reflected from the parameter group itself.

If source = database, which is showing that the timezone value is set at the database level and is overriding the setting from the parameter group.

If source = user , then its set at user level.

Note that, the database level or role/user level or session level settings will override the timezone settings specified in the parameter group. The timezone change made via the parameter group should reflect in all databases unless the setting is explicitly made at the session/database/user level.

You need to make sure that there are no configurations at Database level or role/user level or session level that overrides the timezone settings specified in the parameter group. The order of settings is as follows:

  • Session level configurations.
  • Database level configurations.
  • Role/User level configurations.
  • Use the default settings (parameter group value).

For your reference, below are the commands for setting the timezone value at the user and session levels:

  • Session level postgres=> SET TimeZone="America/Los_Angeles";

  • User level postgres=> ALTER USER <user name> SET TimeZone="America/Los_Angeles";

  • Database level postgres=> ALTER DATABASE <database name> SET TimeZone="America/Los_Angeles";

I hope this helps. If this solution works for you, please mark it as the answer.

rePost-User-6123396
answered a year ago
profile picture
EXPERT
Giovanni Lauria
reviewed 5 months ago
  • jprice
    a year ago

    This was all good advice, nicely presented. It fit the understanding I had of how timezone configuration is supposed to work. But it was not 'working'. It turns out there were two issues.

    1. I was using Java tools to look at timezone, becuase the app in question is based in Java and I wanted to see what the app sees. -- It turns out that the Pg JDBC driver sets the session timezone to the local tz of the client machine, -- This makes it hard to see how the cluster, db, and user settings are working.
    2. In Aurora RDS Pg it was not possible to do the query select * FROM pg_settings WHERE name = 'TimeZone'; I continually got privilege exceptions. I concluded that the Pg superuser role created for RDS is not a real superuser and has no access to this.

    Once I switched to psql for testing, everything became clear. Cluster, Database, User and Connection parameter settings worked exactly as described here.

    So my solution is outside of Aurora RDS. It's to insure that Java code either has the right TZ defined when the JVM starts or to cause connection pools to set timezone as desired before lending a connection.

    Thanks for the response.

1

Hi jprice,

Aurora has cluster-level and instance-level parameter groups. Ensure that the timezone setting is correctly applied at the cluster level and not being overridden by an instance-level parameter group.

Ensure that the cluster isn't using the default parameter group. Default parameter groups are not modifiable.

I hope this helps! If this solution works for you, please accept the answer. Otherwise, do leave a comment, and I'll try to assist you with other ideas.

profile picture
EXPERT
Vitor Castellani
answered a year ago
  • jprice
    a year ago

    Good thoughts.

    1. Yes I have defined custom parameter groups and assigned them at the cluster and instance level.
    2. The cluster level group contains value America/Los_Angeles for the timezone parameter.
    3. The console UI will not allow adding the timezone parameter to the instance level group. It is not present there.

    Yet the setting does not take effect.

  • Vitor Castellani EXPERT
    a year ago

    It's possible that the SHOW timezone; command reflects session-level settings rather than cluster-level settings.

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions

Relevant content