X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id EC4423A3FCC for ; Mon, 17 Jan 2005 19:58:35 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 51222-10 for ; Mon, 17 Jan 2005 19:58:17 +0000 (GMT) Received: from tigger.fuhr.org (tigger.fuhr.org [63.214.45.158]) by svr1.postgresql.org (Postfix) with ESMTP id 5F6493A3EC3 for ; Mon, 17 Jan 2005 19:56:14 +0000 (GMT) Received: from winnie.fuhr.org (winnie.fuhr.org [10.1.0.1]) by tigger.fuhr.org (8.13.1/8.13.1) with ESMTP id j0HJuAOn002651 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK); Mon, 17 Jan 2005 12:56:12 -0700 (MST) (envelope-from mfuhr@winnie.fuhr.org) Received: from winnie.fuhr.org (localhost [127.0.0.1]) by winnie.fuhr.org (8.13.1/8.13.1) with ESMTP id j0HJuAHJ093672; Mon, 17 Jan 2005 12:56:10 -0700 (MST) (envelope-from mfuhr@winnie.fuhr.org) Received: (from mfuhr@localhost) by winnie.fuhr.org (8.13.1/8.13.1/Submit) id j0HJu92d093671; Mon, 17 Jan 2005 12:56:09 -0700 (MST) (envelope-from mfuhr) Date: Mon, 17 Jan 2005 12:56:09 -0700 From: Michael Fuhr To: Martin Kuria Cc: pgsql-admin@postgresql.org Subject: Re: postgres Recover Deleted Data Message-ID: <20050117195609.GA93502@winnie.fuhr.org> References: Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.003 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200501/177 X-Sequence-Number: 16036 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/