postgresql not connecting after upgrading to ubuntu 11.04

Question

I recently upgraded to Natty (Ubuntu 11.04) and now my postgresql does not seem to connect.

I am using pgadmin3 which throws the following error:

could not connect to server: Connection refused Is the server running on host "localhost" and accepting TCP/IP connections on port 5432? could not connect to server: Connection refused Is the server running on host "localhost" and accepting TCP/IP connections on port 5432?

typing psql on the terminal gives the following error:

psql: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

following are the files under var/run/postgresql/

total 8 
drwxrwsr-x  2 postgres postgres 100 2011-05-15 14:35 . 
drwxr-xr-x 19 root     root     720 2011-05-15 14:35 ..
-rw-------  1 postgres postgres   5 2011-05-15 14:35 8.4-main.pid 
srwxrwxrwx  1 postgres postgres   0 2011-05-15 14:35 .s.PGSQL.5433
-rw-------  1 postgres postgres  34 2011-05-15 14:35 .s.PGSQL.5433.lock

netstat shows

sudo netstat -lp | grep postgresql 
unix  2      [ ACC ]     STREAM     LISTENING     7763     1025/postgres   /var/run/postgresql/.s.PGSQL.5433

I believe the problem is that its looking to start on 5432 but it should be starting in 5433 but how do I change this??

Answer 1

The default port for Postgres is 5432. Therefore, all tools (pgadmin3, psql) will default to using this port unless you specify otherwise.

You have Pg running on port 5433. Therefore to connect with any tool, you'll have to manually specify the port number. On pgadmin3, it's a textbox in the connection GUI somewhere (if I remember right). psql takes the --port option as follows:

psql --port 5433 [database]

Answer 2

I just upgraded PostgreSQL and ran into the same problem. The problem was my new postgres server running version (9.1) was installed in parallell with my old server running version (8.4). I guess the default configuration is to run on an alternative port (5433).

So my solution was to make sure the new server ran on 5432.

So edit your postgres configuration

sudo -u postgres gedit /etc/postgresql/9.1/main/postgresql.conf

Make sure that the port is 5432:

port = 5432

And restart the postgres server by issuing :

sudo -u postgres /etc/init.d/postgresql restart

An untested alternative would be to first uninstall the old postgres server, before installing the new version.

Answer 3

psql reads /etc/postgresql-common/user_clusters to know which /etc/postgresql/<n>/postgresql.conf to read the PostgreSQL port from.

There might be a bug in the PostgreSQL upgrade script because on one of my install this /etc/postgresql-common/user_clusters only had the old stopped PostgreSQL install entry. Thus psql attempted to the old PostgreSQL port instead of the new postgresql install one.

You can fix this issue by editing /etc/postgresql-common/user_clusters and replacing the VERSION value in this file to be the same as your current /etc/postgresql/<n>/ install.

It is normal for there to be two postgresql installs at the same time on upgrade. This is required so you can migrate the databases from on postgresql install to the other newer one (by default on upgrade the new postgresql install will have empty databases). So you cannot remove the old one to get psql to not attempt to read the old postgresql install configuration.