I accidentally removed data directory /var/lib/pgsql/9.1/data from running postgresql server.

I guess I'm going to lose my data when server will be restarted.

I'm wondering if postgresql has any tools to recover data from running server instance or I have to use system data recovery tools?

  • I'd try a filesystem level undelete tool.
    – usr
    Jun 14 '14 at 0:36
  • Most importantly, DO NOT stop the server, restart it, or stop/restart PostgreSQL Jun 16 '14 at 12:08

I am afraid your chances are somewhere between infinitesimally small and zero.

There are a lot of files in that directory and it's sub-directories. Any which were actually open by one of the postgresql backend processes at the time you deleted the directory, and have not been closed since, are still present in the filesystem, but any that were not are long gone. So the best you would be able to hope for is to recover some of the files, and the database is unlikely to be able to start up properly with missing files.

Assuming a Linux system, you can see which files are actually open (and therefore in theory at least still recoverable) using this command:

lsof -u postgres | grep /var/lib/pgsql

You will see entries such as:

postmaste 2418 postgres 5u REG  253,0 8192 274804 /var/lib/pgsql/9.2/data/global/5445770

The second column is the PID of the process, and the numeric part of the 4th column is the FD (file descriptor) number. The file can be recovered using a command like:

cp /proc/{PID}/fd/{FD} {dest_file}

replacing the {PID}, {FD} and {dest_file} as appropriate.

However, as I said, your chances of recovering ALL the lost files, or even enough of them to be able to start the database up again afterwards, are very very slim. Even the ones you do recover may be corrupted, as the other process which has them open will be writing to them at the same time.

Instead, it might be worth trying pg_dump. It is likely to fail to dump the whole database, but if you are lucky you might recover some of it that way. If there are some tables which are more important to you than others, and it appears that the running database is still able to access them, then you might try using pg_dump to dump only those tables.

It is possible (but I would say quite unlikely) that you could restore the underlying files using system file recovery tools.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy