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
 postgresql@9.1-main.service   loaded failed failed    PostgreSQL Cluster 9.1-main
 postgresql@9.4-main.service   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 postgresql@9.1-main.service

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


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 postgresql@9.1-main.service
vimdiff /etc/postgresql/9.{1,4}/main/postgresql.conf
vimdiff /etc/postgresql/9.{1,4}/main/pg_hba.conf
systemctl restart postgresql@9.4-main.service
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).

  • pg_upgrade is needed on production environments, specially when no-downtime (or almost no downtime) is required. pg_dumpall solution requires to have enough disk space to duplicate your databses, as you will have them in the old cluster and also in the new, plus the space needed to save the backup (unless you pipe the output directly to the new cluster). – EAmez Feb 4 at 10:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.