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'),

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?


1 Answer 1


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.


Run your setup:

regress=# CREATE SCHEMA ketoan_vn;
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"
regress=# INSERT INTO ketoan_vn.customers(
regress(#             customerid, customername)
regress-#     VALUES ('C0001','Marry'),
regress-#     ('C0002','Tom');

Get the location of the table file on disk:

regress=# SELECT 
  || (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';

(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.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.