渡鴉之丘: 台灣時區的時戳資料超前 14 小時的神秘問題

PostgreSQL 跟 Linux 作業系統在時區設定上的狀況如下：

Linux 電腦的 OS 時區設定成 IANA 時區識別代號 Asia/Taipei；在 SystemD 的系統中，timedatectl 會顯示 Asia/Taipei，而 date 指令則以 Timezone Abbreviation 呈現出容易在各種面向混淆的 CST 縮寫顯示。
PostgreSQL 的時區在初始化 (initdb) 時決定（可以隨時調整）。沒有特別指定，維持預設。此時 Postgres initdb 會偵測 OS 的時區設定值，設定成相同時區。當 OS 設定成 Asia/Taipei 時，在 Postgres 會初始化成 ROC。
Postgres 的時間函數，都會以 UTC offset 的方式顯示時區；然而，Postgres log 的時戳格式，則會以 Timezone Abbreviation 顯示。

然而，這樣會出現一個問題。

通常，軟體的 Log 會再被收集與處理儲存（例如 Elasticsearch 之類）。
但是，用 Timezone Abbreviation 顯示的時戳資料，不小的機會會被混淆：
例如，這邊看到的 CST 標籤，同時代表了 (North American) Central Standard Time (UTC−6) 、 Cuba Standard Time (UTC−5) 或 China Standard Time (UTC+8)。而多數的函式庫在處理時，傾向將 CST 理解成 Central Standard Time (UTC−6) 。

其實～理想上的解法應該是使的 IANA 時區識別代號 Asia/Taipei 對應的 Timezone Abbreviation 正名為 TST（Taiwan Standard Time）就解決了～！

不過。。。其中或許有些令人無奈的政治因素。。。這邊就點到為止，具體細節要好好挖 IANA — Time Zone Database 的內容才知曉。。。希望有朝一日可以正常化囉～

以下直接在 Postgres 裡面，用簡單的方式顯示這種狀況。
我們這邊在 CentOS 7 用 EDB 的 Postgres 企業版作示範，不過操作跟普通 Postgres 一模一樣。
這裡要使用 Postgres 讀檔案的外部表 file_fdw，讀取 Postgres 自己的 log 檔案。

首先，先顯示一些時區的設定。

OS 時區

-bash-4.2$ timedatectl 
      Local time: Sat 2020-03-28 17:33:18 CST
  Universal time: Sat 2020-03-28 09:33:18 UTC
        RTC time: n/a
       Time zone: Asia/Taipei (CST, +0800)
     NTP enabled: n/a
NTP synchronized: yes
 RTC in local TZ: no
      DST active: n/a
-bash-4.2$ 
-bash-4.2$ date
Sat Mar 28 17:33:37 CST 2020
-bash-4.2$

DB 的設定

edb=# select name, setting, sourcefile from pg_file_settings where name like '%time%';
       name       | setting |               sourcefile               
------------------+---------+----------------------------------------
 log_timezone     | UTC     | /pgdata/as12/data/postgresql.conf
 timezone         | UTC     | /pgdata/as12/data/postgresql.conf
 lc_time          | C       | /pgdata/as12/data/postgresql.conf
 timed_statistics | off     | /pgdata/as12/data/postgresql.conf
 log_timezone     | ROC     | /pgdata/as12/data/postgresql.auto.conf
 timezone         | ROC     | /pgdata/as12/data/postgresql.auto.conf
 timed_statistics | on      | /pgdata/as12/data/postgresql.auto.conf
(7 rows)

Time: 2.755 ms
edb=# 
edb=# show timezone;
 TimeZone 
----------
 ROC
(1 row)

Time: 0.403 ms
edb=# show log_timezone;
 log_timezone 
--------------
 ROC
(1 row)

Time: 0.390 ms
edb=# 
edb=# select now();
               now                
----------------------------------
 28-MAR-20 17:36:49.138085 +08:00
(1 row)

Time: 1.070 ms
edb=#

以上兩者可以相比較。

然後，就開始設定 file_fdw 讀取 log：其中我會故意做錯一步，讓 log 紀錄一次事件。

至於 log 欄位定義，可以直接抄 file_fdw 手冊內容～

edb=# alter system set log_destination = 'stderr,csvlog';
ALTER SYSTEM
Time: 53.928 ms
edb=# select pg_reload_conf();
 pg_reload_conf 
----------------
 t
(1 row)

Time: 26.595 ms
edb=# 
edb=# CREATE EXTENSION file_fdw;
CREATE EXTENSION
Time: 256.811 ms
edb=# 
edb=# CREATE SERVER pglog FOREIGN DATA WRAPPER file_fdw;
CREATE SERVER
Time: 87.866 ms
edb=# 
edb=# -- 故意做錯，使 log 留一點內容
edb=# CREATE SERVER pglog FOREIGN DATA WRAPPER file_fd;
ERROR:  server "pglog" already exists
Time: 1.194 ms
edb=# 
edb=# --確認 log 檔是哪一份
edb=# \! cat $PGDATA/current_logfiles
stderr ../log/edb-2020-03-28_141408.log
csvlog ../log/edb-2020-03-28_141408.csv
edb=# 
edb=# CREATE FOREIGN TABLE pglog (
  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
) SERVER pglog
OPTIONS ( filename '/pgdata/as12/log/edb-2020-03-28_141408.csv', format 'csv' );
CREATE FOREIGN TABLE
Time: 134.969 ms
edb=# 
edb=# -- 透過外部表觀看 log 內容
edb=# select * from pglog ;
           log_time            |  user_name   | database_name | process_id | connection_from |  session_id   | session_line_num |  command_tag  |    session_start_time     | virtual_transaction_id | transaction_id | error_severity | sql_state_code |                    message                    | detail |  hint  | internal_query | internal_query_pos | context |                       query                       | query_pos | location | application_name 
-------------------------------+--------------+---------------+------------+-----------------+---------------+------------------+---------------+---------------------------+------------------------+----------------+----------------+----------------+-----------------------------------------------+--------+--------+----------------+--------------------+---------+---------------------------------------------------+-----------+----------+------------------
 29-MAR-20 07:38:59.659 +08:00 | enterprisedb | edb           |      13888 | [local]         | 5e7f1a15.3640 |                2 | CREATE SERVER | 29-MAR-20 07:34:13 +08:00 | 6/1018                 |              0 | ERROR          | 42710          | server "pglog" already exists                 | (NULL) | (NULL) | (NULL)         |             (NULL) | (NULL)  | CREATE SERVER pglog FOREIGN DATA WRAPPER file_fd; |    (NULL) | (NULL)   | psql
(1 row)

Time: 3.235 ms
edb=# select now();
               now                
----------------------------------
 28-MAR-20 17:40:52.574872 +08:00
(1 row)

Time: 0.617 ms
edb=# 
edb=# -- 直接對照 log 原始檔
edb=# \! cat /pgdata/as12/log/edb-2020-03-28_141408.csv
2020-03-28 17:38:59.659 CST,"enterprisedb","edb",13888,"[local]",5e7f1a15.3640,2,"CREATE SERVER",2020-03-28 17:34:13 CST,6/1018,0,ERROR,42710,"server ""pglog"" already exists",,,,,,"CREATE SERVER pglog FOREIGN DATA WRAPPER file_fd;",,,"psql"
edb=#

由上可見，透過軟體再處理時戳，會將 CST 作怪怪的轉換。

這邊的轉換錯誤，就是誤將 CST (UTC+8) 當成 CST (UTC-6) 的 Central Standard Time，因此原先應該是台灣時間 28 號下午 17:34 分，就變成美國時間 28 號下午 17:34 分。接著 file_fdw 為因應我們 Client 端的台灣時區，便將「美國時間」偏移 (6+8) 小時，於是就顯示成「台灣時間」 29 號早上 7:34 分了。

這邊完全使用 Postgres 的功能作示範，但是這邊呈現的現象，還會在各種地方出現，例如，使用 Python 來吃 Postgres log 內容之時～因此要多注意。

至於處理的方式，目前有兩個急就章的方式：

1. 維持 Postgres 的 Timezone 相關參數為 UTC，而保持作業系統設定為 Asia/Taipei，如此 log 紀錄的時戳仍然是正確的，只是顯示上是 UTC。

edb=# alter system set log_timezone to default ;
ALTER SYSTEM
Time: 62.744 ms
edb=# select pg_reload_conf();
 pg_reload_conf 
----------------
 t
(1 row)

Time: 0.913 ms
edb=# CREATE SERVER pglog FOREIGN DATA WRAPPER file_fdw;
ERROR:  server "pglog" already exists
Time: 3.442 ms
edb=# \! cat /pgdata/as12/log/edb-2020-03-28_141408.csv
2020-03-28 17:38:59.659 CST,"enterprisedb","edb",13888,"[local]",5e7f1a15.3640,2,"CREATE SERVER",2020-03-28 17:34:13 CST,6/1018,0,ERROR,42710,"server ""pglog"" already exists",,,,,,"CREATE SERVER pglog FOREIGN DATA WRAPPER file_fd;",,,"psql"
2020-03-28 18:13:37.347 CST,,,585,,5e7b2062.249,5,,2020-03-25 17:12:02 CST,,0,LOG,00000,"received SIGHUP, reloading configuration files",,,,,,,,,""
2020-03-28 10:13:37.350 UTC,,,585,,5e7b2062.249,7,,2020-03-25 17:12:02 CST,,0,LOG,00000,"parameter ""log_timezone"" changed to ""UTC""",,,,,,,,,""
2020-03-28 10:13:44.093 UTC,"enterprisedb","edb",13888,"[local]",5e7f1a15.3640,6,"CREATE SERVER",2020-03-28 17:34:13 CST,6/1027,0,ERROR,42710,"server ""pglog"" already exists",,,,,,"CREATE SERVER pglog FOREIGN DATA WRAPPER file_fdw;",,,"psql"
edb=# select * from pglog ;
           log_time            |  user_name   | database_name | process_id | connection_from |  session_id   | session_line_num |  command_tag  |    session_start_time     | virtual_transaction_id | transaction_id | error_severity | sql_state_code |                    message                     | detail |  hint  | internal_query | internal_query_pos | context |                       query                        | query_pos | location | application_name 
-------------------------------+--------------+---------------+------------+-----------------+---------------+------------------+---------------+---------------------------+------------------------+----------------+----------------+----------------+------------------------------------------------+--------+--------+----------------+--------------------+---------+----------------------------------------------------+-----------+----------+------------------
 29-MAR-20 07:38:59.659 +08:00 | enterprisedb | edb           |      13888 | [local]         | 5e7f1a15.3640 |                2 | CREATE SERVER | 29-MAR-20 07:34:13 +08:00 | 6/1018                 |              0 | ERROR          | 42710          | server "pglog" already exists                  | (NULL) | (NULL) | (NULL)         |             (NULL) | (NULL)  | CREATE SERVER pglog FOREIGN DATA WRAPPER file_fd;  |    (NULL) | (NULL)   | psql
 29-MAR-20 08:13:37.347 +08:00 | (NULL)       | (NULL)        |        585 | (NULL)          | 5e7b2062.249  |                5 | (NULL)        | 26-MAR-20 07:12:02 +08:00 | (NULL)                 |              0 | LOG            | 00000          | received SIGHUP, reloading configuration files | (NULL) | (NULL) | (NULL)         |             (NULL) | (NULL)  | (NULL)                                             |    (NULL) | (NULL)   | 
 28-MAR-20 18:13:37.35 +08:00  | (NULL)       | (NULL)        |        585 | (NULL)          | 5e7b2062.249  |                7 | (NULL)        | 26-MAR-20 07:12:02 +08:00 | (NULL)                 |              0 | LOG            | 00000          | parameter "log_timezone" changed to "UTC"      | (NULL) | (NULL) | (NULL)         |             (NULL) | (NULL)  | (NULL)                                             |    (NULL) | (NULL)   | 
 28-MAR-20 18:13:44.093 +08:00 | enterprisedb | edb           |      13888 | [local]         | 5e7f1a15.3640 |                6 | CREATE SERVER | 29-MAR-20 07:34:13 +08:00 | 6/1027                 |              0 | ERROR          | 42710          | server "pglog" already exists                  | (NULL) | (NULL) | (NULL)         |             (NULL) | (NULL)  | CREATE SERVER pglog FOREIGN DATA WRAPPER file_fdw; |    (NULL) | (NULL)   | psql
(4 rows)

Time: 1.247 ms
edb=#

2. Timezone Abbreviation 之中，有香港時間 HKT 的代號。香港時間也是 UTC+8 時區，在台灣的我們也可以考慮將時區設定成 HKT，如此一來就可以了～

edb=# alter system set log_timezone to 'Asia/Hong_Kong' ;
ALTER SYSTEM
Time: 80.858 ms
edb=# select pg_reload_conf();
 pg_reload_conf 
----------------
 t
(1 row)

Time: 0.819 ms
edb=#
edb=# CREATE SERVER pglog FOREIGN DATA WRAPPER file_fdw;
ERROR:  server "pglog" already exists
Time: 0.366 ms
edb=# select * from pglog ;
           log_time            |  user_name   | database_name | process_id | connection_from |  session_id   | session_line_num |  command_tag  |    session_start_time     | virtual_transaction_id | transaction_id | error_severity | sql_state_code |                           message                            | detail |  hint  | internal_query | internal_query_pos | context |                        query                        | query_pos | location | application_name 
-------------------------------+--------------+---------------+------------+-----------------+---------------+------------------+---------------+---------------------------+------------------------+----------------+----------------+----------------+--------------------------------------------------------------+--------+--------+----------------+--------------------+---------+-----------------------------------------------------+-----------+----------+------------------
 29-MAR-20 07:38:59.659 +08:00 | enterprisedb | edb           |      13888 | [local]         | 5e7f1a15.3640 |                2 | CREATE SERVER | 29-MAR-20 07:34:13 +08:00 | 6/1018                 |              0 | ERROR          | 42710          | server "pglog" already exists                                | (NULL) | (NULL) | (NULL)         |             (NULL) | (NULL)  | CREATE SERVER pglog FOREIGN DATA WRAPPER file_fd;   |    (NULL) | (NULL)   | psql
 29-MAR-20 08:13:37.347 +08:00 | (NULL)       | (NULL)        |        585 | (NULL)          | 5e7b2062.249  |                5 | (NULL)        | 26-MAR-20 07:12:02 +08:00 | (NULL)                 |              0 | LOG            | 00000          | received SIGHUP, reloading configuration files               | (NULL) | (NULL) | (NULL)         |             (NULL) | (NULL)  | (NULL)                                              |    (NULL) | (NULL)   | 
 28-MAR-20 18:13:37.35 +08:00  | (NULL)       | (NULL)        |        585 | (NULL)          | 5e7b2062.249  |                7 | (NULL)        | 26-MAR-20 07:12:02 +08:00 | (NULL)                 |              0 | LOG            | 00000          | parameter "log_timezone" changed to "UTC"                    | (NULL) | (NULL) | (NULL)         |             (NULL) | (NULL)  | (NULL)                                              |    (NULL) | (NULL)   | 
 28-MAR-20 18:13:44.093 +08:00 | enterprisedb | edb           |      13888 | [local]         | 5e7f1a15.3640 |                6 | CREATE SERVER | 29-MAR-20 07:34:13 +08:00 | 6/1027                 |              0 | ERROR          | 42710          | server "pglog" already exists                                | (NULL) | (NULL) | (NULL)         |             (NULL) | (NULL)  | CREATE SERVER pglog FOREIGN DATA WRAPPER file_fdw;  |    (NULL) | (NULL)   | psql
 28-MAR-20 18:37:06.262 +08:00 | (NULL)       | (NULL)        |        585 | (NULL)          | 5e7b2062.249  |                8 | (NULL)        | 26-MAR-20 07:12:02 +08:00 | (NULL)                 |              0 | LOG            | 00000          | received SIGHUP, reloading configuration files               | (NULL) | (NULL) | (NULL)         |             (NULL) | (NULL)  | (NULL)                                              |    (NULL) | (NULL)   | 
 28-MAR-20 18:37:06.265 +08:00 | (NULL)       | (NULL)        |        585 | (NULL)          | 5e7b2062.249  |               10 | (NULL)        | 26-MAR-20 07:12:02 +08:00 | (NULL)                 |              0 | LOG            | 00000          | parameter "log_timezone" changed to "Asia/Hong_Kong"         | (NULL) | (NULL) | (NULL)         |             (NULL) | (NULL)  | (NULL)                                              |    (NULL) | (NULL)   | 
 28-MAR-20 18:37:32.799 +08:00 | enterprisedb | edb           |      13888 | [local]         | 5e7f1a15.3640 |               29 | CREATE SERVER | 29-MAR-20 07:34:13 +08:00 | 6/1041                 |              0 | ERROR          | 42710          | server "pglog" already exists                                | (NULL) | (NULL) | (NULL)         |             (NULL) | (NULL)  | CREATE SERVER pglog FOREIGN DATA WRAPPER file_fdw;  |    (NULL) | (NULL)   | psql
(7 rows)

Time: 1.619 ms
edb=# 
edb=# \! cat /pgdata/as12/log/edb-2020-03-28_141408.csv
2020-03-28 17:38:59.659 CST,"enterprisedb","edb",13888,"[local]",5e7f1a15.3640,2,"CREATE SERVER",2020-03-28 17:34:13 CST,6/1018,0,ERROR,42710,"server ""pglog"" already exists",,,,,,"CREATE SERVER pglog FOREIGN DATA WRAPPER file_fd;",,,"psql"
2020-03-28 18:13:37.347 CST,,,585,,5e7b2062.249,5,,2020-03-25 17:12:02 CST,,0,LOG,00000,"received SIGHUP, reloading configuration files",,,,,,,,,""
2020-03-28 10:13:37.350 UTC,,,585,,5e7b2062.249,7,,2020-03-25 17:12:02 CST,,0,LOG,00000,"parameter ""log_timezone"" changed to ""UTC""",,,,,,,,,""
2020-03-28 10:13:44.093 UTC,"enterprisedb","edb",13888,"[local]",5e7f1a15.3640,6,"CREATE SERVER",2020-03-28 17:34:13 CST,6/1027,0,ERROR,42710,"server ""pglog"" already exists",,,,,,"CREATE SERVER pglog FOREIGN DATA WRAPPER file_fdw;",,,"psql"
2020-03-28 10:37:06.262 UTC,,,585,,5e7b2062.249,8,,2020-03-25 17:12:02 CST,,0,LOG,00000,"received SIGHUP, reloading configuration files",,,,,,,,,""
2020-03-28 18:37:06.265 HKT,,,585,,5e7b2062.249,10,,2020-03-25 17:12:02 CST,,0,LOG,00000,"parameter ""log_timezone"" changed to ""Asia/Hong_Kong""",,,,,,,,,""
2020-03-28 18:37:32.799 HKT,"enterprisedb","edb",13888,"[local]",5e7f1a15.3640,29,"CREATE SERVER",2020-03-28 17:34:13 CST,6/1041,0,ERROR,42710,"server ""pglog"" already exists",,,,,,"CREATE SERVER pglog FOREIGN DATA WRAPPER file_fdw;",,,"psql"
edb=#

不過～其實 Postgres 藏了一個祕密～～
Postgres 有一個時區縮寫控制參數 timezone_abbreviations 與 Timezone Abbreviation 組合檔 /usr/edb/as12/share/timezonesets/Default。
Default 檔案裡面指名了 CST 指向 Central Standard Time，並用秒數指定了 UTC-6 offset 的值 -6x60x60 = -21600。
若我們需要設定 CST 指向 UTC+8 優先，就需要將這個值改成 +8x60x60 = 28800 。具體的值可以在樣板檔 /usr/edb/as12/share/timezonesets/Asia.txt 找到（Note：樣板檔不能直接用）-bash-4.2$ ls /usr/edb/as12/share/timezonesets/

Africa.txt   Antarctica.txt  Atlantic.txt  Australia.txt  Etc.txt     India       Pacific.txt
America.txt  Asia.txt        Australia     Default        Europe.txt  Indian.txt
-bash-4.2$ 
-bash-4.2$ grep CST /usr/edb/as12/share/timezonesets/Default 
# CONFLICT! CST is not unique
#  - CST: Central Standard Time (Australia)
#  - CST: China Standard Time (Asia)
#  - CST: Cuba Central Standard Time (America)
CST    -21600    # Central Standard Time (America)
ACST    34200    # Australian Central Standard Time
-bash-4.2$ 
-bash-4.2$ grep CST /usr/edb/as12/share/timezonesets/Asia.txt 
# CONFLICT! CST is not unique
#  - CST: Central Standard Time (Australia)
#  - CST: Central Standard Time (America)
#  - CST: Cuba Central Standard Time (America)
CST     28800    # China Standard Time
-bash-4.2$ 
-bash-4.2$ psql
Null display is "(NULL)".
Timing is on.
psql (12.2.3)
Type "help" for help.

edb=# show timezone_abbreviations ;
 timezone_abbreviations 
------------------------
 Default
(1 row)

Time: 0.995 ms
edb=#

因此，正規一點的解決方式，就是修改 Default Timezone Abbreviation 檔案。我們可以調整，或參考手冊頁面，可以使用 @OVERRIDE 覆蓋預設值（便於修改）。但是每次只要有執行檔更新（RPM、DEB。。等）就需要重設一次

[root@edb-as12 ~]# cat << EOF >> /usr/edb/as12/share/timezonesets/Default
@OVERRIDE
CST     28800    # China Standard Time
EOF
[root@edb-as12 ~]# service edb-as-12 reload

此時我們把時區設定回來（Asia/Taipei）看看

edb=# alter system set log_timezone to 'Asia/Taipei' ;
ALTER SYSTEM
Time: 66.042 ms
edb=# select pg_reload_conf();
 pg_reload_conf 
----------------
 t
(1 row)

Time: 0.948 ms
edb=# 
edb=# -- 製造一行 log
edb=# error line;
ERROR:  syntax error at or near "error"
LINE 1: error line;
        ^
Time: 0.680 ms
edb=# 
edb=# select * from pglog ;
           log_time            |  user_name   | database_name | process_id | connection_from |  session_id   | session_line_num |  command_tag  |    session_start_time     | virtual_transaction_id | transaction_id | error_severity | sql_state_code |                           message                            | detail |  hint  | internal_query | internal_query_pos | context |                        query                        | query_pos | location | application_name 
-------------------------------+--------------+---------------+------------+-----------------+---------------+------------------+---------------+---------------------------+------------------------+----------------+----------------+----------------+--------------------------------------------------------------+--------+--------+----------------+--------------------+---------+-----------------------------------------------------+-----------+----------+------------------
 28-MAR-20 17:38:59.659 +08:00 | enterprisedb | edb           |      13888 | [local]         | 5e7f1a15.3640 |                2 | CREATE SERVER | 28-MAR-20 17:34:13 +08:00 | 6/1018                 |              0 | ERROR          | 42710          | server "pglog" already exists                                | (NULL) | (NULL) | (NULL)         |             (NULL) | (NULL)  | CREATE SERVER pglog FOREIGN DATA WRAPPER file_fd;   |    (NULL) | (NULL)   | psql
 28-MAR-20 18:13:37.347 +08:00 | (NULL)       | (NULL)        |        585 | (NULL)          | 5e7b2062.249  |                5 | (NULL)        | 25-MAR-20 17:12:02 +08:00 | (NULL)                 |              0 | LOG            | 00000          | received SIGHUP, reloading configuration files               | (NULL) | (NULL) | (NULL)         |             (NULL) | (NULL)  | (NULL)                                              |    (NULL) | (NULL)   | 
 28-MAR-20 18:13:37.35 +08:00  | (NULL)       | (NULL)        |        585 | (NULL)          | 5e7b2062.249  |                7 | (NULL)        | 25-MAR-20 17:12:02 +08:00 | (NULL)                 |              0 | LOG            | 00000          | parameter "log_timezone" changed to "UTC"                    | (NULL) | (NULL) | (NULL)         |             (NULL) | (NULL)  | (NULL)                                              |    (NULL) | (NULL)   | 
 28-MAR-20 18:13:44.093 +08:00 | enterprisedb | edb           |      13888 | [local]         | 5e7f1a15.3640 |                6 | CREATE SERVER | 28-MAR-20 17:34:13 +08:00 | 6/1027                 |              0 | ERROR          | 42710          | server "pglog" already exists                                | (NULL) | (NULL) | (NULL)         |             (NULL) | (NULL)  | CREATE SERVER pglog FOREIGN DATA WRAPPER file_fdw;  |    (NULL) | (NULL)   | psql
 28-MAR-20 18:37:06.262 +08:00 | (NULL)       | (NULL)        |        585 | (NULL)          | 5e7b2062.249  |                8 | (NULL)        | 25-MAR-20 17:12:02 +08:00 | (NULL)                 |              0 | LOG            | 00000          | received SIGHUP, reloading configuration files               | (NULL) | (NULL) | (NULL)         |             (NULL) | (NULL)  | (NULL)                                              |    (NULL) | (NULL)   | 
 28-MAR-20 18:37:06.265 +08:00 | (NULL)       | (NULL)        |        585 | (NULL)          | 5e7b2062.249  |               10 | (NULL)        | 25-MAR-20 17:12:02 +08:00 | (NULL)                 |              0 | LOG            | 00000          | parameter "log_timezone" changed to "Asia/Hong_Kong"         | (NULL) | (NULL) | (NULL)         |             (NULL) | (NULL)  | (NULL)                                              |    (NULL) | (NULL)   | 
 28-MAR-20 18:37:32.799 +08:00 | enterprisedb | edb           |      13888 | [local]         | 5e7f1a15.3640 |               29 | CREATE SERVER | 28-MAR-20 17:34:13 +08:00 | 6/1041                 |              0 | ERROR          | 42710          | server "pglog" already exists                                | (NULL) | (NULL) | (NULL)         |             (NULL) | (NULL)  | CREATE SERVER pglog FOREIGN DATA WRAPPER file_fdw;  |    (NULL) | (NULL)   | psql
 28-MAR-20 23:07:01.796 +08:00 | (NULL)       | (NULL)        |        585 | (NULL)          | 5e7b2062.249  |               13 | (NULL)        | 25-MAR-20 17:12:02 +08:00 | (NULL)                 |              0 | LOG            | 00000          | received SIGHUP, reloading configuration files               | (NULL) | (NULL) | (NULL)         |             (NULL) | (NULL)  | (NULL)                                              |    (NULL) | (NULL)   | 
 28-MAR-20 23:07:01.801 +08:00 | (NULL)       | (NULL)        |        585 | (NULL)          | 5e7b2062.249  |               15 | (NULL)        | 25-MAR-20 17:12:02 +08:00 | (NULL)                 |              0 | LOG            | 00000          | parameter "log_timezone" changed to "Asia/Taipei"            | (NULL) | (NULL) | (NULL)         |             (NULL) | (NULL)  | (NULL)                                              |    (NULL) | (NULL)   | 
 28-MAR-20 23:19:38.851 +08:00 | enterprisedb | edb           |      15056 | [local]         | 5e7f66ef.3ad0 |                2 | idle          | 28-MAR-20 23:02:07 +08:00 | 6/2055                 |              0 | ERROR          | 42601          | syntax error at or near "error"                              | (NULL) | (NULL) | (NULL)         |             (NULL) | (NULL)  | error line;                                         |         1 | (NULL)   | psql
(10 rows)

Time: 1.899 ms
edb=# 


edb=# \! cat /pgdata/as12/log/edb-2020-03-28_141408.csv
2020-03-28 17:38:59.659 CST,"enterprisedb","edb",13888,"[local]",5e7f1a15.3640,2,"CREATE SERVER",2020-03-28 17:34:13 CST,6/1018,0,ERROR,42710,"server ""pglog"" already exists",,,,,,"CREATE SERVER pglog FOREIGN DATA WRAPPER file_fd;",,,"psql"
2020-03-28 18:13:37.347 CST,,,585,,5e7b2062.249,5,,2020-03-25 17:12:02 CST,,0,LOG,00000,"received SIGHUP, reloading configuration files",,,,,,,,,""
2020-03-28 10:13:37.350 UTC,,,585,,5e7b2062.249,7,,2020-03-25 17:12:02 CST,,0,LOG,00000,"parameter ""log_timezone"" changed to ""UTC""",,,,,,,,,""
2020-03-28 10:13:44.093 UTC,"enterprisedb","edb",13888,"[local]",5e7f1a15.3640,6,"CREATE SERVER",2020-03-28 17:34:13 CST,6/1027,0,ERROR,42710,"server ""pglog"" already exists",,,,,,"CREATE SERVER pglog FOREIGN DATA WRAPPER file_fdw;",,,"psql"
2020-03-28 10:37:06.262 UTC,,,585,,5e7b2062.249,8,,2020-03-25 17:12:02 CST,,0,LOG,00000,"received SIGHUP, reloading configuration files",,,,,,,,,""
2020-03-28 18:37:06.265 HKT,,,585,,5e7b2062.249,10,,2020-03-25 17:12:02 CST,,0,LOG,00000,"parameter ""log_timezone"" changed to ""Asia/Hong_Kong""",,,,,,,,,""
2020-03-28 18:37:32.799 HKT,"enterprisedb","edb",13888,"[local]",5e7f1a15.3640,29,"CREATE SERVER",2020-03-28 17:34:13 CST,6/1041,0,ERROR,42710,"server ""pglog"" already exists",,,,,,"CREATE SERVER pglog FOREIGN DATA WRAPPER file_fdw;",,,"psql"
2020-03-28 23:07:01.796 HKT,,,585,,5e7b2062.249,13,,2020-03-25 17:12:02 CST,,0,LOG,00000,"received SIGHUP, reloading configuration files",,,,,,,,,""
2020-03-28 23:07:01.801 CST,,,585,,5e7b2062.249,15,,2020-03-25 17:12:02 CST,,0,LOG,00000,"parameter ""log_timezone"" changed to ""Asia/Taipei""",,,,,,,,,""
2020-03-28 23:19:38.851 CST,"enterprisedb","edb",15056,"[local]",5e7f66ef.3ad0,2,"idle",2020-03-28 23:02:07 CST,6/2055,0,ERROR,42601,"syntax error at or near ""error""",,,,,,"error line;",1,,"psql"
edb=#