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
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
SELECT oid FROM pg_database WHERE datname = current_database();
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*.
In response to
pgsql-admin by date
|Next:||From: Rick Schumeyer||Date: 2005-01-17 20:31:38|
|Subject: "template1 does not exist" while installing 8.0RC5 on RH9|
|Previous:||From: Tom Lane||Date: 2005-01-17 18:22:40|
|Subject: Re: SQL syntax |