How to recover Postgresq data from $PGDATA/base files after system crash

Question

I was working on my Ubuntu machine and left it on to grab food. Upon returning, I found it had shut itself down. It was not power failture because I have a home backup system and lights never go off in my house.

When I turned it on, it went straight to Busybox because apparently the fsck tool had somehow moved the entire contents of the root partition and everything else to lost+found on my laptop's primary drive, which is an SSD.

So I backed up the lost+found directory to an external HDD and installed Ubuntu 22, from Ubuntu 20. I did not lose personal data because I had my /home directory on my secondary HDD. However, everything else was on the same partition on the SSD.

So, after perusing through the lost+found files, I was able to extract all the files from /lib/postgresql/. Now because Postgresql uses OIDS, unlike mysql which uses names, I had to figure out the databases based on the information on this website here https://www.postgresql.fastware.com/blog/how-postgresql-maps-your-tables-into-physical-files

For reference, I was able to recover MySQL tables since they simply use table names. With PostgreSQL however, I'm not sure if just copying the $PGDATA/base/ files will work given the information mentioned on that website.

Is it possible to extract the data (through some tool) from the base files or to re-import them back into an active cluster/instance?

Answer 1

It turns out it was a simple solution. As simple as copying the entire directory to the $PGDATA location

This is what I did

1. Install same version of PostgreSQL
2. Find out where the data directory is by querying show data_directory;
3. Stop Postgresql service sudo sytemctl stop postgresql@xx-main, xx being version number
4. Copy backed up postgresql/ directory to directory obtained in step 2.
5. Start Postgresql service sudo sytemctl start postgresql@xx-main