How to reclaim disk space on PostgreSQL?

Question

I have local installation of 9.1 database with few tables which had cca. 300 mio records and the database grew to about 20 GB. Afterwards I issued delete from command to delete all records from it (I should have used truncate, but I didn't know that). So I did full vacuum on my db to reclaim disk space, but it just doesn't help. My problem looks identical to this one, but there is no solution provided. I have already checked this thread and documentation on "recovering disk space", but still can't find a solution. I use this code to get size of all tables

 SELECT nspname || '.' || relname AS "relation",
 pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
 FROM pg_class C
 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
 WHERE nspname NOT IN ('pg_catalog', 'information_schema')
 AND C.relkind <> 'i'
 AND nspname !~ '^pg_toast'
 ORDER BY pg_total_relation_size(C.oid) DESC
 LIMIT 15;

Totalling less than 1GB. However

SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) 
AS size FROM pg_database 

still shows about 20 GB.

Any advice much appreciated.

Answer 1

Although you did not state it, I assume from your references to documents that you have followed that you have done a VACUUM FULL on the database and/or affected tables. You also didn't specify what postgresql version you are using - I will assume it is > 9.0 (VACUUM FULL behaved differently before this).

VACUUM FULL will rewrite affected tables into new files, then delete the old files. However, if any process still has the old file open, the operating system will not actually delete the file - until the last process has closed it.

If practical, restarting the database would ensure that all open files get closed.

If that is not practical, then you may be able to verify if this is your problem, and find out which process has the files open.

If using Linux (or most other Unix-like systems) you can use the 'lsof' command to get a listing of all files open in all processes. Files which are open but which have since been deleted will have '(deleted)' appended to the filename. So, you can grep the output of lsof, looking for deleted files, like this:

sudo lsof -u postgres | grep 'deleted'

If that identifies processes which still have the old files open, you can use pg_terminate_backend to terminate that process:

SELECT pg_terminate_backend(xxx);

where xxx is the PID of the process, found in the lsof output.

If using Windows, the same principle could apply, because postgres opens files using the FILE_SHARE_DELETE flag, which allows it to delete files that are open in another process. The 'handle' command is the rough equivalent of lsof, although I am not sure if you can tell if the files are deleted or not so some additional work might be required.

It is another question as to why any such processes would be hanging on to old file handles. However in the thread you quoted in your question, Tom Lane seems to imply that it can happen.