localtime ?

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

"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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

"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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

"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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

"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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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:

/messages/by-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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general