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??

score 2 · Accepted Answer · answered May 15 '11 at 19:29

2

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]

answered May 15 '11 at 19:29

growse

7,830
11
72
114

I changed it in the conf file and restarted the service. that helped – Omnipresent May 15 '11 at 19:42

score 2 · Answer 2 · edited Jan 09 '12 at 17:55

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.

postgresql not connecting after upgrading to ubuntu 11.04

2 Answers2