Database system identifier

An integer contained in the pg_control file which provides a reasonably unique identifier for the database cluster

The 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).

The database system identifier is created when the cluster is bootstrapped by initdb in the function BootStrapXLOG() in src/backend/access/transam/xlog.c.

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);

The database system identifier's level of randomness was improved in PostgreSQL 9.4 (commit 5035701e).

Obtaining the database system identifier

From PostgreSQL 9.6, the function pg_control_system() will return the 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:

  • Execute pg_controldata and extract the value "Database system identifier"
  • Initiate a replication connection to the server and issue 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)

Categories

PostgreSQL internals

See also

pg_control, pg_controldata