clusterdb stopped working for PostgreSQL database in Ubuntu 12.04 server

Question

After a fresh PostgreSQL install (version 9.2) on my Ubuntu server 12.04, clusterdb utility stopped working. I used the same config files as I used with version 9.1.

Below is the actual command:

clusterdb -v -h localhost -p <port#> -U postgres -d <dbname>

And this is the response that I'm getting:

clusterdb: could not connect to database <dbname>: could not connect to server: Connection refused
 Is the server running on host "localhost" (127.0.0.1) and accepting
 TCP/IP connections on port <port#>?

I'm able to connect to the server locally and from outside. I can also use another utilities such as pg_dump with no problems.

My pg_hba.conf file below:

# Database administrative login by Unix domain socket
local all postgres peer
# TYPE DATABASE USER ADDRESS METHOD "local" is for Unix domain socket connections only
local all all peer
# IPv4 local connections:
host all all 127.0.0.1/32 md5
# IPv6 local connections:
host all all ::1/128 md5
# Allow replication connections from localhost, by a user with the replication privilege.
local all postgres md5
host all all x.x.x.x/x md5 #my network
host all all x.x.x.x/x md5 #internal ip of the server

Are there any security changes were implemented in PostgreSQL 9.2 vs 9.1 that may prevent clusterdb to work properly?

• Daniel Vérité: See what pg_lsclusters says, and netstat -tln for the addresses and ports that are listened to.
• Timka: @DanielVérité: netstat -tln showed that postgres was listening on that ipdess/port

Answer 1

It looks like something went wrong when you upgraded. You have to check that the port and listen_addresses are correct. Perhaps the listen_address changed and the instance isn't listening on the host anymore?

The reason that pg_dump and the other tools are still working is probably because they're connecting over the unix socket instead over TCP/IP. clusterdb should also be able to do that if you just leave out the -h and -p options.

The pg_hba.conf is not relevant because your client can't connect to the server at all. You can verify that the server is listening on the expected host and port with telnet <host> <port>.

http://www.postgresql.org/docs/9.2/static/runtime-config-connection.html

• Timka: Thank you! removing 'h' and 'p' did help with this issue. I also had to edit pg_hba.conf file and change local all postgres peer to local all postgres md5