PostgreSQL - 意外删除数据文件后恢复函数的代码 [英] PostgreSQL - Recovery of Functions' code following accidental deletion of data files
问题描述
长话短说，容器文件夹被删除。在使用extundelete和ext4magic之后，我设法提取了一些数据库物理文件（它看起来好像大多数文件都在那里......但不是100％确定是否缺少）。
我有两份数据库文件。一个来自9.5.3（看起来更加完整）和一个来自9.6（我最近升级了容器到9.6，但它似乎缺少数据文件）。
我所要做的就是试图提取与用户定义函数相关的SQL代码。是否有人知道我可以尝试的方法？
PS：上次备份有点过时（因为不好的做法），所以如果任务提取所需信息是合理的和成功的。
问候，
G
更新 - 2017年4月20日
我希望通过某种方式从恢复的数据文件中提取函数体文本来实现快速修复......但是，这一生中没有任何东西是免费的：）
从旧的备份和恢复的日志开始，我们设法覆盖了很多地方，使数据库恢复生机。
经验教训：
1. 做实现一个好的备份/恢复策略
2. 不要在同一台物理机器上存储备份
3.硬件故障可能破坏性 ...人为错误可能 disastrous！
如果您可以重建足够的数据目录在单用户模式下启动postgres，你可能会转储pg_proc。但这似乎不太可能。
否则，如果您真幸运，您将能够找到 pg_proc 及其对应的 pg_toast 关系。后者通常会包含压缩的文本，因此搜索你知道出现在函数体中的变量部分可能无法帮助你。
任何内嵌在 pg_proc 将是短的函数，显着小于8k长。为了解码你必须解压缩页面以获得烘烤块，然后重新组装它们并解压它们（如果压缩的话）。
如果必须这样做，我可能会创建一个与 pg_proc 完全相同的模式的表相同版本的新postgres实例。然后，我会使用relfilenode映射文件（如果它幸存下来）或模式匹配和猜测找到 pg_catalog.pg_proc 的relfilenode（s）和它的toast表。我将替换用恢复的表创建的新表的空关系文件，重新启动postgres，如果我是对的，我可以从表中选择 。
不容易。我建议您阅读 postgres的存储格式，因为您需要了解它。
您可以考虑 https://www.postgresql.org/support/professional_support/ 。 （免责声明，我为其中一家上市公司工作）。
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!
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).
这篇关于PostgreSQL - 意外删除数据文件后恢复函数的代码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
