Обсуждение: localtime ?
On one of my servers, even thopugh everything is in UTC, pg insists on
using timezone -04 by default.
Eg:
,----
| :; date
| Thu Jun 11 22:31:51 UTC 2015
|
| :; psql
| psql (9.4.3, server 9.3.4)
| Type "help" for help.
|
| cloos=# SELECT current_setting('TIMEZONE');
| current_setting
| -----------------
| localtime
| (1 row)
|
| cloos=# select now();
| now
| -------------------------------
| 2015-06-11 18:31:55.597289-04
| (1 row)
`----
But:
,----
| :; grep timezone /etc/postgresql/9.3/main/*
| /etc/postgresql/9.3/main/postgresql.conf:log_timezone = 'UTC'
| /etc/postgresql/9.3/main/postgresql.conf:timezone = 'UTC'
| /etc/postgresql/9.3/main/postgresql.conf:#timezone_abbreviations = 'Default' # Select the set of available time
zone
| /etc/postgresql/9.3/main/postgresql.conf: # share/timezonesets/.
`----
So how do I convince it always to use utc?
(I did that test w/ ~/.psqlrc moved out of the way, but it does nothing
related to tz.)
Thanks.
-JimC
--
James Cloos <cloos@jhcloos.com> OpenPGP: 0x997A9F17ED7DAEA6
James Cloos <cloos@jhcloos.com> writes:
> On one of my servers, even thopugh everything is in UTC, pg insists on
> using timezone -04 by default.
> | cloos=# SELECT current_setting('TIMEZONE');
> | current_setting
> | -----------------
> | localtime
> | (1 row)
AFAIK, that is not a valid value for timezone, unless someone has stuck a
file by that name into your zoneinfo database directory (which I think is
standard practice on some distros though by no means all). If so, it
would mean whatever the file said, which would very likely not be UTC.
> But:
> | :; grep timezone /etc/postgresql/9.3/main/*
> | /etc/postgresql/9.3/main/postgresql.conf:log_timezone = 'UTC'
> | /etc/postgresql/9.3/main/postgresql.conf:timezone = 'UTC'
Evidently that grep has little to do with your actual configuration
source. This would likely be informative as to where "localtime"
is coming from:
select * from pg_settings where name = 'TimeZone';
regards, tom lane
>>>>> "TL" == Tom Lane <tgl@sss.pgh.pa.us> writes: TL> AFAIK, [localtime] is not a valid value for timezone, unless someone TL> has stuck a file by that name into your zoneinfo database directory TL> (which I think is standard practice on some distros though by no TL> means all). If so, it would mean whatever the file said, which TL> would very likely not be UTC. localtime is also the result is there on all of my debians. But every other one defaults to utc. >> But: >> | :; grep timezone /etc/postgresql/9.3/main/* >> | /etc/postgresql/9.3/main/postgresql.conf:log_timezone = 'UTC' >> | /etc/postgresql/9.3/main/postgresql.conf:timezone = 'UTC' TL> Evidently that grep has little to do with your actual configuration TL> source. This would likely be informative as to where "localtime" TL> is coming from: TL> select * from pg_settings where name = 'TimeZone'; That command says sourcefile is /etc/postgresql/9.3/main/postgresql.conf, which is what I grep(1)ed. As it turned out, writing that reminded me that I hadn't run pg_upgradecluster on that box. So I did so. And the 9.4/main cluster defaults to UTC as I prefer. The only difference between the results of that pg_settings select bewteen the two clusters is 9.3 vs 9.4 in the name of the sourcefile. I restarted the 9.3 cluster to try that select, and it still prefers -04. Diff(1)ing the /etc/postgresql/9.[34] directories doesn't show any relevant differences. Just directory names and the port number. I'll leave the old cluster stopped but around for a while in case there are any other queries which might explain the differences. -JimC -- James Cloos <cloos@jhcloos.com> OpenPGP: 0x997A9F17ED7DAEA6
On 06/11/2015 04:48 PM, James Cloos wrote: >>>>>> "TL" == Tom Lane <tgl@sss.pgh.pa.us> writes: > > TL> AFAIK, [localtime] is not a valid value for timezone, unless someone > TL> has stuck a file by that name into your zoneinfo database directory > TL> (which I think is standard practice on some distros though by no > TL> means all). If so, it would mean whatever the file said, which > TL> would very likely not be UTC. > > localtime is also the result is there on all of my debians. But every > other one defaults to utc. > >>> But: >>> | :; grep timezone /etc/postgresql/9.3/main/* >>> | /etc/postgresql/9.3/main/postgresql.conf:log_timezone = 'UTC' >>> | /etc/postgresql/9.3/main/postgresql.conf:timezone = 'UTC' > > TL> Evidently that grep has little to do with your actual configuration > TL> source. This would likely be informative as to where "localtime" > TL> is coming from: > > TL> select * from pg_settings where name = 'TimeZone'; > > That command says sourcefile is /etc/postgresql/9.3/main/postgresql.conf, > which is what I grep(1)ed. What does source from 'select * from pg_settings where name = 'TimeZone';' show? > > As it turned out, writing that reminded me that I hadn't run pg_upgradecluster > on that box. So I did so. And the 9.4/main cluster defaults to UTC as I > prefer. > > The only difference between the results of that pg_settings select > bewteen the two clusters is 9.3 vs 9.4 in the name of the sourcefile. > > I restarted the 9.3 cluster to try that select, and it still prefers -04. > > Diff(1)ing the /etc/postgresql/9.[34] directories doesn't show any > relevant differences. Just directory names and the port number. > > I'll leave the old cluster stopped but around for a while in case there > are any other queries which might explain the differences. > > -JimC > -- Adrian Klaver adrian.klaver@aklaver.com
The full results on that cluster of select * from pg_settings where name = 'TimeZone' is: -[ RECORD 1 ]--------------------------------------------------------------- name | TimeZone setting | localtime unit | category | Client Connection Defaults / Locale and Formatting short_desc | Sets the time zone for displaying and interpreting time stamps. extra_desc | context | user vartype | string source | configuration file min_val | max_val | enumvals | boot_val | GMT reset_val | localtime sourcefile | /etc/postgresql/9.3/main/postgresql.conf sourceline | 508 The 9.3 vs 9.4 in sourcefile is the only difference between the two clusters on that box. -JimC -- James Cloos <cloos@jhcloos.com> OpenPGP: 0x997A9F17ED7DAEA6
On 06/15/2015 10:55 AM, James Cloos wrote: > The full results on that cluster of select * from pg_settings where name = > 'TimeZone' is: > > -[ RECORD 1 ]--------------------------------------------------------------- > name | TimeZone > setting | localtime > unit | > category | Client Connection Defaults / Locale and Formatting > short_desc | Sets the time zone for displaying and interpreting time stamps. > extra_desc | > context | user > vartype | string > source | configuration file > min_val | > max_val | > enumvals | > boot_val | GMT > reset_val | localtime > sourcefile | /etc/postgresql/9.3/main/postgresql.conf > sourceline | 508 So what is at line 508 in /etc/postgresql/9.3/main/postgresql.conf? > > > The 9.3 vs 9.4 in sourcefile is the only difference between the two > clusters on that box. > > -JimC > -- Adrian Klaver adrian.klaver@aklaver.com
>>>>> "AK" == Adrian Klaver <adrian.klaver@aklaver.com> writes: AK> So what is at line 508 in /etc/postgresql/9.3/main/postgresql.conf? timezone = 'localtime' That is the same in the 9.4 conf, where everything defaults to utc as I desire. Those are Debian's default config files. As I wrote the diff between the not working /etc/postgresql/9.3/ snd the working /etc/postgresql/9.4/ is only things like 9.3 vs 9.4 in path names, the port number and the addition in 9.4 of dynamic_shared_memory_type = mmap. Whatever caused this is not in the config files. -JimC -- James Cloos <cloos@jhcloos.com> OpenPGP: 0x997A9F17ED7DAEA6
On 06/15/2015 11:44 AM, James Cloos wrote: >>>>>> "AK" == Adrian Klaver <adrian.klaver@aklaver.com> writes: > > AK> So what is at line 508 in /etc/postgresql/9.3/main/postgresql.conf? > > timezone = 'localtime' > > That is the same in the 9.4 conf, where everything defaults to utc as I > desire. So from the command line on the non-UTC machine what does the below show: ~>date > > Those are Debian's default config files. As I wrote the diff between > the not working /etc/postgresql/9.3/ snd the working /etc/postgresql/9.4/ > is only things like 9.3 vs 9.4 in path names, the port number and the > addition in 9.4 of dynamic_shared_memory_type = mmap. > > Whatever caused this is not in the config files. > > -JimC > -- Adrian Klaver adrian.klaver@aklaver.com
On 15/06/15 20:44, James Cloos wrote: >>>>>> "AK" == Adrian Klaver <adrian.klaver@aklaver.com> writes: > > AK> So what is at line 508 in /etc/postgresql/9.3/main/postgresql.conf? > > timezone = 'localtime' > > That is the same in the 9.4 conf, where everything defaults to utc as I > desire. > > Those are Debian's default config files. As I wrote the diff between > the not working /etc/postgresql/9.3/ snd the working /etc/postgresql/9.4/ > is only things like 9.3 vs 9.4 in path names, the port number and the > addition in 9.4 of dynamic_shared_memory_type = mmap. > > Whatever caused this is not in the config files. > localtime is a timezone but it appears simply to be a copy of the current local timezone file in /etc/localtime. (This is a binary file containing a timezone file.) Is that file different between machines? You can reconfigure the timezone for the machine: sudo dpkg-reconfigure tzdata Oliver
>>>>> "AK" == Adrian Klaver <adrian.klaver@aklaver.com> writes: AK> So from the command line on the non-UTC machine what does the below show: AK> ~>date It is the same machine, just the old 9.3 cluster vs the new 9.4 cluster. date returns: Mon Jun 15 19:37:14 UTC 2015 -JimC -- James Cloos <cloos@jhcloos.com> OpenPGP: 0x997A9F17ED7DAEA6
>>>>> "OE" == Oliver Elphick <olly@lfix.co.uk> writes: OE> localtime is a timezone but it appears simply to be a copy of the OE> current local timezone file in /etc/localtime. (This is a binary file OE> containing a timezone file.) OE> Is that file different between machines? It is 9.3 vs 9.4 on the same box. OE> You can reconfigure the timezone for the machine: OE> sudo dpkg-reconfigure tzdata And /etc/localtime is a copy of /usr/share/zoneinfo/Zulu. -JimC -- James Cloos <cloos@jhcloos.com> OpenPGP: 0x997A9F17ED7DAEA6
On 06/15/2015 11:44 AM, James Cloos wrote: >>>>>> "AK" == Adrian Klaver <adrian.klaver@aklaver.com> writes: > > AK> So what is at line 508 in /etc/postgresql/9.3/main/postgresql.conf? > > timezone = 'localtime' > > That is the same in the 9.4 conf, where everything defaults to utc as I > desire. Went back to the beginning of the thread: http://www.postgresql.org/message-id/m3616t3m5d.fsf@carbon.jhcloos.org where you had: " But: ,---- | :; grep timezone /etc/postgresql/9.3/main/* | /etc/postgresql/9.3/main/postgresql.conf:log_timezone = 'UTC' | /etc/postgresql/9.3/main/postgresql.conf:timezone = 'UTC' | /etc/postgresql/9.3/main/postgresql.conf:#timezone_abbreviations = 'Default' # Select the set of available time zone | /etc/postgresql/9.3/main/postgresql.conf: # share/timezonesets/. " So now I am lost:( Also what is the timezone set to in the 9.4 cluster? If nothing else, just change the timezone in the 9.3 cluster to 'UTC'. > > Those are Debian's default config files. As I wrote the diff between > the not working /etc/postgresql/9.3/ snd the working /etc/postgresql/9.4/ > is only things like 9.3 vs 9.4 in path names, the port number and the > addition in 9.4 of dynamic_shared_memory_type = mmap. > > Whatever caused this is not in the config files. > > -JimC > -- Adrian Klaver adrian.klaver@aklaver.com