pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
postgres Recover Deleted Data
2+ messages / 2 participants
[nested] [flat]

* postgres Recover Deleted Data
@ 2005-01-17 08:42 Martin Kuria <martinkuria@hotmail.com>
  2005-01-17 19:56 ` Re: postgres Recover Deleted Data Michael Fuhr <mike@fuhr.org>
  0 siblings, 1 reply; 2+ messages in thread

From: Martin Kuria @ 2005-01-17 08:42 UTC (permalink / raw)
  To: pgsql-admin

Hi,
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.

Kind Regards,
+-----------------------------------------------------+
| Martin W. Kuria (Mr.) martin.kuria@unon.org
+----------------------------------------------------+

_________________________________________________________________
FREE pop-up blocking with the new MSN Toolbar - get it now! 
http://toolbar.msn.click-url.com/go/onm00200415ave/direct/01/




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: postgres Recover Deleted Data
  2005-01-17 08:42 postgres Recover Deleted Data Martin Kuria <martinkuria@hotmail.com>
@ 2005-01-17 19:56 ` Michael Fuhr <mike@fuhr.org>
  0 siblings, 0 replies; 2+ messages in thread

From: Michael Fuhr @ 2005-01-17 19:56 UTC (permalink / raw)
  To: Martin Kuria <martinkuria@hotmail.com>; +Cc: pgsql-admin

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/



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2005-01-17 19:56 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-01-17 08:42 postgres Recover Deleted Data Martin Kuria <martinkuria@hotmail.com>
2005-01-17 19:56 ` Michael Fuhr <mike@fuhr.org>

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