configuring timezone

See the archived thread here:
/messages/by-id/CAEghcWD8DXjroBYCZsdGrx+cHTCbCbW9es2uQ+o7a8NZ61JT=Q@mail.gmail.com

Short version: Sorry, but you're going to need to recompile if you want
that behavior. Here's a diff applied against 9.2.1
http://pastebin.com/5AyaX2RF. I've deployed the patched version a couple
dozen times now and it is working flawlessly.

T.J.

On Wed, Feb 6, 2013 at 1:32 PM, Igor Neyman <ineyman@perceptron.com> wrote:

Terence,

Thanks for quick reply, I read your thread (Dec, 2012) before posting my question.
But, recompile is not an option for me. Was hoping, that something regarding this issue changed since...

Igor Neyman

From: Terence Ferraro [mailto:terencejferraro@gmail.com]
Sent: Wednesday, February 06, 2013 1:45 PM
To: Igor Neyman
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] configuring timezone

See the archived thread here: /messages/by-id/CAEghcWD8DXjroBYCZsdGrx+cHTCbCbW9es2uQ+o7a8NZ61JT=Q@mail.gmail.com

Short version: Sorry, but you're going to need to recompile if you want that behavior. Here's a diff applied against 9.2.1 http://pastebin.com/5AyaX2RF. I've deployed the patched version a couple dozen times now and it is working flawlessly.

T.J.
On Wed, Feb 6, 2013 at 1:32 PM, Igor Neyman <ineyman@perceptron.com<mailto:ineyman@perceptron.com>> wrote:
Timezone configuration parameter (defaulting to system timezone) worked fine for us before upgrading from 8.4. to 9.2.

Now we've got a problem.
9.2 Release Notes says:

* Identify the server time zone during initdb, and set postgresql.conf entries timezone<http://www.postgresql.org/docs/9.2/static/runtime-config-client.html#GUC-TIMEZONE&gt; and log_timezone<http://www.postgresql.org/docs/9.2/static/runtime-config-logging.html#GUC-LOG-TIMEZONE&gt; accordingly (Tom Lane)

This avoids expensive time zone probes during server start.

Question: is there any way to revert back to old behavior so that server will probe system's timezone on startup (default to OS timezone on startup) instead setting it during initdb?
Obviously, without recompiling/rebuilding Postgres.

I'm dealing with the situation, where system is being built in one timezone (could be anywhere around the globe), and then moved to other (not known during system build) location with different timezone.
After relocation, OS timezone will change, but we can't allow user to edit timezone parameter in Postgresql.conf.

Regards,
Igor Neyman

Sorry, but from what I understand the change is permanent. If recompile is
not an option but you're on Windows let me know; I do have binaries
available..

On Wed, Feb 6, 2013 at 2:05 PM, Igor Neyman <ineyman@perceptron.com> wrote:

I am on Windows (both 32 and 64 bit) using 32-bit Postgres.
So, your binaries are for 9.2.1, you aren't planning to go to 9.2.2?

From: Terence Ferraro [mailto:terencejferraro@gmail.com]
Sent: Wednesday, February 06, 2013 3:07 PM
To: Igor Neyman
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] configuring timezone

Sorry, but from what I understand the change is permanent. If recompile is not an option but you're on Windows let me know; I do have binaries available..
On Wed, Feb 6, 2013 at 2:05 PM, Igor Neyman <ineyman@perceptron.com<mailto:ineyman@perceptron.com>> wrote:
Terence,

Thanks for quick reply, I read your thread (Dec, 2012) before posting my question.
But, recompile is not an option for me. Was hoping, that something regarding this issue changed since...

Igor Neyman

From: Terence Ferraro [mailto:terencejferraro@gmail.com<mailto:terencejferraro@gmail.com>]
Sent: Wednesday, February 06, 2013 1:45 PM
To: Igor Neyman
Cc: pgsql-general@postgresql.org<mailto:pgsql-general@postgresql.org>
Subject: Re: [GENERAL] configuring timezone

See the archived thread here: /messages/by-id/CAEghcWD8DXjroBYCZsdGrx+cHTCbCbW9es2uQ+o7a8NZ61JT=Q@mail.gmail.com

Short version: Sorry, but you're going to need to recompile if you want that behavior. Here's a diff applied against 9.2.1 http://pastebin.com/5AyaX2RF. I've deployed the patched version a couple dozen times now and it is working flawlessly.

T.J.
On Wed, Feb 6, 2013 at 1:32 PM, Igor Neyman <ineyman@perceptron.com<mailto:ineyman@perceptron.com>> wrote:
Timezone configuration parameter (defaulting to system timezone) worked fine for us before upgrading from 8.4. to 9.2.

Now we've got a problem.
9.2 Release Notes says:

* Identify the server time zone during initdb, and set postgresql.conf entries timezone<http://www.postgresql.org/docs/9.2/static/runtime-config-client.html#GUC-TIMEZONE&gt; and log_timezone<http://www.postgresql.org/docs/9.2/static/runtime-config-logging.html#GUC-LOG-TIMEZONE&gt; accordingly (Tom Lane)

This avoids expensive time zone probes during server start.

Question: is there any way to revert back to old behavior so that server will probe system's timezone on startup (default to OS timezone on startup) instead setting it during initdb?
Obviously, without recompiling/rebuilding Postgres.

I'm dealing with the situation, where system is being built in one timezone (could be anywhere around the globe), and then moved to other (not known during system build) location with different timezone.
After relocation, OS timezone will change, but we can't allow user to edit timezone parameter in Postgresql.conf.

Regards,
Igor Neyman

On 02/06/2013 10:32 AM, Igor Neyman wrote:

Timezone configuration parameter (defaulting to system timezone) worked
fine for us before upgrading from 8.4. to 9.2.

Now we�ve got a problem.

9.2 Release Notes says:

� Identify the server time zone during initdb, and set postgresql.conf
entries timezone
<http://www.postgresql.org/docs/9.2/static/runtime-config-client.html#GUC-TIMEZONE&gt;
and log_timezone
<http://www.postgresql.org/docs/9.2/static/runtime-config-logging.html#GUC-LOG-TIMEZONE&gt;
accordingly (Tom Lane)

This avoids expensive time zone probes during server start.

Question: is there any way to revert back to old behavior so that server
will probe system�s timezone on startup (default to OS timezone on
startup) instead setting it during initdb?

Obviously, without recompiling/rebuilding Postgres.

I�m dealing with the situation, where system is being built in one
timezone (could be anywhere around the globe), and then moved to other
(not known during system build) location with different timezone.

After relocation, OS timezone will change, but we can�t allow user to
edit timezone parameter in Postgresql.conf.

It is not possible to change the postgresql.conf just before the
relocate? In other words do you have no idea where the server will end up?

Regards,

Igor Neyman

--
Adrian Klaver
adrian.klaver@gmail.com

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

-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver@gmail.com]
Sent: Wednesday, February 06, 2013 4:40 PM
To: Igor Neyman
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] configuring timezone

On 02/06/2013 10:32 AM, Igor Neyman wrote:

Timezone configuration parameter (defaulting to system timezone)
worked fine for us before upgrading from 8.4. to 9.2.

Now we've got a problem.

9.2 Release Notes says:

* Identify the server time zone during initdb, and set
postgresql.conf entries timezone
<http://www.postgresql.org/docs/9.2/static/runtime-config-

client.html#

GUC-TIMEZONE>
and log_timezone
<http://www.postgresql.org/docs/9.2/static/runtime-config-

logging.html

#GUC-LOG-TIMEZONE>
accordingly (Tom Lane)

This avoids expensive time zone probes during server start.

Question: is there any way to revert back to old behavior so that
server will probe system's timezone on startup (default to OS

timezone

on
startup) instead setting it during initdb?

Obviously, without recompiling/rebuilding Postgres.

I'm dealing with the situation, where system is being built in one
timezone (could be anywhere around the globe), and then moved to

other

(not known during system build) location with different timezone.

After relocation, OS timezone will change, but we can't allow user to
edit timezone parameter in Postgresql.conf.

It is not possible to change the postgresql.conf just before the
relocate? In other words do you have no idea where the server will end
up?

Regards,

Igor Neyman

--
Adrian Klaver
adrian.klaver@gmail.com

Sometimes, but not always.

Going back to the reason for this change in Release Notes:

"This avoids expensive time zone probes during server start."

How expensive? How often Postgres is restarted?
We aren't restarting Postgres for months. Doesn't seem to be very valid reason, at least not for us :)

Igor Neyman

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

On 02/06/2013 01:47 PM, Igor Neyman wrote:

--
Adrian Klaver
adrian.klaver@gmail.com

Sometimes, but not always.

I guess you could ship a script that sets the timezone
when the server is installed.

Going back to the reason for this change in Release Notes:

"This avoids expensive time zone probes during server start."

How expensive? How often Postgres is restarted?
We aren't restarting Postgres for months. Doesn't seem to be very valid reason, at least not for us :)

Here is the -hackers message that relates;

/messages/by-id/E1R296v-00014A-D2@gemulon.postgresql.org

Igor Neyman

--
Adrian Klaver
adrian.klaver@gmail.com

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

Igor Neyman <ineyman@perceptron.com> writes:

Going back to the reason for this change in Release Notes:
"This avoids expensive time zone probes during server start."
How expensive?

The time zone probe logic involves reading every file under
/usr/share/zoneinfo (or wherever you have the Olson tz database
installed). There are a couple thousand of those in a typical Linux
installation. In a cold-boot situation where none of that data is
already swapped in, it's not unusual for this to take five seconds or
more. Now that may or may not seem like a lot, but it's more than
enough to cause many startup scripts to conclude that the postmaster has
failed. The hacks we'd built up to deal with this eventually became
insupportable.

We're not going back. I suggest you consider ways to adjust your
server-migration process.

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

9.2.1 was the version standard when I was building and deploying...so no, I
probably will not (personally) be updating anytime soon...

However, if you're interested, I'll see if I can find a place tonight or
tomorrow to put these binaries (they are 32-bit as well), source, etc
(sourceforge maybe?). I can also include to inno setup script that builds
an installer similar to the EnterpriseDB version; that is, it automatices
the service setup, creates a postgres user, etc. Hell, I may as well
include the pre-built installer, too, if you don't want to customize
anything..

In addition to the timezone fix, I (originally) wanted to build my own
Windows installer because the EnterpriseDB version does NOT link against
zlib with respect to openssl. In other words, no compressed ssl connections
are possible with the currently distributed windows version. This one is
linked against zlib (and the speed increase is quite significant).

T.J.

On Wed, Feb 6, 2013 at 3:23 PM, Igor Neyman <ineyman@perceptron.com> wrote:

Thank you for explaining.

Regards,
Igor Neyman

________________________________________
From: Tom Lane [tgl@sss.pgh.pa.us]
Sent: Wednesday, February 06, 2013 5:11 PM
To: Igor Neyman
Cc: Adrian Klaver; pgsql-general@postgresql.org
Subject: Re: [GENERAL] configuring timezone

Igor Neyman <ineyman@perceptron.com> writes:

Going back to the reason for this change in Release Notes:
"This avoids expensive time zone probes during server start."
How expensive?

The time zone probe logic involves reading every file under
/usr/share/zoneinfo (or wherever you have the Olson tz database
installed). There are a couple thousand of those in a typical Linux
installation. In a cold-boot situation where none of that data is
already swapped in, it's not unusual for this to take five seconds or
more. Now that may or may not seem like a lot, but it's more than
enough to cause many startup scripts to conclude that the postmaster has
failed. The hacks we'd built up to deal with this eventually became
insupportable.

We're not going back. I suggest you consider ways to adjust your
server-migration process.

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

Terence,

Thank you for the offer.
But, I will probably be creating custom install scripts to run at destination location to modify parameter in Postgresql.conf.

Regards,
Igor Neyman

From: Terence Ferraro [mailto:terencejferraro@gmail.com]
Sent: Wednesday, February 06, 2013 6:47 PM
To: Igor Neyman
Cc: pgsql-general@postgresql.org
Subject: Re: configuring timezone

9.2.1 was the version standard when I was building and deploying...so no, I probably will not (personally) be updating anytime soon...

However, if you're interested, I'll see if I can find a place tonight or tomorrow to put these binaries (they are 32-bit as well), source, etc (sourceforge maybe?). I can also include to inno setup script that builds an installer similar to the EnterpriseDB version; that is, it automatices the service setup, creates a postgres user, etc. Hell, I may as well include the pre-built installer, too, if you don't want to customize anything..

In addition to the timezone fix, I (originally) wanted to build my own Windows installer because the EnterpriseDB version does NOT link against zlib with respect to openssl. In other words, no compressed ssl connections are possible with the currently distributed windows version. This one is linked against zlib (and the speed increase is quite significant).

T.J.
On Wed, Feb 6, 2013 at 3:23 PM, Igor Neyman <ineyman@perceptron.com<mailto:ineyman@perceptron.com>> wrote:
I am on Windows (both 32 and 64 bit) using 32-bit Postgres.
So, your binaries are for 9.2.1, you aren't planning to go to 9.2.2?

From: Terence Ferraro [mailto:terencejferraro@gmail.com<mailto:terencejferraro@gmail.com>]
Sent: Wednesday, February 06, 2013 3:07 PM
To: Igor Neyman
Cc: pgsql-general@postgresql.org<mailto:pgsql-general@postgresql.org>
Subject: Re: [GENERAL] configuring timezone

Sorry, but from what I understand the change is permanent. If recompile is not an option but you're on Windows let me know; I do have binaries available..
On Wed, Feb 6, 2013 at 2:05 PM, Igor Neyman <ineyman@perceptron.com<mailto:ineyman@perceptron.com>> wrote:
Terence,

Thanks for quick reply, I read your thread (Dec, 2012) before posting my question.
But, recompile is not an option for me. Was hoping, that something regarding this issue changed since...

Igor Neyman

From: Terence Ferraro [mailto:terencejferraro@gmail.com<mailto:terencejferraro@gmail.com>]
Sent: Wednesday, February 06, 2013 1:45 PM
To: Igor Neyman
Cc: pgsql-general@postgresql.org<mailto:pgsql-general@postgresql.org>
Subject: Re: [GENERAL] configuring timezone

See the archived thread here: /messages/by-id/CAEghcWD8DXjroBYCZsdGrx+cHTCbCbW9es2uQ+o7a8NZ61JT=Q@mail.gmail.com

Short version: Sorry, but you're going to need to recompile if you want that behavior. Here's a diff applied against 9.2.1 http://pastebin.com/5AyaX2RF. I've deployed the patched version a couple dozen times now and it is working flawlessly.

T.J.
On Wed, Feb 6, 2013 at 1:32 PM, Igor Neyman <ineyman@perceptron.com<mailto:ineyman@perceptron.com>> wrote:
Timezone configuration parameter (defaulting to system timezone) worked fine for us before upgrading from 8.4. to 9.2.

Now we've got a problem.
9.2 Release Notes says:

* Identify the server time zone during initdb, and set postgresql.conf entries timezone<http://www.postgresql.org/docs/9.2/static/runtime-config-client.html#GUC-TIMEZONE&gt; and log_timezone<http://www.postgresql.org/docs/9.2/static/runtime-config-logging.html#GUC-LOG-TIMEZONE&gt; accordingly (Tom Lane)

This avoids expensive time zone probes during server start.

Question: is there any way to revert back to old behavior so that server will probe system's timezone on startup (default to OS timezone on startup) instead setting it during initdb?
Obviously, without recompiling/rebuilding Postgres.

I'm dealing with the situation, where system is being built in one timezone (could be anywhere around the globe), and then moved to other (not known during system build) location with different timezone.
After relocation, OS timezone will change, but we can't allow user to edit timezone parameter in Postgresql.conf.

Regards,
Igor Neyman