执行pg_controldata命令获得Database system identifier
pg_controldata | grep 'Database system identifier'
结果:
Database system identifier: 6732746755019295031
参考链接:PostgreSQL学习系列—pg_control之Database system identifier - 简书 (jianshu.com)
(1)数据库标识符(sysid):
Database system identifier(数据库系统标识符,内部提示为sysid),用于唯一识别Database Cluster,启动、备份或者恢复等过程中会校验pg_control中的Database system identifier与wal文件中的sysid是否相同。
sysid生成的算法为:
uint64 sysidentifier;(二进制是64位的)
/*
* Select a hopefully-unique system identifier code for this installation.
* We use the result of gettimeofday(), including the fractional seconds
* field, as being about as unique as we can easily get. (Think not to
* use random(), since it hasn't been seeded and there's no portable way
* to seed it other than the system clock value...) The upper half of the
* uint64 value is just the tv_sec part, while the lower half contains the
* tv_usec part (which must fit in 20 bits), plus 12 bits from our current
* PID for a little extra uniqueness. A person knowing this encoding can
* determine the initialization time of the installation, which could
* perhaps be useful sometimes.
*/
gettimeofday(&tv, NULL);
sysidentifier = ((uint64) tv.tv_sec) << 32;
sysidentifier |= ((uint64) tv.tv_usec) << 12;
sysidentifier |= getpid() & 0xFFF;
注:tv_sec 指秒 tv_usec 指微秒,ts 指时间戳
如果你想等待整整几秒钟,你只需要设置tv_sec.如果你想等一段时间,你可以设置tv_usec.如果要等待4.5秒,则将它们都设置为适当的值(分别为4和500000)
struct timeval
{
__time_t tv_sec; /* Seconds. */
__suseconds_t tv_usec; /* Microseconds. */
};
tv_sec = ts / 1000;
tv_usec = (ts % 1000) * 1000;
结构如下:
0-31位,32-51位,52-63位分别代表不同的含义。
pg_controldata显示 sysid时,基于跨平台展示的考虑,采用 char进行了转换:
/*
* Format system_identifier and mock_authentication_nonce separately to keep platform-dependent format code out of the translatable message string.
*/
snprintf(sysident_str, sizeof(sysident_str), UINT64_FORMAT,
ControlFile->system_identifier);
(2)推断数据库簇创建时间:
基于这种机制,可以利用 system identifier来推断出数据库簇创建时间:
SELECT to_timestamp( (( 6548580191788017147 >> 32 ) & ( 2^32 -1 )::bigint) );
结果:
to_timestamp
------------------------
2018-04-26 10:35:41+08
分析这个计算过程:
select (6548580191788017147>>32);
结果: 1524710141(秒)
select (6548580191788017147>>12)&(2^20-1)::bigint;
结果: 76530(毫秒小数位)
时间戳数值:1524710141.76530
select to_timestamp( 1524710141.76530 );
结果:
to_timestamp
-----------------------------
2018-04-26 10:35:41.7653+08