0

Context

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.

Question

  • if system_identifier is 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/data from my local server ?

[edit] 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.

7
  • I don't understand the question. The server that runs on a certain data directory can only be determined by running SHOW data_directory; and comparing the result. Is that what you are looking for? Nov 16, 2022 at 20:21
  • @LaurenzAlbe: say you have several servers and containers which all store their databases in /var/lib/pgsql/data, comparing whether [net connection] > show data_directory with a local path won't do you any good, you could still be connected to the replication server, or another server ... I'm looking for a way to identify with some level of confidence "the database on that file system is the same as the one served through that IP / port"
    – LeGEC
    Nov 17, 2022 at 3:58
  • in our case, it is just a main server and a replica server, but given that wal-g is a generic tool, I was curious about how to establish "I'm connected to the correct database" with more certainty.
    – LeGEC
    Nov 17, 2022 at 4:23
  • 1
    If you want to distinguish primary and standby, the system identifier won't help you, since it is the same. Your question still confused me. Why do you want to distinguish them? What is the actual objective or problem you are trying to solve? Nov 17, 2022 at 7:00
  • @LaurenzAlbe: I'm looking at the code of a backup implementation, which uses the sequence SELECT pg_backup_start(), [copy files from data/ directory], SELECT pg_backup_stop(). As a sanity check, they try to establish that the data/ is actually the one served through the postgresql connection. At the moment : they check if the path to data/ on disk is the same as what they see through the pgsql connection (SHOW data_directory). I just realized that, if you are comparing paths from two distinct file systems, this won't help you [...]
    – LeGEC
    Nov 27, 2022 at 18:22

2 Answers 2

1

System identifier you retrieving using SELECT system_identifier FROM pg_control_system(); is equal to first 8 bytes of global/pg_control file of your cluster.

[postgres@admin200 ~]$ psql -tc 'select to_hex(system_identifier) from pg_control_system();'
 634d08977b5aa882

[postgres@admin200 ~]$ head -c8 /var/lib/pgsql/15/data/global/pg_control | od -An -tx1
 82 a8 5a 7b 97 08 4d 63
2

PostgreSQL comes with a binary for reading the control data. I don't know if that binary will automatically be in your PATH or not, it would depend on your package manager and your installation method.

pg_controldata -D  /data/postgresql/data |fgrep 'Database system identifier'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.