How do I change the timezone to UTC in postgres permanently?

Question

I have set the timezone in postgresql.conf as follows :-

- Locale and Formatting -

datestyle = 'iso, mdy'
#intervalstyle = 'postgres'
timezone = 'UTC'
#timezone_abbreviations = 'Default'

But When I do see the timezone in postgres server it defaults to 'Asia/Kolkata'. I have installed the postgres using brew package in MacOs.

Answer 1

setting timezone in postgresql.conf just sets the default for clients without this setting. If you see other value, means client sets it to some local value.

Or alternatively that you did not reload server:

select pg_reload_conf();

To check the setting and what's needed to enable it, you can:

t=# select * from pg_settings where name = 'TimeZone';
-[ RECORD 1 ]---+----------------------------------------------------------------
name            | TimeZone
setting         | UTC
unit            |
category        | Client Connection Defaults / Locale and Formatting
short_desc      | Sets the time zone for displaying and interpreting time stamps.
extra_desc      |
context         | user
vartype         | string
source          | configuration file
min_val         |
max_val         |
enumvals        |
boot_val        | GMT
reset_val       | UTC
sourcefile      | /Users/vao/t96/postgresql.conf
sourceline      | 556
pending_restart | f 

pending_restart false means you don't need to restart postgres, but you still need to reload config after change. Also as you see this setting influence client only. And obviously client can easily override ths default.

to set timezone client side, just run set timezone to 'UTC'.

in order to set this for some user permanently, use alter user uname set timezone and same for setting default for db.

Answer 2

There are many ways to set configuration variables. But most of these setting can be overruled by the client inside its transaction. (Some settings require a server restart or have other restrictions, but not timezone.)

To make absolutely sure, some operations happen with timezone = 'UTC', you could encapsulate that in a server-side function with its own setting, overriding any user-setting. Find a code example in this related answer:

• How does the search_path influence identifier resolution and the "current schema"

But while that fits the wording of your question, I don't think that's what you are looking for. You probably just need to reload after changing postgresql.conf to set the default for new sessions.