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

1 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).

  • 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, 2021 at 10:37

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .