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
andcluster_name
inpostgresql.conf
. Then I happened to notice that I had two files namedpostgresql.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
setProper version None set Only cluster_name
setProper version None set Both set Proper version Only port
setNone set Socket error Only port
setOnly port
setCluster warning and socket error Only port
setOnly cluster_name
setSocket error Only port
setBoth set Cluster warning and socket error Only cluster_name
setNone set Proper version Only cluster_name
setOnly port
setProper version Only cluster_name
setOnly cluster_name
setProper version Only cluster_name
setBoth set Proper version Both set None set Socket error Both set Only port
setCluster warning and socket error Both set Only cluster_name
setSocket 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,
- 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?, - if it shouldn't, why does it work that way?,
- or even, maybe, PostgreSQL reads its configuration not regarding the numbers in the names of the directories, "12" and "17"?,
- 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.conf
s.
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 purge
ing, I have accidentally purge 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.