Thread: configuring timezone
Timezone configuration parameter (defaulting to system timezone) worked fi= ne 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 ent= ries timezone<http://www.postgresql.org/docs/9.2/static/runtime-config-clie= nt.html#GUC-TIMEZONE> and log_timezone<http://www.postgresql.org/docs/9.2/s= tatic/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 wi= ll probe system's timezone on startup (default to OS timezone on startup) i= nstead 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 d= uring 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
See the archived thread here: http://www.postgresql.org/message-id/CAEghcWD8DXjroBYCZsdGrx+cHTCbCbW9es2uQ= +o7a8NZ61JT=3DQ@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: > Timezone configuration parameter (defaulting to system timezone) worked > fine for us before upgrading from 8.4. to 9.2.**** > > ** ** > > Now we=92ve got a problem. **** > > 9.2 Release Notes says:**** > > =B7 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-log= ging.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=92s timezone on startup (default to OS timezone on star= tup) > instead setting it during initdb?**** > > Obviously, without recompiling/rebuilding Postgres.**** > > ** ** > > I=92m dealing with the situation, where system is being built in one > timezone (could be anywhere around the globe), and then moved to other (n= ot > known during system build) location with different timezone. **** > > After relocation, OS timezone will change, but we can=92t allow user to e= dit > timezone parameter in Postgresql.conf.**** > > ** ** > > Regards,**** > > Igor Neyman**** >
Terence, Thanks for quick reply, I read your thread (Dec, 2012) before posting my qu= estion. But, recompile is not an option for me. Was hoping, that something regardi= ng 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: http://www.postgresql.org/message-id/CAEghcWD= 8DXjroBYCZsdGrx+cHTCbCbW9es2uQ+o7a8NZ61JT=3DQ@mail.gmail.com Short version: Sorry, but you're going to need to recompile if you want tha= t behavior. Here's a diff applied against 9.2.1 http://pastebin.com/5AyaX2R= F. I've deployed the patched version a couple dozen times now and it is wor= king 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 fi= ne 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 ent= ries timezone<http://www.postgresql.org/docs/9.2/static/runtime-config-clie= nt.html#GUC-TIMEZONE> and log_timezone<http://www.postgresql.org/docs/9.2/s= tatic/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 wi= ll probe system's timezone on startup (default to OS timezone on startup) i= nstead 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 d= uring 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: > 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=85**** > > ** ** > > 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: > http://www.postgresql.org/message-id/CAEghcWD8DXjroBYCZsdGrx+cHTCbCbW9es2= uQ+o7a8NZ61JT=3DQ@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:**** > > Timezone configuration parameter (defaulting to system timezone) worked > fine for us before upgrading from 8.4. to 9.2.**** > > **** > > Now we=92ve got a problem. **** > > 9.2 Release Notes says:**** > > =B7 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-log= ging.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=92s timezone on startup (default to OS timezone on star= tup) > instead setting it during initdb?**** > > Obviously, without recompiling/rebuilding Postgres.**** > > **** > > I=92m dealing with the situation, where system is being built in one > timezone (could be anywhere around the globe), and then moved to other (n= ot > known during system build) location with different timezone. **** > > After relocation, OS timezone will change, but we can=92t allow user to e= dit > timezone parameter in Postgresql.conf.**** > > **** > > Regards,**** > > Igor Neyman**** > > ** ** >
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 availab= le.. 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 qu= estion. But, recompile is not an option for me. Was hoping, that something regardi= ng this issue changed since... Igor Neyman From: Terence Ferraro [mailto:terencejferraro@gmail.com<mailto:terencejferr= aro@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: http://www.postgresql.org/message-id/CAEghcWD= 8DXjroBYCZsdGrx+cHTCbCbW9es2uQ+o7a8NZ61JT=3DQ@mail.gmail.com Short version: Sorry, but you're going to need to recompile if you want tha= t behavior. Here's a diff applied against 9.2.1 http://pastebin.com/5AyaX2R= F. I've deployed the patched version a couple dozen times now and it is wor= king 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 fi= ne 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 ent= ries timezone<http://www.postgresql.org/docs/9.2/static/runtime-config-clie= nt.html#GUC-TIMEZONE> and log_timezone<http://www.postgresql.org/docs/9.2/s= tatic/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 wi= ll probe system's timezone on startup (default to OS timezone on startup) i= nstead 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 d= uring 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 weve 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 systems timezone on startup (default to OS timezone on > startup) instead setting it during initdb? > > Obviously, without recompiling/rebuilding Postgres. > > Im 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 cant 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
> -----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 >=20 > 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. >=20 >=20 > 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? >=20 > > > > Regards, > > > > Igor Neyman > > >=20 >=20 > -- > 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 re= ason, at least not for us :) Igor Neyman
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; http://www.postgresql.org/message-id/E1R296v-00014A-D2@gemulon.postgresql.org > > Igor Neyman > > -- Adrian Klaver adrian.klaver@gmail.com
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
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: > I am on Windows (both 32 and 64 bit) using 32-bit Postgres.**** > > So, your binaries are for 9.2.1, you aren=92t 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 i= s > 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:**** > > 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=85**** > > **** > > 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: > http://www.postgresql.org/message-id/CAEghcWD8DXjroBYCZsdGrx+cHTCbCbW9es2= uQ+o7a8NZ61JT=3DQ@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:**** > > Timezone configuration parameter (defaulting to system timezone) worked > fine for us before upgrading from 8.4. to 9.2.**** > > **** > > Now we=92ve got a problem. **** > > 9.2 Release Notes says:**** > > =B7 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-log= ging.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=92s timezone on startup (default to OS timezone on star= tup) > instead setting it during initdb?**** > > Obviously, without recompiling/rebuilding Postgres.**** > > **** > > I=92m dealing with the situation, where system is being built in one > timezone (could be anywhere around the globe), and then moved to other (n= ot > known during system build) location with different timezone. **** > > After relocation, OS timezone will change, but we can=92t allow user to e= dit > timezone parameter in Postgresql.conf.**** > > **** > > Regards,**** > > Igor Neyman**** > > **** > > ** ** >
Thank you for explaining.=0A=
=0A=
Regards,=0A=
Igor Neyman=0A=
=0A=
________________________________________=0A=
From: Tom Lane [tgl@sss.pgh.pa.us]=0A=
Sent: Wednesday, February 06, 2013 5:11 PM=0A=
To: Igor Neyman=0A=
Cc: Adrian Klaver; pgsql-general@postgresql.org=0A=
Subject: Re: [GENERAL] configuring timezone=0A=
=0A=
Igor Neyman <ineyman@perceptron.com> writes:=0A=
> Going back to the reason for this change in Release Notes:=0A=
> "This avoids expensive time zone probes during server start."=0A=
> How expensive?=0A=
=0A=
The time zone probe logic involves reading every file under=0A=
/usr/share/zoneinfo (or wherever you have the Olson tz database=0A=
installed). There are a couple thousand of those in a typical Linux=0A=
installation. In a cold-boot situation where none of that data is=0A=
already swapped in, it's not unusual for this to take five seconds or=0A=
more. Now that may or may not seem like a lot, but it's more than=0A=
enough to cause many startup scripts to conclude that the postmaster has=0A=
failed. The hacks we'd built up to deal with this eventually became=0A=
insupportable.=0A=
=0A=
We're not going back. I suggest you consider ways to adjust your=0A=
server-migration process.=0A=
=0A=
regards, tom lane=0A=
Terence, Thank you for the offer. But, I will probably be creating custom install scripts to run at destinati= on 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 to= morrow to put these binaries (they are 32-bit as well), source, etc (source= forge maybe?). I can also include to inno setup script that builds an insta= ller similar to the EnterpriseDB version; that is, it automatices the servi= ce 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 Wind= ows installer because the EnterpriseDB version does NOT link against zlib w= ith respect to openssl. In other words, no compressed ssl connections are p= ossible 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:terencejferr= aro@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 availab= le.. 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 qu= estion. But, recompile is not an option for me. Was hoping, that something regardi= ng this issue changed since... Igor Neyman From: Terence Ferraro [mailto:terencejferraro@gmail.com<mailto:terencejferr= aro@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: http://www.postgresql.org/message-id/CAEghcWD= 8DXjroBYCZsdGrx+cHTCbCbW9es2uQ+o7a8NZ61JT=3DQ@mail.gmail.com Short version: Sorry, but you're going to need to recompile if you want tha= t behavior. Here's a diff applied against 9.2.1 http://pastebin.com/5AyaX2R= F. I've deployed the patched version a couple dozen times now and it is wor= king 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 fi= ne 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 ent= ries timezone<http://www.postgresql.org/docs/9.2/static/runtime-config-clie= nt.html#GUC-TIMEZONE> and log_timezone<http://www.postgresql.org/docs/9.2/s= tatic/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 wi= ll probe system's timezone on startup (default to OS timezone on startup) i= nstead 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 d= uring 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