Clean up conflicting postgresql installations

Question

I have a little development box which I upgraded to Debian 8.5 (to have a current apache and postgres which better match the productive servers). Now I have both Postgres 9.1 (old) and 9.4 (which I'd like to use):

$ systemctl | grep postgres
 postgresql.service            loaded active exited    PostgreSQL RDBMS
 [email protected]   loaded failed failed    PostgreSQL Cluster 9.1-main
 [email protected]   loaded active running   PostgreSQL Cluster 9.4-main
 system-postgresql.slice       loaded active active    system-postgresql.slice

The PostgreSQL 9.4 service was set automatically to serve on the non-standard port 5433, which I changed; now of course it is not possible to run both. I don't want to run both versions; I want PostgreSQL 9.4 to serve my existing databases.

I can recover to make my databases usable again like this:

$ systemctl stop postgresql@9.4-main.service
$ systemctl start [email protected]

This brings me back my databases, but of course with the outdated database engine.

How would I clean up this mess? Can I un-install version 9.1 without losing my data? Or should I export my databases and re-create them? What about system databases (postgres, template1 and the like)?

Answer 1

After some struggling, this is what I found:

There are two major possibilities mentioned in the docs:

1. Use pg_dumpall to export the data from the old service and psql to import it in the new;
2. Use pg_upgrade.

My advice is: unless you have compelling reasons not to do so, use the pg_dumpall method:

1. Export everything from the old database service (if the new version is already installed, use the new pg_dumpall binary);
2. Import everything in the new database service.
3. Stop the old database service.
4. Make sure you have all your changes to postgresql.conf and pg_hba.conf in the configuration of the new version; restart the new service if necessary.
5. Test if your applications still work.
6. Uninstall the old version.

In my case (use -p <Port> as appropriate):

sudo -s -u postgres
cd /tmp/
pg_dumpall -p 5433 > all.sql
psql -f all.sql

and, as root:

systemctl stop [email protected]
vimdiff /etc/postgresql/9.{1,4}/main/postgresql.conf
vimdiff /etc/postgresql/9.{1,4}/main/pg_hba.conf
systemctl restart [email protected]
apt-get remove postgresql-client-9.1 postgresql-9.1

The pg_upgrade method requires you to find out and specify:

• the old and new data directories (e.g. grep data_directory /etc/postgresql/9.{1,4}/main/postgresql.conf)
• the directories of the old and new binaries (e.g. dpkg --listfiles postgresql-9.1 | grep '/bin$')
• You need to start from a directory where the postgres user can write

... and then it still doesn't necessarily work (see for example pg_upgrade fails with an unspecified error).