I have a Patroni cluster with two Ubuntu 22 db server. The timezone of my instances was set to UTC. I have needed to edit them to set the local timezone to Europe/Istanbul. I have made the timezone change on OS-level. When i checked the timezone setting with timedatectl command, i saw local time was correct.

And, I applied the setting one by one to all my databases on my server:

ALTER DATABASE "db_name" SET TIMEZONE TO "Europe/Istanbul";

Also applied the setting:

ALTER SYSTEM SET TimeZone TO 'Europe/Istanbul';

And i edited my patroni config file and entered timezone setting like:

TimeZone: Europe/Istanbul

After that, i restarted my patroni cluster. After restarting my cluster, i saw that some of databases had applied the changes but other ones had not. I checked those databases configuration. The configuration was same with the applied ones.

I still see old timezone setting which was ETC/UTC when I execute sql command below:

show timezone

I am a little bit confused about it. Strange situation. What might cause the situation? Do i miss something?

  • Some clients will automatically set the timezone upon connection, rather than accepting the servers default. So you will have to describe what you used to do show timezone and how it was configured. Also, select * from pg_settings where name ilike 'Timezone' will give more info than show timezone will.
    – jjanes
    Commented Sep 6, 2023 at 13:12
  • I have used show timezone command to check the current TimeZone value in my postgresql settings. I also used this query: select * from pg_settings where name ilike 'Timezone' . There is something to do in my opinion but what should to do, i don't know. Those commands just shows the current value of the setting. The clients also don't set any timezone value.
    – user274715
    Commented Sep 6, 2023 at 15:47
  • If you just dismiss all the suggestions without evaluating them, then you will find that not solution exists because you won't look for one. What did select * from pg_settings where name ilike 'Timezone' show?
    – jjanes
    Commented Sep 6, 2023 at 16:45
  • @jjanes You are right . I have checked the settings from the query you sent. The result set is like this : name: TimeZone setting: Etc/UTC context: user vartype: string source: session I have saw that those databases's source type was session which did not apply timezone setting. The applied database's source type is database. I think this is the reason of difference. I also connected those databases from psql client and queried them. I saw all databases in my Europe/Istanbul timezone. But it is not same while connecting over PgAdmin,
    – user274715
    Commented Sep 6, 2023 at 21:05
  • The correct way to change a parameter value is patrinictl edit-config. Commented Sep 7, 2023 at 0:54


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.