Обсуждение: The timezone oddities
Hi, Gentoo Linux, PostgreSQL 9.2.4. I'm trying to find out why postgres uses a specific time zone that I don't expect to be used, and without any success so far. The situation seems strange to me, but I could probably miss something. Here is what I found out. 1. There are no any per-database and no any per-user settings (the \drds command shows both). postgres=# \drds No settings found. 2. There is nothing that changes the timezone from the system one in postgresql.conf. postgres@ip-xx ~ $ grep timezone /db/postgresql/9.2/data/postgresql.conf #log_timezone = unknown # actually, defaults to TZ environment #timezone = unknown # actually, defaults to TZ environment #timezone_abbreviations = 'Default' # Select the set of available time zone 3. The system timezone is PST. postgres@ip-xx ~ $ date Tue Feb 4 10:31:51 PST 2014 4. No timezone is set as a command line parameter for the server. postgres@ip-xx ~ $ grep PGOPTS /etc/conf.d/postgresql-9.2 #PGOPTS="-N 512 -B 1024" postgres@ip-xx ~ $ ps aux | grep 'db/postgres'| grep -v grep postgres 23561 0.0 0.6 18410880 445116 ? S Jan28 0:32 /usr/lib64/postgresql-9.2/bin/postgres -D /db/postgresql/9.2/data -D /db/postgresql/9.2/data --data-directory=/db/postgresql/9.2/data 5. There is no env settings for PG* or TZ that might also affect psql's behaviour. postgres@ip-xx ~ $ env | grep PG postgres@ip-xx ~ $ env | grep TZ 6. Therefore, the time zone is GMT. postgres@ip-xx ~ $ psql psql (9.2.4) Type "help" for help. postgres=# show timezone; TimeZone ---------- GMT (1 row) Do you have any thoughts of where else this GMT could be set from? Thank you in advance. -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 gray.ru@gmail.com
On 02/04/2014 11:23 AM, Sergey Konoplev wrote: > Hi, > > Gentoo Linux, PostgreSQL 9.2.4. > > I'm trying to find out why postgres uses a specific time zone that I > don't expect to be used, and without any success so far. The situation > seems strange to me, but I could probably miss something. > > Here is what I found out. > > 1. There are no any per-database and no any per-user settings (the > \drds command shows both). > > postgres=# \drds > No settings found. > > 2. There is nothing that changes the timezone from the system one in > postgresql.conf. > > postgres@ip-xx ~ $ grep timezone > /db/postgresql/9.2/data/postgresql.conf > #log_timezone = unknown # actually, defaults to TZ environment > #timezone = unknown # actually, defaults to TZ environment > #timezone_abbreviations = 'Default' # Select the set of available time zone > > 3. The system timezone is PST. > > postgres@ip-xx ~ $ date > Tue Feb 4 10:31:51 PST 2014 > > 4. No timezone is set as a command line parameter for the server. > > postgres@ip-xx ~ $ grep PGOPTS /etc/conf.d/postgresql-9.2 > #PGOPTS="-N 512 -B 1024" > > postgres@ip-xx ~ $ ps aux | grep 'db/postgres'| grep -v grep > postgres 23561 0.0 0.6 18410880 445116 ? S Jan28 0:32 > /usr/lib64/postgresql-9.2/bin/postgres -D /db/postgresql/9.2/data -D > /db/postgresql/9.2/data --data-directory=/db/postgresql/9.2/data > > 5. There is no env settings for PG* or TZ that might also affect > psql's behaviour. > > postgres@ip-xx ~ $ env | grep PG > postgres@ip-xx ~ $ env | grep TZ > > 6. Therefore, the time zone is GMT. > > postgres@ip-xx ~ $ psql > psql (9.2.4) > Type "help" for help. > > postgres=# show timezone; > TimeZone > ---------- > GMT > (1 row) > > Do you have any thoughts of where else this GMT could be set from? Does the below apply?: http://www.postgresql.org/message-id/26874.1391127434@sss.pgh.pa.us > > Thank you in advance. > -- Adrian Klaver adrian.klaver@gmail.com
On Tue, Feb 4, 2014 at 11:29 AM, Adrian Klaver <adrian.klaver@gmail.com> wrote: > On 02/04/2014 11:23 AM, Sergey Konoplev wrote: >> Gentoo Linux, PostgreSQL 9.2.4. >> >> I'm trying to find out why postgres uses a specific time zone that I >> don't expect to be used, and without any success so far. The situation >> seems strange to me, but I could probably miss something. [...] > Does the below apply?: > > http://www.postgresql.org/message-id/26874.1391127434@sss.pgh.pa.us > 1. You ran initdb in a different environment than you normally start the server in. No, AFAIK it was always the same server with the same environment. > 2. You blindly copied the 9.1 postgresql.conf into the 9.3 installation, overwriting what initdb had done. If there's no uncommented timezone setting in the .conf file, 9.3 will fall back to GMT, IIRC. But that approach to configuration has a lot of pitfalls besides this one. No, It was initially 9.2, no major version upgrades were performed. > In any case the fix is to set the zone you want in postgresql.conf. I think this will be the solution, but it would be very good if we could find out the reason too. -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 gray.ru@gmail.com
Sergey Konoplev escribió: > Hi, > > Gentoo Linux, PostgreSQL 9.2.4. > > I'm trying to find out why postgres uses a specific time zone that I > don't expect to be used, and without any success so far. The situation > seems strange to me, but I could probably miss something. As far as I know, GMT is the fallback if no timezone is configured. In 9.2 there's no longer a scan at postmaster start for a timezone matching the system's; if you don't have a value set in postgresql.conf by initdb, it will start as GMT. This is a change from 9.1 behavior. -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
On 02/04/2014 01:21 PM, Alvaro Herrera wrote:
Having just read Alvaro's post and knowing I did not manually set the TZ I checked my 9.3 installation. Interesting output.Sergey Konoplev escribió:Hi, Gentoo Linux, PostgreSQL 9.2.4. I'm trying to find out why postgres uses a specific time zone that I don't expect to be used, and without any success so far. The situation seems strange to me, but I could probably miss something.As far as I know, GMT is the fallback if no timezone is configured. In 9.2 there's no longer a scan at postmaster start for a timezone matching the system's; if you don't have a value set in postgresql.conf by initdb, it will start as GMT. This is a change from 9.1 behavior.
toys=# show timezone;Perhaps building from source does make a guess at TZ. I am not residing in the Navaho national territory, but is that just Mountain time?
TimeZone
----------
Navajo
(1 row)
toys=# \!date
Invalid command \!date. Try \? for help.
toys=# \! date
Tue Feb 4 13:26:03 MST 2014
toys=# select * from version();
version
--------------------------------------------------------------------------------------------------------------
PostgreSQL 9.3.1 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-4), 64-bit
(1 row)
On 02/04/2014 12:09 PM, Sergey Konoplev wrote: > On Tue, Feb 4, 2014 at 11:29 AM, Adrian Klaver <adrian.klaver@gmail.com> wrote: >> On 02/04/2014 11:23 AM, Sergey Konoplev wrote: >>> Gentoo Linux, PostgreSQL 9.2.4. >>> >>> I'm trying to find out why postgres uses a specific time zone that I >>> don't expect to be used, and without any success so far. The situation >>> seems strange to me, but I could probably miss something. > [...] >> Does the below apply?: >> >> http://www.postgresql.org/message-id/26874.1391127434@sss.pgh.pa.us > >> 1. You ran initdb in a different environment than you normally start the > server in. > > No, AFAIK it was always the same server with the same environment. > >> 2. You blindly copied the 9.1 postgresql.conf into the 9.3 installation, > overwriting what initdb had done. If there's no uncommented timezone > setting in the .conf file, 9.3 will fall back to GMT, IIRC. But that > approach to configuration has a lot of pitfalls besides this one. > > No, It was initially 9.2, no major version upgrades were performed. > >> In any case the fix is to set the zone you want in postgresql.conf. > > I think this will be the solution, but it would be very good if we > could find out the reason too. So the postgresql.conf is the one created by initdb for this particular installation? If that is the case it would seem that initdb could not determine what the timezone is at init. I do not use Gentoo so I do not know how it works with timezones. All I can do is point you at the below.: https://wiki.gentoo.org/wiki/Localization/HOWTO > -- Adrian Klaver adrian.klaver@gmail.com
On Tue, Feb 4, 2014 at 12:41 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote: > On 02/04/2014 12:09 PM, Sergey Konoplev wrote: > So the postgresql.conf is the one created by initdb for this particular > installation? > > If that is the case it would seem that initdb could not determine what the > timezone is at init. I do not use Gentoo so I do not know how it works with > timezones. All I can do is point you at the below.: > > https://wiki.gentoo.org/wiki/Localization/HOWTO As Avaro mentioned GMT is a fallback for >=9.2, that explains everything. Thanks to everyone for your help. -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 gray.ru@gmail.com
On 02/04/2014 12:31 PM, Rob Sargent wrote: > On 02/04/2014 01:21 PM, Alvaro Herrera wrote: > > Perhaps building from source does make a guess at TZ. I am not residing > in the Navaho national territory, but is that just Mountain time? Yes: http://en.wikipedia.org/wiki/List_of_tz_database_time_zones The reason for specificity is that the Navajo Nation observes DST, while the rest of Arizona does not. > > -- Adrian Klaver adrian.klaver@gmail.com
Adrian Klaver <adrian.klaver@gmail.com> writes: > On 02/04/2014 12:31 PM, Rob Sargent wrote: >> Perhaps building from source does make a guess at TZ. I am not residing >> in the Navaho national territory, but is that just Mountain time? > Yes: > http://en.wikipedia.org/wiki/List_of_tz_database_time_zones > The reason for specificity is that the Navajo Nation observes DST, while > the rest of Arizona does not. Not quite. The zoneinfo database has a number of names for Mountain Time with DST, eg America/Denver. A quick look says that "Navajo" is just an alias for that one, so no probe of the system's timezone behavior is going to be able to tell the difference. IIRC, given two zone names that both match the system's behavior equally well, initdb chooses the shorter one. That heuristic produces good results in many places, but not so much here. Note that this is exactly the same choice of zone you'd have gotten from pre-9.2 if it wasn't told a timezone setting to use. We only moved this examination of the system's behavior from every-postmaster-start to initdb. regards, tom lane
On 02/04/2014 03:44 PM, Tom Lane wrote:
Interesting. I'm cool with it being 'Navaho' as that has a certain nostalgic resonance with me.Adrian Klaver <adrian.klaver@gmail.com> writes:On 02/04/2014 12:31 PM, Rob Sargent wrote:Perhaps building from source does make a guess at TZ. I am not residing in the Navaho national territory, but is that just Mountain time?Yes: http://en.wikipedia.org/wiki/List_of_tz_database_time_zones The reason for specificity is that the Navajo Nation observes DST, while the rest of Arizona does not.Not quite. The zoneinfo database has a number of names for Mountain Time with DST, eg America/Denver. A quick look says that "Navajo" is just an alias for that one, so no probe of the system's timezone behavior is going to be able to tell the difference. IIRC, given two zone names that both match the system's behavior equally well, initdb chooses the shorter one. That heuristic produces good results in many places, but not so much here. Note that this is exactly the same choice of zone you'd have gotten from pre-9.2 if it wasn't told a timezone setting to use. We only moved this examination of the system's behavior from every-postmaster-start to initdb. regards, tom lane