**数据库系统标识符** 是一个无符号的 64 位整数，包含在 pg_control 文件中，旨在为数据库集群提供一个“希望是唯一的”标识符。它来源于集群初始化时的系统时间，不包含有关安装或其运行系统的任何其他信息。在内部，数据库系统标识符 用于备用服务器上验证正在读取的 WAL 文件是否源自同一系统（这意味着流式复制集群中的所有数据库将共享相同的标识符）。
获取数据库系统标识符
从 PostgreSQL 9.6 开始，函数 pg_control_system() 将返回系统标识符。
postgres=# SELECT system_identifier FROM pg_control_system(); system_identifier --------------------- 6380220679621304217 (1 row)
在 9.6 之前，系统标识符只能通过以下两种方法之一检索
- 执行
pg_controldata并提取“数据库系统标识符”报告的值。 - 启动与服务器的
复制连接并发出IDENTIFY_SYSTEM。$ psql "dbname=postgres host=localhost user=repl_user replication=1" psql (9.5.3) Type "help" for help. postgres=# IDENTIFY_SYSTEM; systemid | timeline | xlogpos | dbname ---------------------+----------+-----------+-------- 6301592708518993068 | 1 | 0/5002168 | (1 row)
源代码
数据库系统标识符是在集群由 initdb 在 src/backend/access/transam/xlog.c 中的 BootStrapXLOG() 函数中引导时创建的。
相关代码摘录
(...) uint64 sysidentifier; struct timeval tv; (...) /* * 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 is the XOR * of tv_sec and tv_usec. This is to ensure that we don't lose uniqueness * unnecessarily if "uint64" is really only 32 bits wide. 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 |= (uint32) (tv.tv_sec | tv.tv_usec);
数据库系统标识符的随机性级别在 PostgreSQL 9.4（提交 5035701e）中得到了改进。