Why does old postgresql.conf affect new PostgreSQL version? [closed]

Question

I'm a beginner in administering databases. I use Linux Mint. A couple of weeks ago I decided to start learning PostgreSQL.

I installed PostgreSQL 12, from Mint's repository. After some time I installed PostgreSQL 17, from the PostgreSQL's APT repository, https://wiki.postgresql.org/wiki/Apt. I did a check after the installation, and pg_config --version was saying PostgreSQL 17.0 (Ubuntu 17.0-1.pgdg20.04+1).

Now, I write a blog about my learning. Therefore, for the description of the problem I'll quote myself below.

(…) I was trying to correctly set the parameters port and cluster_name in postgresql.conf. Then I happened to notice that I had two files named postgresql.conf on my machine. One was from the previous PostgreSQL installation, version 12, and the other was from the current installation, version 17. I resolved to get to know how those two parameters and two files relate.

I was interested in which parameter in which file makes PostgreSQL work, and which not. To test, I used the command psql -U postgresql -c 'select version();'. If this command would display the proper version of PostgreSQL, that is, 17, then I would consider that the combination of parameters and files work. Otherwise I would not. The results are in the table below.

17	12	Result
None set	None set	Proper version
None set	Only port set	Proper version
None set	Only cluster_name set	Proper version
None set	Both set	Proper version
Only port set	None set	Socket error
Only port set	Only port set	Cluster warning and socket error
Only port set	Only cluster_name set	Socket error
Only port set	Both set	Cluster warning and socket error
Only cluster_name set	None set	Proper version
Only cluster_name set	Only port set	Proper version
Only cluster_name set	Only cluster_name set	Proper version
Only cluster_name set	Both set	Proper version
Both set	None set	Socket error
Both set	Only port set	Cluster warning and socket error
Both set	Only cluster_name set	Socket error
Both set	Both set	Cluster warning and socket error

"Socket error" means

psql: error: connection to server on socket (...) failed: No such file or directory
  Is the server running locally and accepting connections on that socket?

"Cluster warning" means

Warning: No existing cluster is suitable as a default target. Please see man pg_wrapper(1) how to specify one.

To be specific, one file resides in /etc/postgresql/17/main/, and the other in /etc/postgresql/12/main/.

As you can see above, there are cases when setting PostgreSQL's parameters in /etc/postgresql/12/main/postgresql.conf affects how psql works. For me, this is odd. So, my questions are,

1. shouldn't it be that whatever I'm going to put in the file /etc/postgresql/12/main/postgresql.conf, it will not affect how PostgreSQL 17 works?,
2. if it shouldn't, why does it work that way?,
3. or even, maybe, PostgreSQL reads its configuration not regarding the numbers in the names of the directories, "12" and "17"?,
4. lastly, if this may be caused by any mess I should have done on my machine, how do I know that, and how do I clean it up?

I know the simplest solution might be just to try to remove all traces of PostgreSQL and install it afresh. But the point of this question is that I want to learn.

---

Update. OK, I hope I did not make a mistake, but it seems, what @Laurenz Albe suggested in his answer, that indeed setting or not cluster_name doesn't affect how psql work. So, I'm posting a simplified version of the table below.

17	12	Result
None set	None set	Proper version
None set	Only port set	Proper version
Only port set	None set	Socket error
Only port set	Only port set	Cluster warning and socket error

---

Update. I don't know if this is relevant, but I have been restarting the server after checking each combination of settings. The command was

sudo systemctl restart postgresql.service && psql -U postgres -c 'select version();'

---

Update. OK, I've discovered there is a utility pg_lsclusters. I'm posting below what the command pg_lsclusters outputs, maybe it will help.

Ver Cluster Port Status                Owner    Data directory              Log file
12  main    5432 down,binaries_missing postgres /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log
17  main    5432 online                postgres /var/lib/postgresql/17/main /var/log/postgresql/postgresql-17-main.log

Now, maybe it's important to note that I was setting the port to 5433 in postgresql.confs.

---

Update. I've prepared another version of the simplified version of the table. This one shows information about the statuses of the clusters. I was setting port to 5433 in /etc/postgresql/12/main/postgresql.conf and /etc/postgresql/17/main/postgresql.conf. To test, I used the command

sudo systemctl restart postgresql.service && grep 'port =' /etc/postgresql/1?/main/postgresql.conf && pg_lsclusters

v12	v17	v12's status	v12's port	v17's status	v17's port
Port not set	Port not set	down,binaries_missing	5432	online	5432
Port not set	Port set	down,binaries_missing	5432	online	5433
Port set	Port not set	down,binaries_missing	5433	online	5432
Port set	Port set	down,binaries_missing	5433	online	5433

---

Update. The socket in the "socket error" I've mentioned above is /var/run/postgresql/.s.PGSQL.5432.

---

Update. I've run sudo find / -name '*.s.PGSQL.*' and the only two files found are

/run/postgresql/.s.PGSQL.5433
/run/postgresql/.s.PGSQL.5433.lock

I'm not acquainted with Linux sockets, but let me guess the .lock file has nothing to do with the problem. Therefore clearly I can see that /run/postgresql/.s.PGSQL.5433 does not correspond to /var/run/postgresql/.s.PGSQL.5432, expected by psql. But I don't know why.

Also I might add that the command

postgres -c 'select version();' -h /run/postgresql -p 5433

succeeds, and without an error shows the right version, that is, 17.

---

Update. I don't know if this is relevant, but https://dba.stackexchange.com/a/308800/316928 suggested to me I might be using a wrong version of psql.

Although I don't know PostgreSQL that good, this sounds reasonable. ll $(which psql) gives /usr/bin/psql -> ../share/postgresql-common/pg_wrapper*, which is a script. I've also found a binary under /usr/lib/postgresql/17/bin/psql. But it tries to connect to the same socket as psql, so I assume either it is itself called by the script, or it works the same in my case as something the script calls.

---

Update. Another thing https://dba.stackexchange.com/a/308800/316928 suggested to me is to think about the parameter unix_socket_directories. https://www.postgresql.org/docs/current/runtime-config-connection.html says that this parameter "[s]pecifies the directory of the Unix-domain socket(s) on which the server is to listen for connections from client applications". In my postgresql.conf for v17 it has been set to '/var/run/postgresql'. When I changed it to '/run/postgresql', that is, to the directory the socket is in fact in, it doesn't make a difference for psql. It still looks for the socket in /var/run/postgresql.

---

Update. After some tests I shall conclude, without establishing another table, that the socket which pqsl displays the socket error with depends on the value of unix_socket_directories when it's set in postgresql.conf of v12, and does not depend on the value of this parameter when it's set in postgresql.conf of v17. Thus I infer that /usr/share/postgresql-common/pg_wrapper examines only v12's postgresql.conf. The question shall be, why not v17's.

The above is also interesting when we consider that man pg_wrapper says that "[f]or psql, pg_archivecleanup, and pg_isready, pg_wrapper will always use the binary from the newest PostgreSQL version installed". If "using a binary" means connecting to a cluster, then /usr/share/postgresql-common/pg_wrapper should connect to v17, the newest version. And assumed it really does so under the hood, why should it then read v12's configuration? Additionally I note that the name of the directory postgresql-common suggests to me that the same pg_wrapper shall be used for every cluster I should have.

---

Update. apt search postgresql-12 shows that this package is removed, but its configuration remains. However, I don't know whether this is related or not to the actual pg_wrapper's behavior.

---

Update. During today's apt purgeing, I have accidentally purged the package postgresql-12. Then a screen showed up, and I select there not to remove configuration, as far as I can remember the question. Lucky me, postgresql.conf of v12 is still there, even though, confusingly to me, apt purge seemed to have processed it successfully. I'd rather not test all the above again if I'd not have a specific reason to do it. For now I just assume all the errors, et cetera, should be the same.

Answer 1

Your question is pretty unclear...

The configuration for your v12 instance doesn't affect your v17 instance at all.

However, if you try to have both instances running at the same time on the same machine, they may affect each other. Some examples:

• you cannot use the same port for both instances; the second instance you try to start will fail

• if both instances have large shared_buffers, starting the second one may make you go out of memory

However, cluster_name has no impact at all.