Upgrade to Ubuntu 10.04 broke my Postgres – “Could not Connect to Server” in Pgadmin

I upgraded last week to Ubuntu 10.04, and just noticed today that my development environment was broken (yeah yeah, it took a week to notice, give me a hard time about not spending enough time coding). I looked into it, and found a weird problem caused by the upgrade – perhaps if you are reading this you have that problem too. My Postgres database was apparently not operating.

Postgres 8.4 Upgrade

I did notice that the Ubuntu 10.04 upgrade was taking me from Postgresql 8.3 to 8.4. I figured that should be fine. And it is, but it caused some downstream problems.

First off, put away any notion that the upgrade does an automatic migration of your databases from 8.3 to 8.4. Ubuntu is good, but not that good. You will have to manually migrate your databases yourself. I imagine you should dump all your databases before you do your upgrade, as the upgrade does remove 8.3 from /usr/lib/postgresql. Since I could rebuild my databases, I didn’t have to worry about migration – but do some research ahead of time if you do.

This was one issue – my dev databases were not migrated into 8.4. I needed to rebuild them, and recreate our dev user in 8.4. But before I could do that…

Pgadmin Could Not Connect

A major roadblock was that I could not connect to Postgres using Pgadmin, even as the ‘postgres’ user. In fact, I couldn’t seem to get network connectivity to Postgres at all. What is going on?

Server doesn't listen
The server doesn't accept connections: the connection library reports could not connect to server: Connection refused Is the server running on host "127.0.0.1" and accepting TCP/IP connections on port 5432?

To troubleshoot this, I made sure that the processes were running:

sudo /etc/init.d/postgresql-8.4 restart

and then made sure it stayed started:

ps –ef | grep postgres

and also checked the logs:

/var/log/postgresql$ cat postgresql-8.4-main.log

But everything seemed to be kosher. I was also able to use psql from the command line. What is going on, I still can’t connect from Pgadmin? An invocation of netstat is what solved it:

netstat -ln | grep 5432

That should have returned some lines like:

tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN    
tcp6       0      0 ::1:5432                :::*                    LISTEN    
unix  2      [ ACC ]     STREAM     LISTENING     5623     /var/run/postgresql/.s.PGSQL.5432

but it returned nothing. Eh? 5432 is the default port for postgresql, and the port I use in my Pgadmin connections. It should be there.

I then looked at the postgres server configuration in  /etc/postgresql/8.4/main/postgresql.conf and found the problem:

port = 5433

For some reason the upgrader installed 8.4 on port 5433 instead of 5432. During the upgrade it perhaps needs to run 8.3 and 8.4 at the same time, and therefore puts them on different ports. But after upgrade, shouldn’t it switch 8.4 back to the default port?

Its an easy fix:

sudo gedit postgresql.conf   (change 5433 to 5432)

sudo /etc/init.d/postgresql-8.4 stop

sudo /etc/init.d/postgresql-8.4 start

Note that I first tried just restart, but found that the port change did not take effect. I had to do a stop and start explicitly.

I am now back up and running again with Postgres 8.4. Joy.

Technorati Tags: ubuntu,postgresql