Locating the database's system identifier
Each database server has a system identifier assigned when the database is initialized (created). The server identifier remains the same if the server is backed up, cloned, and so on.
Many actions on the server are keyed to the system identifier, and you may be asked to provide this information when you report a fault.
In this recipe, you will learn how to display the system identifier.
Getting ready
You need to connect as the Postgres OS user, or another user with execute privileges on the server software.
How to do it…
In order to display the system identifier, we just need to launch the following command:
pg_controldata <data-directory> | grep "system identifier" Database system identifier: 7015545877453537036
Note that the preceding syntax will not work on Debian or Ubuntu systems, for the same reasons explained in relation to initdb in the Locating the database server files recipe. However, in this case, there is no postgresql-common alternative, so if you must run pg_controldata, you need to specify the full path to the executable, as in this example:
/usr/lib/postgresql/14/bin/pg_controldata $PGDATA
Tip
Don't use -D in front of the data directory name. This is the only PostgreSQL server application where you don't need to do that.
How it works…
The pg_controldata utility is a PostgreSQL server application that shows the content of a server's control file. The control file is located within the data directory of a server, and it is created at database initialization time. Some of the information within it is updated regularly, and some is only updated when certain major events occur.
The full output of pg_controldata looks like the following (some values may change over time as the server runs):
pg_control version number: 1300 Catalog version number: 202107181 Database system identifier: 7015545877453537036 Database cluster state: in production pg_control last modified: Tue 05 Oct 2021 12:46:26 BST Latest checkpoint location: 0/16F2EC0 … (not shown in full)
Tip
Never edit the PostgreSQL control file. If you do, the server probably won't start correctly, or you may mask other errors. And if you do that, people will be able to tell, so fess up as soon as possible!
