PostgreSQL pg_backup_start_time() CST 时区转换 问题

PostgreSQL的物理备份方法之一 ：
在使用pg_start_backup()函数新建备份点后，用户可以开始拷贝PG的数据文件。

postgres=# select pg_start_backup('a'),now();
 pg_start_backup |              now
-----------------+-------------------------------
 0/50000028      | 2016-05-06 11:03:30.917509+08
(1 row)

调用pg_start_backup后，会创建一个检查点，同时在$PGDATA中新建一个backup_label文件。
里面包含了START TIME的信息，是创建完检查点后的时间。

START WAL LOCATION: 0/50000028 (file 000000010000000000000014)
CHECKPOINT LOCATION: 0/50000028
BACKUP METHOD: pg_start_backup
BACKUP FROM: master
START TIME: 2016-05-06 11:03:33 CST
LABEL: a

但是，使用pg_backup_start_time得到的时间与之不符。

postgres=# select pg_backup_start_time();
  pg_backup_start_time
------------------------
 2016-05-07 01:03:33+08
(1 row)

原因分析，首先我们要看看pg_backup_start_time的代码

postgres=# \df+ pg_backup_start_time
                                                                                         List of functions
   Schema   |         Name         |     Result data type     | Argument data types |  Type  | Security | Volatility |  Owner   | Language |     Source code      |          Description
------------+----------------------+--------------------------+---------------------+--------+----------+------------+----------+----------+----------------------+--------------------------------
 pg_catalog | pg_backup_start_time | timestamp with time zone |                     | normal | invoker  | stable     | postgres | internal | pg_backup_start_time | start time of an online backup
(1 row)

代码如下

/*
 * Returns start time of an online exclusive backup.
 *
 * When there's no exclusive backup in progress, the function
 * returns NULL.
 */
Datum
pg_backup_start_time(PG_FUNCTION_ARGS)
{
        Datum           xtime;
        FILE       *lfp;
        char            fline[MAXPGPATH];
        char            backup_start_time[30];

        /*
         * See if label file is present
         */
        lfp = AllocateFile(BACKUP_LABEL_FILE, "r");
        if (lfp == NULL)
        {
                if (errno != ENOENT)
                        ereport(ERROR,
                                        (errcode_for_file_access(),
                                         errmsg("could not read file \"%s\": %m",
                                                        BACKUP_LABEL_FILE)));
                PG_RETURN_NULL();
        }

        /*
         * Parse the file to find the START TIME line.
         */
        backup_start_time[0] = '\0';
        while (fgets(fline, sizeof(fline), lfp) != NULL)
        {
                if (sscanf(fline, "START TIME: %25[^\n]\n", backup_start_time) == 1)
                        break;
        }

        / Check for a read error. /
        if (ferror(lfp))
                ereport(ERROR,
                                (errcode_for_file_access(),
                           errmsg("could not read file \"%s\": %m", BACKUP_LABEL_FILE)));

        / Close the backup label file. /
        if (FreeFile(lfp))
                ereport(ERROR,
                                (errcode_for_file_access(),
                          errmsg("could not close file \"%s\": %m", BACKUP_LABEL_FILE)));

        if (strlen(backup_start_time) == 0)
                ereport(ERROR,
                                (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
                                 errmsg("invalid data in file \"%s\"", BACKUP_LABEL_FILE)));

        /*
         * Convert the time string read from file to TimestampTz form.
         */
        xtime = DirectFunctionCall3(timestamptz_in,
                                                                CStringGetDatum(backup_start_time),
                                                                ObjectIdGetDatum(InvalidOid),
                                                                Int32GetDatum(-1));

        PG_RETURN_DATUM(xtime);
}

所以从代码可以看到pg_backup_start_time是从backup_label中获取到启动时间，并转化为带时区的时间的。

CST时间，CST同时可以代表如下 4 个不同的时区：

Central Standard Time (USA) UT-6:00
Central Standard Time (Australia) UT+9:30
China Standard Time UT+8:00
Cuba Standard Time UT-4:00

所以问题其实是出在时区转化这里：

postgres=# show timezone;
 TimeZone
----------
 PRC
(1 row)

postgres=# select timestamp '2016-05-06 11:03:33 CST';
      timestamp
---------------------
 2016-05-06 11:03:33
(1 row)

postgres=# select timestamptz '2016-05-06 11:03:33 CST';
      timestamptz
------------------------
 2016-05-07 01:03:33+08
(1 row)

PostgreSQL pg_backup_start_time应该是把CST用USA时区来处理的

postgres=# set timezone='-6';
SET
postgres=# select pg_backup_start_time();
  pg_backup_start_time
------------------------
 2016-05-06 11:03:33-06
(1 row)