Thread: file signature for files that make up postgres database

Hi I am trying to recover a postgres database from a ext3 partition
that the filesystem has become corrupt and lost files.  Can anybody
tell me what are the file signatures for the files that comprise a
postgres database?

Anybody know of any tools that can detect and recover postgres files
from an EXT3 partition?  I see that photorec is supposed to be able to
recognize mysql database files.

Thanks,

Lee

Lee Duynslager <lee.duynslager@gmail.com> writes:
> Hi I am trying to recover a postgres database from a ext3 partition
> that the filesystem has become corrupt and lost files.  Can anybody
> tell me what are the file signatures for the files that comprise a
> postgres database?

Look at PageHeaderIsValid in bufpage.c.  Those tests are pretty weak if
only applied once, but if you got matches on the first ten or twenty 8K
pages of a file, you could probably conclude it was a PG file with high
confidence.
        regards, tom lane

Excerpts from Lee Duynslager's message of jue mar 03 19:23:35 -0300 2011:
> Hi I am trying to recover a postgres database from a ext3 partition
> that the filesystem has become corrupt and lost files.  Can anybody
> tell me what are the file signatures for the files that comprise a
> postgres database?
> 
> Anybody know of any tools that can detect and recover postgres files
> from an EXT3 partition?  I see that photorec is supposed to be able to
> recognize mysql database files.

Being confronted with exactly this problem currently, I find that
pg_filedump is an excellent tool for identifying files -- both for
detecting which files are PG data files, and also for determining which
file belongs to a given table.  Still, it involves too much manual work.
For a small set of files that's OK, but when confronted with a hundred
files or more, it surely needs more automation.

-- 
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support