From: Michael Fuhr <mike@fuhr.org>
To: Martin Kuria <martinkuria@hotmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: postgres Recover Deleted Data
Date: Mon, 17 Jan 2005 12:56:09 -0700
Message-ID: <20050117195609.GA93502@winnie.fuhr.org> (raw)
In-Reply-To: <BAY22-F20299D450F412B4CA05A05A68E0@phx.gbl>
References: <BAY22-F20299D450F412B4CA05A05A68E0@phx.gbl>
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/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: mike@fuhr.org, martinkuria@hotmail.com
Subject: Re: postgres Recover Deleted Data
In-Reply-To: <20050117195609.GA93502@winnie.fuhr.org>
References: <BAY22-F20299D450F412B4CA05A05A68E0@phx.gbl> <20050117195609.GA93502@winnie.fuhr.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox