数据库系统标识符 是一个存储在 pg_control 文件中的无符号64位整数，旨在为数据库集群提供一个“有希望唯一的”标识符。它源自集群初始化时的系统时间，不包含有关安装或其运行系统的任何其他信息。在内部，数据库系统标识符 用于备用服务器，以验证正在读取的 WAL 文件是否来自同一系统（这意味着流复制集群中的所有数据库将共享相同的标识符）。
获取数据库系统标识符
从 PostgreSQL 9.6 开始，函数 pg_control_system() 将返回系统标识符。
postgres=# SELECT system_identifier FROM pg_control_system(); system_identifier --------------------- 6380220679621304217 (1 row)
在 9.6 之前，只能通过以下两种方法之一检索系统标识符：
- 执行
pg_controldata并提取由“Database system identifier”报告的值。 - 向服务器发起一个
复制连接，并发出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）。