Question 291094 in Debian Question posted in
The oficial documentation can be found here.

Debian – Postgresql config parameter timezone don't change timezone in data base

I change parameter timezone and log_timezone in postgresql.conf by this doc:
https://www.postgresql.org/docs/current/datatype-datetime.html

From documenttaion: The TimeZone configuration parameter can be set in the file postgresql.conf

timezone = 'Europe/Moscow'
log_timezone = 'Europe/Moscow'

File postgresql.conf i could changed in docker.
But parameter timezone don’t change in database by this sql:

SELECT * FROM pg_settings where name like '%timezone%' or name = 'TimeZone';

timezone parameter is ‘UTC’ now. Why???
And log_timezone changed! What is it?!

My postgresql is in docker.

postgresql version: PostgreSQL 13.1 (Debian 13.1-1.pgdg100+1)
Thanks.

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    Tin of course. The config was applied on the server. This is my intellij idea client changing timezone purely for himself. I check

       select * from current_timestamp;

    in psql console and pgadmin. It is normal time with my timezone. Thanks everybody


  2. 0

    Note that the parameter timezone could (and should!) be overridden in the database session to set the appropriate session time zone. To see if your parameter value is from postgresql.conf or was set somewhere else, query pg_settings:

    SELECT setting, source, sourcefile, sourceline
FROM pg_settings
WHERE name = 'TimeZone';
    Login or Signup to reply.
  3. 0

    Changes in postgresql.conf take effect only if they are there before the database loads it – it’s read on startup. To re-load them, you’ll need to restart or run pg_reload_conf(). This one’s fine but some settings do require a full restart, which you need to check on case-by-case basis.

    Mind that what you’re trying to do sets the cluster-wide default – all connections to all databases on this cluster will assume this time zone, but they are all free to set their own. All defaults take effect at the start of each session, so all ongoing sessions will disregard all changes after that and cannot be forced to change their settings from the outside – they need to either re-connect for changes to apply, or apply them on their own.

    Postgresql.conf and alter system set affect defaults on cluster, alter database set on db, alter [role|user] set on role level. When session starts, it can open by issuing its own set or set_config(), which will override all those settings and there’s no (built-in) way to revoke anyone’s right to set.

    Demo at db<>fiddle:

    select current_database(),current_user,current_setting('timezone');
    current_database current_user current_setting
    postgres postgres UTC

    This changes the default, without affecting current settings:

    alter database postgres set timezone='Europe/Moscow';
select current_database(),current_user,current_setting('timezone');
    current_database current_user current_setting
    postgres postgres UTC

    Same: change to role-level default doesn’t affect ongoing session(s):

    alter role current_user set timezone='Europe/Moscow';
select current_database(),current_user,current_setting('timezone');
    current_database current_user current_setting
    postgres postgres UTC

    Only set command or set_config() function can affect the session, from the inside.

    begin;
set local timezone='Europe/Moscow';
select current_database(),current_user,current_setting('timezone');
commit;
select current_database(),current_user,current_setting('timezone');
    current_database current_user current_setting
    postgres postgres Europe/Moscow

    (set local and set_config('name','value',true) makes the setting go back to what it was before the transaction after it ends)

    current_database current_user current_setting
    postgres postgres UTC

    set session and set_config('name','value',false) keeps the setting after the transaction even if it was issued inside it:

    begin;
set session timezone='Europe/Moscow';
select current_database(),current_user,current_setting('timezone');
commit;
select current_database(),current_user,current_setting('timezone');
    current_database current_user current_setting
    postgres postgres Europe/Moscow
    current_database current_user current_setting
    postgres postgres Europe/Moscow
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search