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)  

digoal’s 大量PostgreSQL文章入口