Slony1 PostgreSQL Replication

Author

timestamp

Sebastian Kühner

2005-09-06, 1:26 pm

Hello,

I use the following script to setup a cluster:

#!/bin/sh
CLUSTER=sql_cluster6

DB1=contactdb
DB2=contactdb_slave
H1=localhost
H2=localhost
U=postgres
slonik <<_EOF_
cluster name = $CLUSTER;
node 1 admin conninfo = 'dbname=$DB1 host=$H1 user=$U';
node 2 admin conninfo = 'dbname=$DB2 host=$H2 user=$U';
init cluster (id = 1, comment = 'Node 1');
create set (id = 1, origin = 1,
comment = 'contact table');
set add table (set id = 1, origin = 1, id = 1,
full qualified name = 'public.contact',
comment = 'Table contact');
set add sequence (set id = 1, origin = 1, id = 2,
full qualified name = 'public.contact_seq',
comment = 'Sequence contact_seq');
store node (id = 2, comment = 'Node 2');
store path (server = 1, client = 2,
conninfo = 'dbname=$DB1 host=$H1 user=$U');
store path (server = 2, client = 1,
conninfo = 'dbname=$DB2 host=$H2 user=$U');
store listen (origin = 1, provider = 1, receiver = 2);
store listen (origin = 2, provider = 2, receiver = 1);

....and following error message occurs:

ERROR: invalid input syntax for type timestamp: "Tue Sep 06 02:23:17.284444
2005 Local time zone must be set--see zic manual page"
CONTEXT: SQL statement "insert into "_sql_cluster6".sl_confirm (con_origin,
con_received, con_seqno) select no_id, $1 , 0 from "_sql_cluster6".sl_node
where no_id != $2 and no_active"
PL/pgSQL function "enablenode_int" line 32 at SQL statement

I don't have any idea why. Anyone of you can help me? How do I use ZIC (man
zic doesn't say me anything)?

Many thanks!

Sebastian

Christopher Browne

2005-09-06, 1:26 pm

Sebastian K=FChner wrote:

>...and following error message occurs:
>
>ERROR: invalid input syntax for type timestamp: "Tue Sep 06 02:23:17.28=
4444
>2005 Local time zone must be set--see zic manual page"
>CONTEXT: SQL statement "insert into "_sql_cluster6".sl_confirm (con_ori=
gin,
>con_received, con_seqno) select no_id, $1 , 0 from "_sql_cluster6".sl_n=
ode
>where no_id !=3D $2 and no_active"
> PL/pgSQL function "enablenode_int" line 32 at SQL statement
>
>
>I don't have any idea why. Anyone of you can help me? How do I use ZIC (=
man
>zic doesn't say me anything)?
> =20
>
I'm not sure what flavour of Unix you're on; what is seemingly happening
here is that there is some confusion as to what timezone you're in.

The "best practice" that we have found has been to set PGTZ and TZ to
either GMT or UTC; it seems clear that that is NOT what you're using.

This isn't a Slony-I problem per se; you'll find it the case any time
you grab a timestamp via current_timestamp if you're using a timezone
that PostgreSQL doesn't recognize.

Sebastian Kühner

2005-09-06, 8:24 pm

Hi!

Many thanks. Now my script worked.

This was my solution:

TZ=3D'America/Argentina/Mendoza'
export TZ
PGTZ=3D'America/Argentina/Mendoza'
export PGTZ

and then: ./cluster_setup.sh

Without errors. So it wasn't necesary to put them to GMT. I put it into
/etc/profile so I think they will be initialized on any startup...

Sebastian

----- Original Message -----=20
From: "Christopher Browne" <cbbrowne-swQf4SbcV9C7WVzo/KQ3Mw@public.gmane.org>
To: "Sebastian K=FChner" <skuehner- W+yTvUamF4rQT0dZR+Al
fA@public.gmane.org>
Cc: <slony1-general- AuKwsB3Fm+ugFIWk8tvy
RWD2FQJk+8+b@public.gmane.org>
Sent: Tuesday, September 06, 2005 3:00 PM
Subject: Re: [Slony1-general] timestamp

> Sebastian K=FChner wrote:
>
02:23:17. 284444[color=darkred
]
(con_origin,[color=d
arkred]
"_sql_cluster6". sl_node[color=darkre
d]
(man[color=darkred]
> I'm not sure what flavour of Unix you're on; what is seemingly happenin=
g
> here is that there is some confusion as to what timezone you're in.
>
> The "best practice" that we have found has been to set PGTZ and TZ to
> either GMT or UTC; it seems clear that that is NOT what you're using.
>
> This isn't a Slony-I problem per se; you'll find it the case any time
> you grab a timestamp via current_timestamp if you're using a timezone
> that PostgreSQL doesn't recognize.
>

Christopher Browne

2005-09-06, 8:24 pm

Sebastian K=FChner wrote:

>Hi!
>
>Many thanks. Now my script worked.
>
>This was my solution:
>
>TZ=3D'America/Argentina/Mendoza'
>export TZ
>PGTZ=3D'America/Argentina/Mendoza'
>export PGTZ
>
>and then: ./cluster_setup.sh
>
>Without errors. So it wasn't necesary to put them to GMT. I put it into
>/etc/profile so I think they will be initialized on any startup...
> =20
>
That may work with Red Hat Linux; I'm not sure that TZ value would
necessarily work everywhere.

We have enough diversity of server locations in our distributed
environment that GMT/UTC has proven to be our best choice, not just for
Slony-I, but for pretty well all processing, because:

a) There are no Daylight Savings Time issues, where time shifts, as if
by magic, by an hour, every so often;
b) Our systems report time to outside clients in UTC;
c) We don't get confused when logging onto an internal host that is in
the "wrong" timezone.

If you only have a few servers, in one spot, it may be no big deal. =20
With many servers in many places, it's quite a big deal...

Sponsored Links