Upgrading Postgre 8.4 to 9.1 on Ubuntu (Turnkey Linux)

Question

I am trying to get Postgre 9.1 to work because I need the features available in the 2.0 version of PostGIS. Turnkey Linux offers a Postgre 8.4 appliance which works great and even has PostGIS built in... but it's only version 1.2.

I followed this guide which basically showed how I could install 9.1 via apt-get, which seems to have worked: http://netadminlinux.blogspot.com/2011/11/installing-postgresql-91-on-ubuntu-1104.html

My problem now, is that while both 8.4 and 9.1 are installed, I only seem to be able to access 8.4. I installed pgadmin3, but I have no idea how to access it since the default Turnkey Appliance is set up to host pgadmin2.

in /etc/init.d/ there are two entries, postgres-8.4. and postgres. I believe the second is the 9.1 instance, and I can even start it up with /etc/init.d/postgres start. I tried shutting down the 8.4 instance, and then starting the 9.1... but when I try to use command line psql to access the server I get the following message:

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

What do I need to do to my new instance of 9.1 to get it to run? What are the post-installation steps that I seem to have missed?

Answer 1

when I installed 2 instances of PG (8.3 vs 9.1) I could not connect the PG Admin version that came with 8.3, to the 9.1 server instance

so be careful you did not open the PG Admin version that came with 8.x, and you are not trying to use it to connect to 9.x.. because I got the same or similar msg

psql: could not connect to server: No such file or directory Is the server running locally and accepting connections

I changed my icon for the old PG Admin shortcut so I could temporarily know which I was working with. Using the PG Admin that came with 9.1 i was able to connect to it no problem, and once all data was migrated I made sure I uninstalled the proper PG and PGAdmin programs

Answer 2

If you have both installed, you should have two configuration files:

/etc/postgresql/8.4/main/postgresql.conf
/etc/postgresql/9.1/main/postgresql.conf

Each of them will have a different port = directive (typically, 5432 and 5433).

If version 9.1 is configured with 5433, you may have to configure the client to use 5433 explicitly (either for the port with a TCP connection or as part of the unix socket name).

If you want 9.1 to use the default ports, change it to be 5432 and the other to be 5433 instead, stop both services and start them up again. (Note that this may affect other applications that were using version 8.4 on port 5432, if any.)

Answer 3

You could try removing old postgres:

 apt-get purge postgresql-8.4