Recover data from Postgresql database after failed system update

Question

First, I want to make clear that this question was already asked in askubuntu, but I proceeded to delete it because I got almost no feedback. If this violates SU protocol, please let me know.

---

Welcome to my mess...

The situation is:

• Ubuntu Software was making some "important" updates (and probably a lot of them, since was easily more than 9 months since my last update).

• The update seemed to stop working (update progress bar frozen), with no explanation of what was happening. There is a notification of some error that didn't give me much information or anything to do about it, save for clicking on the "Report" button.

• After around 8 hours of using the PC and hoping that the issue would resolve itself, I note that the process bar is still frozen. I cross my fingers and shut down the computer.

• Now laptop doesn't boot on Ubuntu (Still has Windows, didn't try to boot that, but I guess it still works).

• Big and important Postgresql database is stored in laptop, on /var/lib/postgresql/9.5/main (yeah, I was really tired the day before when I shut down the laptop, didn't anticipate this... really stupid thing to do; don't do this at home kids).

• Made a bootable usb stick with Ubuntu 16.04 live, with the intention of accessing my files and make a backup, before re-installing Ubuntu.

• Can't backup mentioned database (meaning, can copy/paste the folder with Nautilus or via terminal+sudo), it says the contents of /var/lib/postgresql/9.5/main are unreadable (I don't have permissions). With my home directory there are fewer problems and I manage to make a backup (with a couple of unimportant exceptions).

What can I do about this? Is it possible to copy the files from the database to make a backup? Furthermore, how can I access this database afterwards?

Thanks for your time...

---

Update: I succesfully backed up my files using sudo /bin/bash and then cp, as davidgo suggested (saved them into an external hard drive).

Then I installed ubuntu from scratch in my PC, installed posgresql-9.5 (adding the Postgres PGDG repo mentioned by davidgo), and copied the files to the same original location (using cp -rnP /pathto/backupfolder /var/lib/postgresql/9.5/main) and changed the ownership to "postgres".

Now my problem is that I cannot start the postgresql client (psql). This is the command line output:

user@machine:~$ psql
psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

Looking up the documentation, I found that I could use pg_ctl to start the server, but that in Ubuntu, is expected that I use pg_ctlcluster instead. Ok, so I try this:

user@machine:~$ su - postgres
Password: 
postgres@machine:~$ pg_ctlcluster 9.5 main status
pg_ctl: server is running (PID: 1034)
/usr/lib/postgresql/9.5/bin/postgres "-D" "/var/lib/postgresql/9.5/main" "-c" "config_file=/etc/postgresql/9.5/main/postgresql.conf"

I am a rather clumsy PostgreSQL user, I can tell, so this is probably something real simple that I cannot see right now. Again, any help would be more than wecome!

Answer 1

In order to get this working, the steps would be:

1. Install a new copy of UBUNTU - onto a new drive, so as not to overwrite the postgres files. Install Postgres 9.5 on the new instance (You may or may not need to add the Postgres PGDG repo to do this)

2. Copy the old Postgres files onto the new system to the same location (typically /var/lib/pgsql/9.5) . As you are having difficulties here, you should try doing it AS ROOT from the command line. (I assume SELinux is not operative or interfering, if it is, disable it). [You can either put the disk in as a second drive, or use a USB enclosure] To get root access, type "sudo /bin/bash"

3. Change the ownership permissions of the postgresql files to the postgres user.

4. Start Postgres!

Answer 2

in my case it was the shared_buffers was too big that cased the failed state, I decrease it to 128MB and restarted then worked fine , I made a copy of the data directory after an OS crash to a new OS ..