Postgresql – Botched migration from 9.4 to 9.5 and I’m not sure how to fix it

migrationpostgresql

For reasons I can't totally explain, I had both 9.4 and 9.5 installed, which was giving me a headache as I tried to enable PostGIS, which was only installed for 9.5.

I used Synaptic to uninstall postgresql-9.4, and restarted my server with sudo service postgresql start but now I'm finding that I have a bunch of issues:

PGAdmin can't connect. I'm connecting over localhost on port 5432 — that wouldn't have changed. The connection error says:

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 "localhost" (127.0.0.1) and accepting TCP/IP connections on port 5432?

In addtion, psql can't see my old databases, and it doesn't recognize any of the roles that I had set up.

I feel like I'm missing something basic here.

Best Answer

Since you had 2 instances installed, the first (I guess 9.4) was using the default 5432 port so the 2nd got another port (probably 5433). When you uninstalled the first instance, the 2nd doesn't know anything about it and still uses the 5433 port.

You can use pg_lsclusters to find what instances have been installed, and some more details like whether the are running or not, their ports, their data directories, etc. Example:

ian@jupiter:~$ pg_lsclusters
Ver Cluster Port Status Owner    Data directory                 Log file
9.3 main    5437 online postgres /var/lib/postgresql/9.3/main   /var/log/postgresql/postgresql-9.3-main.log
9.3 test93  5438 down   postgres /var/lib/postgresql/9.3/test93 /var/log/postgresql/postgresql-9.3-test93.log
9.4 main    5435 online postgres /var/lib/postgresql/9.4/main   /var/log/postgresql/postgresql-9.4-main.log
9.5 main    5432 online postgres /var/lib/postgresql/9.5/main   /var/log/postgresql/postgresql-9.5-main.log
9.5 repl    5439 online postgres /var/lib/postgresql/9.5/repl   /var/log/postgresql/postgresql-9.5-repl.log
9.5 stage   5433 online postgres /var/lib/postgresql/9.5/stage  /var/log/postgresql/postgresql-9.5-stage.log
9.6 main    5434 online postgres /var/lib/postgresql/9.6/main   pg_log/postgresql-%Y-%m-%d_%H%M.log
9.6 repl    5440 online postgres /var/lib/postgresql/9.6/repl   /var/log/postgresql/postgresql-9.6-repl.log
10  main    5441 online postgres /var/lib/postgresql/10/main    /var/log/postgresql/postgresql-10-main.log
ian@jupiter:~$

So the solution is pretty simple. Tell pgAdmin to connect to port 5433 or whichever port your 9.5 has been setup to listen to.

Alternatively, change the configuration of your 9.5 (second and now only one) instance to use port 5432. You need to edit the postgresql.conf file and restart it.

Related Question