pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #16579: In some areas of UTC+8, "CST" in the log is recognized as the wrong time zone when importing these
2+ messages / 2 participants
[nested] [flat]
* BUG #16579: In some areas of UTC+8, "CST" in the log is recognized as the wrong time zone when importing these
@ 2020-08-12 07:51 PG Bug reporting form <noreply@postgresql.org>
2020-08-12 12:57 ` Re: BUG #16579: In some areas of UTC+8, "CST" in the log is recognized as the wrong time zone when importing these Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 2+ messages in thread
From: PG Bug reporting form @ 2020-08-12 07:51 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: xyh@nvn.xyz
The following bug has been logged on the website:
Bug reference: 16579
Logged by: Bert Xu
Email address: xyh@nvn.xyz
PostgreSQL version: 12.0
Operating system: N/A
Description:
In some areas of UTC+8, "CST" in the log is recognized as the wrong time
zone when importing these logs.
All PostgreSQL version have this problem.
Database parameter:
log_destination = 'csvlog'
logging_collector = 'on'
log_filename = 'postgres.log'
log_directory = 'log'
log_timezone = 'PRC'
timezone = 'PRC'
CREATE TABLE pg_log
( log_time timestamp(3) with time zone,
user_name text,
database_name text,
process_id integer,
connection_from text,
session_id text,
session_line_num bigint,
command_tag text,
session_start_time timestamp with time zone,
virtual_transaction_id text,
transaction_id bigint,
error_severity text,
sql_state_code text,
message text,
detail text,
hint text,
internal_query text,
internal_query_pos integer,
context text,
query text,
query_pos integer,
location text,
application_name text,
PRIMARY KEY (session_id, session_line_num)
);
===============================
phenomenon:
[postgres12@rhel711g log]$ cat postgres.csv
2020-08-12 14:27:17.479 CST,,,9930,,5f338bc5.26ca,1,,2020-08-12 14:27:17
CST,,0,LOG,00000,"ending log output to stderr",,"Future log output will go
to log destination ""csvlog"".",,,,,,,""
2020-08-12 14:27:17.482 CST,,,9932,,5f338bc5.26cc,1,,2020-08-12 14:27:17
CST,,0,LOG,00000,"database system was shut down at 2020-08-12 14:27:17
CST",,,,,,,,,""
2020-08-12 14:27:17.484 CST,,,9930,,5f338bc5.26ca,2,,2020-08-12 14:27:17
CST,,0,LOG,00000,"database system is ready to accept
connections",,,,,,,,,""
2020-08-12 14:30:50.002 CST,,,9930,,5f338bc5.26ca,3,,2020-08-12 14:27:17
CST,,0,LOG,00000,"received fast shutdown request",,,,,,,,,""
2020-08-12 14:30:50.007 CST,,,9930,,5f338bc5.26ca,4,,2020-08-12 14:27:17
CST,,0,LOG,00000,"aborting any active transactions",,,,,,,,,""
COPY pg_log FROM '/pg12/data/log/postgres.csv' WITH csv;
postgres=# select log_time,user_name,message from pg_log;
log_time | user_name |
message
----------------------------+-----------+----------------------------------------------
2020-08-13 04:27:17.479+08 | | ending log output to stderr
2020-08-13 04:27:17.482+08 | | database system was shut down at
2020-08-12 14:27:17 CST
2020-08-13 04:27:17.484+08 | | database system is ready to accept
connections
2020-08-13 04:30:50.002+08 | | received fast shutdown request
2020-08-13 04:30:50.007+08 | | aborting any active transactions
the time is wrong.
++++++++++++++++++++++++++++++++++++++++
change log_timezone='Asia/Shanghai' ,restart db.
[postgres12@rhel711g log]$ cat postgres.csv
2020-08-12 15:03:11.208 CST,,,10773,,5f33942f.2a15,1,,2020-08-12 15:03:11
CST,,0,LOG,00000,"ending log output to stderr",,"Future log output will go
to log destination ""csvlog"".",,,,,,,""
2020-08-12 15:03:11.212 CST,,,10775,,5f33942f.2a17,1,,2020-08-12 15:03:11
CST,,0,LOG,00000,"database system was shut down at 2020-08-12 15:03:11
CST",,,,,,,,,""
2020-08-12 15:03:11.216 CST,,,10773,,5f33942f.2a15,2,,2020-08-12 15:03:11
CST,,0,LOG,00000,"database system is ready to accept
connections",,,,,,,,,""
postgres=# truncate pg_log ;
TRUNCATE TABLE
postgres=# COPY pg_log FROM '/pg12/data/log/postgres.csv' WITH csv;
COPY 3
postgres=# select log_time,user_name,message from pg_log;
log_time | user_name | message
----------------------------+-----------+----------------------------------------------------------
2020-08-13 05:03:11.208+08 | | ending log output to stderr
2020-08-13 05:03:11.212+08 | | database system was shut down at
2020-08-12 15:03:11 CST
2020-08-13 05:03:11.216+08 | | database system is ready to accept
connections
(3 rows)
the time is wrong.
++++++++++++++++++++++++++++++++++++++++++++++
change log_timezone='Asia/Singapore' ,restart db.
[postgres12@rhel711g log]$ cat postgres.csv
2020-08-12 15:01:40.988 +08,,,10743,,5f3393d4.29f7,1,,2020-08-12 15:01:40
+08,,0,LOG,00000,"ending log output to stderr",,"Future log output will go
to log destination ""csvlog"".",,,,,,,""
2020-08-12 15:01:40.991 +08,,,10745,,5f3393d4.29f9,1,,2020-08-12 15:01:40
+08,,0,LOG,00000,"database system was shut down at 2020-08-12 15:01:40
+08",,,,,,,,,""
2020-08-12 15:01:40.994 +08,,,10743,,5f3393d4.29f7,2,,2020-08-12 15:01:40
+08,,0,LOG,00000,"database system is ready to accept
connections",,,,,,,,,""
postgres=# truncate pg_log ;
TRUNCATE TABLE
postgres=# COPY pg_log FROM '/pg12/data/log/postgres.csv' WITH csv;
COPY 3
postgres=# select log_time,user_name,message from pg_log;
log_time | user_name | message
----------------------------+-----------+----------------------------------------------------------
2020-08-12 15:01:40.988+08 | | ending log output to stderr
2020-08-12 15:01:40.991+08 | | database system was shut down at
2020-08-12 15:01:40 +08
2020-08-12 15:01:40.994+08 | | database system is ready to accept
connections
(3 rows)
the time is right.
+++++++++++++++++++++++++++++++++++++++++++++
change log_timezone='Asia/Hong_Kong' ,restart db.
[postgres12@rhel711g log]$ cat postgres.csv
2020-08-12 15:04:21.442 HKT,,,10820,,5f339475.2a44,1,,2020-08-12 15:04:21
HKT,,0,LOG,00000,"ending log output to stderr",,"Future log output will go
to log destination ""csvlog"".",,,,,,,""
2020-08-12 15:04:21.445 HKT,,,10822,,5f339475.2a46,1,,2020-08-12 15:04:21
HKT,,0,LOG,00000,"database system was shut down at 2020-08-12 15:04:21
HKT",,,,,,,,,""
2020-08-12 15:04:21.447 HKT,,,10820,,5f339475.2a44,2,,2020-08-12 15:04:21
HKT,,0,LOG,00000,"database system is ready to accept
connections",,,,,,,,,""
postgres=# truncate pg_log ;
TRUNCATE TABLE
postgres=# COPY pg_log FROM '/pg12/data/log/postgres.csv' WITH csv;
COPY 3
postgres=# select log_time,user_name,message from pg_log;
log_time | user_name | message
----------------------------+-----------+----------------------------------------------------------
2020-08-12 15:04:21.442+08 | | ending log output to stderr
2020-08-12 15:04:21.445+08 | | database system was shut down at
2020-08-12 15:04:21 HKT
2020-08-12 15:04:21.447+08 | | database system is ready to accept
connections
the time is right.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: BUG #16579: In some areas of UTC+8, "CST" in the log is recognized as the wrong time zone when importing these
2020-08-12 07:51 BUG #16579: In some areas of UTC+8, "CST" in the log is recognized as the wrong time zone when importing these PG Bug reporting form <noreply@postgresql.org>
@ 2020-08-12 12:57 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2020-08-12 12:57 UTC (permalink / raw)
To: xyh@nvn.xyz; +Cc: pgsql-bugs@lists.postgresql.org
PG Bug reporting form <noreply@postgresql.org> writes:
> In some areas of UTC+8, "CST" in the log is recognized as the wrong time
> zone when importing these logs.
With the default set of time zone abbreviations, CST will be taken as
US Central Standard Time (UTC-6). If you don't want that, set up an
alternative timezone abbreviation file. See
https://www.postgresql.org/docs/current/datetime-config-files.html
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2020-08-12 12:57 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-08-12 07:51 BUG #16579: In some areas of UTC+8, "CST" in the log is recognized as the wrong time zone when importing these PG Bug reporting form <noreply@postgresql.org>
2020-08-12 12:57 ` Re: BUG #16579: In some areas of UTC+8, "CST" in the log is recognized as the wrong time zone when importing these Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox