Using postgresql, I would like a way to make sure that the server I am talking with over a pgsql connection is the one that serves a specific
/path/to/data/ directory on the local server.
I know there is a
system_identifier value I an get from a
SELECT system_identifier FROM pg_control_system() query on the server, I would like to compare this with a value read from files on disk.
The use case is in wal-g : for some reason, their delta backup algorithm has to access the postgres files on disk while running some actions through a connection with the server, and their way to check that both are coherent is to compare the
data/ path provided by the user with the
SHOW data_directory read from the postgresql connection.
However, the paths may well match even if postgresql is hosted on a different server -- e.g: if the data path looks like
/data/postgresql/data, it may very well exits on server1 and server2.
system_identifieris the way to go : is there a documented, stable way to read it directly from files on disk ?
- is there another simple way to check that "the server I'm talking to on [ip:port]" is the one that serves
/path/to/datafrom my local server ?
 it turns out
system_dentifier is not a value that identifies uniquely a server -- for example: our hot standby database has the same
system_identifier as our main database. So
system_identifier doesn't fit the bill for: identify one specific postgresql instance.
The answers to the "how do I get system_identifier from disk ?" part are correct.