24

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 to 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.

migrated from stackoverflow.com Feb 28 '14 at 11:58

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

  • Well, your size query excludes: indexes, tables in pg_catalog, and tables in information_schema. So try seeing if it's any of those by removing those restrictions in the WHERE clause. Please show your exact PostgreSQL version (SELECT version()) and what exactly you're doing to "vacuum the full database", i.e. the exact command. If possible, run VACUUM FULL VERBOSE; (no arguments) and paste the output somewhere then link to it here. – Craig Ringer Jan 12 '14 at 10:16
  • Try dropping the database. You might also try dumping the database, and then restoring it would drop the garbage. – jb. Jan 12 '14 at 10:16
  • 1
    @jb That'd work, but shouldn't be necessary. Better to learn what the problem is. – Craig Ringer Jan 12 '14 at 10:17
21

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.

  • I had to urgently get disk space back so I droped the database and restored it from backup. However the "how to" solve this issue is still much valuable for future cases. My database is 9.1, win 8 64 bit, does the file naming (case of open files) apply the same way as in linux? – arcull Jan 13 '14 at 7:11
  • @arcull OK, I did not realize you were using Windows. I added some info to the answer about how this applies to Windows. If you think it might be a useful answer, please consider upvoting as it makes it easier for others to find it. – harmic Jan 13 '14 at 10:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy