Slony-I timezone setting

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
------------------------------------------------------------------