Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have :

CREATE TABLE ketoan_vn.Customers
(
  CustomerId character varying(10) NOT NULL,
  CustomerName character varying(150),
  CONSTRAINT Customers_PK_CustomerID PRIMARY KEY (CustomerId)
);

I inserted 2 rows into table ketoan_vn.Customers:

INSERT INTO ketoan_vn.customers(
            customerid, customername)
    VALUES ('C0001','Marry'),
    ('C0002','Tom');

I then dropped ketoan_vn.Customers :

drop table ketoan_vn.Customers

Now I want to recover the data of the dropped table. Is this possible with PostgreSQL 9.1?

share|improve this question
    
    
Similar to this one: stackoverflow.com/q/12472318/479863 –  mu is too short Apr 18 '13 at 4:34
    
I was worked.but not success –  Le Ngoc Loan Apr 18 '13 at 4:36
    
voting to migrate to dba.se –  Chris Travers Apr 18 '13 at 5:25
add comment

migrated from stackoverflow.com Apr 18 '13 at 6:52

This question came from our site for professional and enthusiast programmers.

1 Answer

You can't, the file has been physically deleted from the disk. I'm assuming your real question is about a non-trivial table you care about, not a toy-table you can re-create at will.

If so: Restore from a backup.

If you don't have a backup, shut the computer down now and contact a professional data recovery team to see if they can recover the table from the empty space in the file system if it hasn't been over-written yet. Expect to pay several thousand dollars for the service.

Since the disk isn't phsysically damaged you can try some recovery yourself: Take a disk image of the drive by plugging it into another machine. Do not take a file-system-level copy, you need a raw image of the volume/partition the file system is on. You can then attempt to locate the table in the disk image, but be aware that it's likely fragmented and that the inode information that would identify its location is typically gone. Details would require knowledge of the operating system and file system the database is on.

More on this topic here.

Demo

Run your setup:

regress=# CREATE SCHEMA ketoan_vn;
CREATE SCHEMA
regress=# CREATE TABLE ketoan_vn.Customers
regress-# (
regress(#   CustomerId character varying(10) NOT NULL,
regress(#   CustomerName character varying(150),
regress(#   CONSTRAINT Customers_PK_CustomerID PRIMARY KEY (CustomerId)
regress(# );
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "customers_pk_customerid" for table "customers"
CREATE TABLE
regress=# INSERT INTO ketoan_vn.customers(
regress(#             customerid, customername)
regress-#     VALUES ('C0001','Marry'),
regress-#     ('C0002','Tom');
INSERT 0 2

Get the location of the table file on disk:

regress=# SELECT 
  COALESCE(
    pg_catalog.pg_tablespace_location(pg_tablespace.oid),
    current_setting('data_directory')||'/base/'
  )
  || (SELECT oid from pg_database where datname = current_database() )
  || '/' 
  || relfilenode
  AS path_to_table_file
FROM pg_class
INNER JOIN pg_namespace ON (pg_class.relnamespace = pg_namespace.oid)
LEFT OUTER JOIN pg_tablespace on (reltablespace = pg_tablespace.oid)
WHERE nspname = 'ketoan_vn' AND relname = 'customers';

          path_to_table_file               
------------------------------------------
 /var/lib/pgsql/9.2/data/base/43720/43733
(1 row)

Confirm the location on the shell:

$ sudo ls -l /var/lib/pgsql/9.2/data/base/43720/43733
-rw-------. 1 postgres postgres 8192 Apr 18 14:25 /var/lib/pgsql/9.2/data/base/43720/43733

Now back in psql, drop the table:

drop table ketoan_vn.Customers;

and confirm it's now either gone or zero length:

$ sudo ls -l /var/lib/pgsql/9.2/data/base/43720/43733
-rw-------. 1 postgres postgres 0 Apr 18 14:28 /var/lib/pgsql/9.2/data/base/43720/43733

(the actual file will be removed by the DB at some later time, but it's been truncated, or it'll get re-used. Either is very bad for your data.)

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.