Обсуждение: Slony-I timezone setting
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Hi All,
When configuring a Slony cluster I get the infamous 'ERROR: invalid
input syntax for type timestamp: "Mon Jun 29 13:00:36.628805 2009 WEST"'
I know that this is a timezone setting issue.
In my case I have my system set to 'Atlantic/Madeira' and UTC.
My postgresql.conf has the same setting ('Atlantic/Madeira')
Postgresql ver. 8.3.7 on Fedora 8
I feel reluctant to follow Slony's 'Best practices' depicted in the
(cough) fine manual as it would mean losing any DST awareness ...
Any thoughts appreciated,
TIA,
- --
Pedro Doria Meunier
GSM: +351 96 17 20 188
Skype: pdoriam
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.7 (GNU/Linux)
Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org
iD8DBQFKSLaI2FH5GXCfxAsRAkPpAJ48qThWwTWwwIRK802T/Tyn9ztyvgCfWjw3
kkUnMNb1hmKNYZ5dmM04C7U=
=3XOr
-----END PGP SIGNATURE-----
Pedro Doria Meunier <pdoria@netmadeira.com> writes:
> When configuring a Slony cluster I get the infamous 'ERROR: invalid
> input syntax for type timestamp: "Mon Jun 29 13:00:36.628805 2009 WEST"'
You need to make the timezone_abbreviations configuration on the slave
match that on the master. Alternatively, set datestyle to ISO so that
a less ambiguous timestamp format is used.
regards, tom lane
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Thanks Tom for your thoughts :) I tried what you suggested to no avail :-( Looking at this more closely I see this format when the error arises: "Mon Jun 29 15:28:10.952151 2009 WEST" Curiously enough this is what the following command throws out of /etc/localtime: zdump -v /etc/localtime | grep 2009 /etc/localtime Sun Mar 29 00:59:59 2009 UTC = Sun Mar 29 00:59:59 2009 WET isdst=0 gmtoff=0 /etc/localtime Sun Mar 29 01:00:00 2009 UTC = Sun Mar 29 02:00:00 2009 WEST isdst=1 gmtoff=3600 /etc/localtime Sun Oct 25 00:59:59 2009 UTC = Sun Oct 25 01:59:59 2009 WEST isdst=1 gmtoff=3600 /etc/localtime Sun Oct 25 01:00:00 2009 UTC = Sun Oct 25 01:00:00 2009 WET isdst=0 gmtoff=0 Even though I'm *sure* that the system's timezone is set to 'Atlantic/Madeira'... This is what's defined in postgresql.conf datestyle = 'iso, ymd' timezone = 'Atlantic/Madeira' BR, Pedro Doria Meunier GSM: +351 96 17 20 188 Skype: pdoriam Tom Lane wrote: > Pedro Doria Meunier <pdoria@netmadeira.com> writes: >> When configuring a Slony cluster I get the infamous 'ERROR: >> invalid input syntax for type timestamp: "Mon Jun 29 >> 13:00:36.628805 2009 WEST"' > > You need to make the timezone_abbreviations configuration on the > slave match that on the master. Alternatively, set datestyle to > ISO so that a less ambiguous timestamp format is used. > > regards, tom lane > -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.7 (GNU/Linux) Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org iD8DBQFKSNJs2FH5GXCfxAsRAmCJAKC/WBdfkUmkKUgvBvrSwD0dLOoGmwCcCPb7 UJRvPCTdSKCUkiOoEXf/WmU= =QEO6 -----END PGP SIGNATURE-----
Pedro Doria Meunier <pdoria@netmadeira.com> writes:
> This is what's defined in postgresql.conf
> datestyle = 'iso, ymd'
> timezone = 'Atlantic/Madeira'
Hmm. WET/WEST are the zone abbreviations for that zone, all right,
but I don't understand why they're being emitted if you have that
datestyle setting. Maybe something is overriding the datestyle for
some dumb reason?
Anyway, a look at the default timezone abbrevs file shows that it
recognizes WETDST not WEST. You might care to add WEST as an
accepted abbrev too.
regards, tom lane
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Tom,
This is what I have in '/usr/share/pgsql/timezonesets/Atlantic.txt':
WEST 3600 D # Western Europe Summer Time
# (Atlantic/Canary)
# (Atlantic/Faeroe)
# (Atlantic/Madeira)
# (Europe/Lisbon)
I copied this portion into '/usr/share/pgsql/timezonesets/Default' and
restarted the service.
Still no go :(
I even tried alter user user-slony set timezone='WEST';
After the mods this what the query gives:
ERROR: unrecognized time zone name: "WEST"
And this is when I ran out of ideas...
btw: do you happen to know of a Slony mailing list?
TIA,
Pedro Doria Meunier
GSM: +351 96 17 20 188
Skype: pdoriam
Tom Lane wrote:
> Pedro Doria Meunier <pdoria@netmadeira.com> writes:
>> This is what's defined in postgresql.conf
>
>> datestyle = 'iso, ymd' timezone = 'Atlantic/Madeira'
>
> Hmm. WET/WEST are the zone abbreviations for that zone, all right,
> but I don't understand why they're being emitted if you have that
> datestyle setting. Maybe something is overriding the datestyle for
> some dumb reason?
>
> Anyway, a look at the default timezone abbrevs file shows that it
> recognizes WETDST not WEST. You might care to add WEST as an
> accepted abbrev too.
>
> regards, tom lane
>
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.7 (GNU/Linux)
Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org
iD8DBQFKSOBE2FH5GXCfxAsRAr+8AJsHvnlpWWZw7rVb2Kp9A70Q4/DJPwCfXbrb
L+n1Km17aMA7AzhUe7IqvPs=
=THOj
-----END PGP SIGNATURE-----
On 29/06/2009 16:39, Pedro Doria Meunier wrote: > btw: do you happen to know of a Slony mailing list? Here you go: http://lists.slony.info/mailman/listinfo Ray. ------------------------------------------------------------------ Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland rod@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals ------------------------------------------------------------------