Postgres: Docker-compose timezone

[cheatmenot]

version: "3.7"
services:
  postgres:
    image: postgres:11.5
    container_name: postgres
    environment:
      - TZ=Singapore
      - POSTGRES_USER=alona
      - POSTGRES_PASSWORD=alona
      - POSTGRES_DB=alona
    ports:
      - "5432:5432"

I have tried this docker-compose script above but still the timezone is default.
I run the commands SELECT now(); and SHOW timezone;
Please help, thanks!

[wglambert]

TZ uses a specific format that's usually <region>/<country> or <country>/<state>
So make it TZ=Asia/Singapore
https://en.wikipedia.org/wiki/List_of_tz_database_time_zones

$ docker run -d --rm -e TZ=Asia/Singapore --name postgres postgres
40df8a60139036a6ba8bbaa94b5ec9f4acbdbd2c8c30dce828b5286fd565c7c5

$ docker exec -it postgres psql -U postgres                                    
psql (12.0 (Debian 12.0-2.pgdg100+1))                                                        
Type "help" for help.                                                                        

postgres=# show timezone;
    TimeZone                                                                                 
----------------                                                                             
 Asia/Singapore                                                                              
(1 row)

[cheatmenot]

I have changed it on TZ=Asia/Singapore, it is sure did run on your example, but it won't when you use the docker-compose file.

version: "3.7"
services:
  postgres:
    image: postgres:11.5
    container_name: postgres
    environment:
      - TZ=Asia/Singapore
      - POSTGRES_USER=alona
      - POSTGRES_PASSWORD=alona
      - POSTGRES_DB=alona
    ports:
      - "5432:5432"

$ docker-compose -f docker-compose.yml up

$ docker exec -it postgres psql -U alona
alona=# show timezone;
 TimeZone 
----------
 Etc/UTC
(1 row)

[wglambert]

Looks like you had an existing installation in your container, did you remove the previous container or just stop it?

$ docker-compose up -d 
Creating postgres ... done

$ docker-compose logs -f
Attaching to postgres
postgres    | The files belonging to this database system will be owned by user "postgres".
postgres    | This user must also own the server process.
postgres    | 
postgres    | The database cluster will be initialized with locale "en_US.utf8".
postgres    | The default database encoding has accordingly been set to "UTF8".
postgres    | The default text search configuration will be set to "english".
postgres    | 
postgres    | Data page checksums are disabled.
postgres    | 
postgres    | fixing permissions on existing directory /var/lib/postgresql/data ... ok
postgres    | creating subdirectories ... ok
postgres    | selecting default max_connections ... 100
postgres    | selecting default shared_buffers ... 128MB
postgres    | selecting default timezone ... Asia/Singapore
postgres    | selecting dynamic shared memory implementation ... posix
postgres    | creating configuration files ... ok
postgres    | running bootstrap script ... ok
postgres    | performing post-bootstrap initialization ... ok
postgres    | syncing data to disk ... ok
postgres    | 
postgres    | Success. You can now start the database server using:
postgres    | 
postgres    |     pg_ctl -D /var/lib/postgresql/data -l logfile start
postgres    | 
postgres    | 
postgres    | WARNING: enabling "trust" authentication for local connections
postgres    | You can change this by editing pg_hba.conf or using the option -A, or
postgres    | --auth-local and --auth-host, the next time you run initdb.
postgres    | waiting for server to start....2019-11-27 01:08:20.431 +08 [43] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
postgres    | 2019-11-27 01:08:20.700 +08 [44] LOG:  database system was shut down at 2019-11-27 01:08:15 +08
postgres    | 2019-11-27 01:08:21.030 +08 [43] LOG:  database system is ready to accept connections
postgres    |  done
postgres    | server started
postgres    | CREATE DATABASE
postgres    | 
postgres    | 
postgres    | /usr/local/bin/docker-entrypoint.sh: ignoring /docker-entrypoint-initdb.d/*
postgres    | 
postgres    | 2019-11-27 01:08:26.130 +08 [43] LOG:  received fast shutdown request
postgres    | waiting for server to shut down....2019-11-27 01:08:26.180 +08 [43] LOG:  aborting any active transactions
postgres    | 2019-11-27 01:08:26.185 +08 [43] LOG:  background worker "logical replication launcher" (PID 50) exited with exit code 1
postgres    | 2019-11-27 01:08:26.186 +08 [45] LOG:  shutting down
postgres    | 2019-11-27 01:08:26.500 +08 [43] LOG:  database system is shut down
postgres    |  done
postgres    | server stopped
postgres    | 
postgres    | PostgreSQL init process complete; ready for start up.
postgres    | 
postgres    | 2019-11-27 01:08:26.610 +08 [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
postgres    | 2019-11-27 01:08:26.610 +08 [1] LOG:  listening on IPv6 address "::", port 5432
postgres    | 2019-11-27 01:08:26.702 +08 [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
postgres    | 2019-11-27 01:08:26.876 +08 [61] LOG:  database system was shut down at 2019-11-27 01:08:26 +08
postgres    | 2019-11-27 01:08:26.929 +08 [1] LOG:  database system is ready to accept connections
^CERROR: Aborting.

$ docker exec -it postgres psql -U alona
psql (11.5 (Debian 11.5-3.pgdg90+1))
Type "help" for help.

alona=# show timezone;
    TimeZone    
----------------
 Asia/Singapore
(1 row)

[cheatmenot]

yeah I did down and remove my previous container using

docker-compose -f docker-compose.yml down
docker sytem prune --volumes

its really weird, still the timezone is ETC/UTC, anyway thank you, will try on other machines cause currently I'm on Ubuntu Linux

[LuisLuyo]

Hello, @cheatmenot:
Did you set the time zone with your docker-compose file? I had the same problem, but I set the time zone with "ALTER DATABASE postgres_db SET TIMEZONE TO 'America/Lima';"