0

So, I am (well... I was) running PostgreSQL within a container (Ubuntu 14.04LTS with all the recent updates, back-end storage is "dir" because of convince).

To cut the long story short, the container folder got deleted. Following the use of extundelete and ext4magic, I have managed to extract some of the database physical files (it appears as if most of the files are there... but not 100% sure if and what is missing).

I have two copies of the database files. One from 9.5.3 (which appears to be more complete) and one from 9.6 (I upgraded the container very recently to 9.6, however it appears to be missing datafiles).

All I am after is to attempt and extract the SQL code the relates to the user defined functions. Is anyone aware of an approach that I could try?

P.S.: Last backup is a bit dated (due to bad practices really) so it would be last resort if the task of extracting the needed information is "reasonable" and "successful".

Regards, G

Update - 20/4/2017 I was hoping for a "quick fix" by somehow extracting the function body text off the recovered data files... however, nothing's free in this life :)

Starting from the old-ish backup along with the recovered logs, we managed to cover a lot of ground into bringing the DB back to life.

Lessons learned:
1. Do implement a good backup/restore strategy
2. Do not store backups on the same physical machine
3. Hardware failure can be disruptive... Human error can be disastrous!

George G
  • 43
  • 1
  • 6
  • The lessons learned is missing a very important part: 4. Store your source code in a version control system. –  Apr 20 '17 at 11:52
  • Hi horse with no name... we had that on another container... on the same machine... The whole storage pool was deleted so... Point 3 prevailed here unfortunately... – George G Apr 24 '17 at 09:09
  • well... that wouldn't have mattered ... if ... you had properly ... stored the source code in ... a version control system ... (your broken `.` key ... seems ... to affect my ... keyboard as well ... ) –  Apr 24 '17 at 09:21

2 Answers2

1

If you can reconstruct enough of a data directory to start postgres in single user mode you might be able to dump pg_proc. But this seems unlikely.

Otherwise, if you're really lucky you'll be able to find the relation for pg_proc and its corresponding pg_toast relation. The latter will often contain compressed text, so searches for parts of variables you know appear in function bodies may not help you out.

Anything stored inline in pg_proc will be short functions, significantly less than 8k long. Everything else will be in the toast relation.

To decode that you have to unpack the pages to get the toast hunks, then reassemble them and uncompress them (if compressed).

If I had to do this, I would probably create a table with the exact same schema as pg_proc in a new postgres instance of the same version. I would then find the relfilenode(s) for pg_catalog.pg_proc and its toast table using the relfilenode map file (if it survived) or by pattern matching and guesswork. I would replace the empty relation files for the new table I created with the recovered ones, restart postgres, and if I was right, I'd be able to select from the tables.

Not easy.

I suggest reading up on postgres's storage format as you'll need to understand it.

You may consider https://www.postgresql.org/support/professional_support/ . (Disclaimer, I work for one of the listed companies).

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • You forgot the most important advice: the source of those functions should have been stored in a source control (git, subversion, ...) in the first place. Then this would not have been a problem at all. –  Apr 18 '17 at 19:59
0

P.S.: Last backup is a bit dated (due to bad practices really) so it would be last resort if the task of extracting the needed information is "reasonable" and "successful".

Backups are your first resort here.

If the 9.5 files are complete and undamaged (or enough so to dump the schema) then simply copying them in place, checking permissions and starting the server will get you going. Don't trust the data though, you'll need to check it all.

Although it is possible to partially recover given damaged files, it's a long complicated process and the fact that you are asking on Stack Overflow probably means it's not for you.

Richard Huxton
  • 21,516
  • 3
  • 39
  • 51
  • Hindsight is 20/20 they say... all I am after is the functions' code which would be in the range of a couple of hundred of kilobytes. I am hoping that it will be a matter of comparing that against an older codebase. The rest of the data can be recreated at a much lesser cost (processing time is virtually free). – George G Apr 18 '17 at 11:30
  • If you have an older copy of the functions, it's likely going to be WAY easier to just re-do whatever recent changes you made. BTW, time to use revision control for your functions. – Craig Ringer Apr 18 '17 at 12:01