Обсуждение: 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 ------------------------------------------------------------------