How to change timezone of postgreSQL 9.5 on docker?
Asked Answered
L

4

25

Default timezone is UTC. But I want to change it to GMT+2. I tried as below.

alter database governance set timezone = 'GMT+2';

But it does't work.

How can I manage it?

postgresql version is 9.5. And it run on Docker.

Thanks!

Loutitia answered 6/6, 2017 at 15:37 Comment(0)
T
42

You should set timezone in your docker compose file (TZ and PGTZ are required):

postgres:
    image: postgres
    environment:
        TZ: 'GMT+2'
        PGTZ: 'GMT+2'

Reference: https://github.com/docker-library/postgres/issues/137#issuecomment-217064811

Titanate answered 5/8, 2018 at 19:28 Comment(7)
why does it require two keys to set?Loreanloredana
@DularaMalindu TZ is for GNU/Linux and PGTZ is for PostgreSQL.Titanate
Thanks! Works great for postgres:14.1 image too.Semitics
For anyone with an issue use timezones using: select * from pg_timezone_names; Reference: postgresql.org/docs/14/view-pg-timezone-names.htmlAstrea
What if I want to change the timezone for a running container ? I don't want to recreate itKershner
@Kershner have you tried this query SET TIME ZONE 'GMT+2';? ref: https://mcmap.net/q/143126/-postgres-default-timezoneTitanate
@Titanate thank you very much. It does change the timezone :))Kershner
Y
16

For those who uses TZ and nothing happens

the reason for me was that for the first time when container starts it stores TZ variable in PG config in mapped volume. and after changing docker compose file to another TZ value it stays the same and looks like it doesn't work. you should remove db first and then restart docker-compose

in 2023

if you are using some IDEs PG's client it could require additional info about your time zone

here is additional info of how it could be fixed

enter image description here

Yesseniayester answered 30/1, 2021 at 16:28 Comment(1)
Thanks for this, the logs were stuck on the wrong time zone for meEffort
V
12

To change timezone of you image try this:

docker run -it -e "TZ=GMT+2" postgres:alpine

docker-compose.yml

postgres:
  image: postgres:alpine
  environment: 
    - TZ=GMT+2
Virge answered 6/6, 2017 at 17:34 Comment(2)
My explanation was insufficient. I'm using docker-compose.yml. And I tried to add environment: TZ: GMT+2 But it doesn't work.Loutitia
Can you post the docker-compose.yml?Virge
F
6

You have to specify the timezone in the docker-compose.yml file in this format:

postgres:
    image: postgres:alpine
    environment:
        TZ: "Europe/Madrid"
Fiorenza answered 25/11, 2017 at 23:43 Comment(0)

© 2022 - 2024 — McMap. All rights reserved.