Postgresql – clusterdb stopped working for PostgreSQL database in Ubuntu 12.04 server

linuxpostgresqlpostgresql-9.2SecurityUbuntu

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?

Best Answer

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

Related Question