How to recover Postgresql databases from an older version

Question

I was running for many years Davical on a Debian server (Debian version 11.6 at the moment) to have access to my calendar from many different devices. That application uses PostgreSQL to store its data. I was tinkering on the server when I saw that there were two older versions of PostgreSQL (9.6 and 11) that were still installed while version 13 was already installed. So I figured, I could simply delete these versions without any problem. (How foolish of me)

I saw that I could not synchronize anymore from Evolution or from my phone (using CalDAV) . I could not also access Davical's admin web interface. I was able to track down that Davical (or PDO to be more precise) was trying to access a PostgreSQL using a Unix socket at /var/run/postgresql/.s.PGSQL.5432 but I saw that only /var/run/postgresql/.s.PGSQL.5433 existed. I have found the configuration file (/etc/postgresql/13/main/postgresql.conf) where I could tell PostgreSQL to start listening on port 5432 instead of port 5433.

But I could still not access it.I saw that there were some configuration left from the older PostgreSQL versions I had removed. So I thought that maybe there was something still blocking PostgreSQL from running from those ports. So I purged those two application versions.

It worked but now in the logs of PostgreSQL (/var/log/postgresql/postgresql-13-main.log), I saw an error message saying "davical_app@davical FATAL: le rôle « davical_app » n'existe pas" ("davical_app@davical FATAL: the role "davical_app" does not exist`)

When I used psql to see if I could see the database was intact I received an error message:

postgres$ psql davical
psql: erreur : FATAL:  la base de données « davical » n'existe pas

(which mean that database "davical" doesn't exist)

I went and saw that there are still files in in /var/lib/postgresql/11/main and /var/lib/postgresql/9.6/main but aptitude does not give me the option to reinstall postgresql-9.6 or postgresql-11.

My questions:

• Is there a way for PostgreSQL to import the databases from an earlier version?
• Is there a way to export a PostgreSQL database without having that version installed?
• Is there a way to reinstall an old version of a Debian package?

No I do not have any backup. Yes, I know this looks bad. Is there, at least, any way to recover all my appointments and tasks?

Answer 1

Finally what I did is:

1. I created an unprivileged LXC system container running Debian Buster on another computer.
2. In this container, I installed PostgreSQL 11.
3. I copied the content of these folders from the production server to this container :
   • /etc/postgresql/11/main
   • /var/lib/postgresql/11/main
4. I used pg_dump in the container to create a dump of the database
5. On the production server, I used psql with the database dump to recreate the database

And it looks like it's working. But still I'm going to setup automated backups to prevent to have to do something like that in the future.

Answer 2

When you upgrade to a new PostgreSQL version, data is not migrated – you will need to do that manually.

Ideally, you have the old and new PostgreSQL version installed alongside each other. In that case:

• Run pg_lsclusters to list all clusters in the system. You should see your existing cluster on the old version. Upon installation, PostgreSQL may have created a default cluster in the default version.
• If PostgreSQL has created a cluster for the new version, delete it with pg_dropcluster --stop NEW_VERSION main.
• Upgrade the old cluster with pg_upgradecluster OLD_VERSION main (if your cluster has a different name instead of main, change it accordingly).
• pg_lsclusters should now show your migrated cluster with the new version, alongside the old one.
• You can now delete the old cluster with pg_dropcluster, then uninstall the old version.

PostgreSQL also has prebuilt binaries for the most common Linux distributions, see https://wiki.postgresql.org/wiki/Apt. However, since different choices of compile options may result in incompatible data formats, binaries from the maintainers may not be able to read binaries from some distributions.

One point worth considering would then be switching to PostgreSQL binaries from the PostgreSQL repos rather than your distro‘s. That will decouple your PostgreSQL installation from your distro, give you a wide choice if versions to install (10 is still available from the repo, the archives have versions down to 8.2) and avoid unpleasant surprises with incompatible data formats.

Another possible (and official) migration path is to create a backup while still on the old version and restore from that.