Database system identifier
database system identifier is an unsigned 64-bit integer contained in the
pg_control file which is meant to provide a "hopefully unique" identifier for the database cluster. This is derived from the system time when the cluster is initialized and does not contain any other information about the installation or the system it runs on. Internally the
database system identifier is used on a standby to verify that WAL files being read originate from the same system (which means all databases in a streaming replication cluster will share the same identifier).
Obtaining the database system identifier
postgres=# SELECT system_identifier FROM pg_control_system(); system_identifier --------------------- 6380220679621304217 (1 row)
Prior to 9.6 the system identifier can only be retrieved with one of the two following methods:
pg_controldataand extract the value reported by "
Database system identifier"
- Initiate a
replication connectionto the server and issue
$ 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)
Relevant code excerpt:
(...) 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);