Sample Header Ad - 728x90

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

1 vote
1 answer
2451 views
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?
Asked by TheRealChx101 (121 rep)
Jul 31, 2022, 12:20 AM
Last activity: Mar 22, 2025, 01:00 PM