Re: postgres Recover Deleted Data

On Mon, Jan 17, 2005 at 11:42:20AM +0300, Martin Kuria wrote:

> I accidentally deleted two records in one of my tables, the problem I had
> not done a back for the database.
>
> I can I restore my records, please advice how I can recover my deleted
> records, thanks again.

If you haven't VACUUMed the database then the deleted rows are
probably still there.  I don't know if any recovery tools exist,
but to have any chance of recovering the data make a filesystem
backup of $PGDATA as soon as possible, or at least make a backup
of the table's file(s) (see below).  They might not do you any good,
but you'll probably need those files if you do find any recovery
tools.

If you don't find any recovery tools and you *really* need to get
the data back, then you might enjoy reading the chapter describing
page file formats in the documentation ("Page Files" in PostgreSQL
7.x; "Database Physical Storage" in the upcoming 8.0).

You can find out which files hold a table's data by querying
pg_database and pg_class.  For example, suppose I have a table "foo"
in the database "test".  I connect to the database and issue the
following queries:

SELECT oid FROM pg_database WHERE datname = current_database();
  oid
-------
 26492
(1 row)

SELECT relfilenode, reltoastrelid FROM pg_class WHERE relname = 'foo';
 relfilenode | reltoastrelid
-------------+---------------
       36008 |         36011

The table's main file is $PGDATA/base/26492/36008 (also 36008.1,
36008.2, etc. if they exist).  If reltoastrelid has a value other
than 0, then you'll also want to find out where the table's TOAST
data is stored:

SELECT relname, relfilenode FROM pg_class WHERE oid = 36011;
    relname     | relfilenode
----------------+-------------
 pg_toast_36008 |       36011

The table's TOAST data is in $PGDATA/base/26492/36011*.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/