BUG #16579: In some areas of UTC+8, "CST" in the log is recognized as the wrong time zone when importing these

Started by PG Bug reporting formover 5 years ago2 messagesbugs
Jump to latest
#1PG Bug reporting form
noreply@postgresql.org

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.

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: PG Bug reporting form (#1)
Re: 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> 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