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.